Flow
Merging repeated lines without lying
1Keep drops apart
An item going to two drops stays two lines.
2Key on the ID
Use the name only when a row has no ID.
3Add money per piece
Cost adds each piece at its own price; the shown price is a weighted average.
4One source for every output
The spreadsheet reads the same merged lines as the order.
5Test the same-name case
Two different items with one name must come out as two lines.
The order my system sends a supplier printed the same item once per store. A few of my stores share a delivery spot, and the order is split by drop, so one item for three stores showed up as three lines that looked the same.
Two kinds of merging
Some things must never merge. An item going to two drops is two deliveries, so it stays two lines. Inside one drop, though, lines for the same item can become one line. Whatever you use to decide "same item" is the whole game. If two lines get the same key, you are saying they are one thing.
Probably my old key was item plus store, so stores never merged. That's why the supplier saw a line per store.
My first key was the name
I keyed on the item's name, cleaned up: no extra spaces, all lowercase. Lines from different stores merged, and the test with three stores at one drop got its one line.
That change also did some good things I kept. A merged line now shows the drop's name, not the first store's. Its price is an average weighted by units. And its cost adds each piece at that piece's own price, not at whatever price the line started with. That last part matters once lines with different prices can meet.
The spreadsheet that goes with the order now reads the merged lines too, so it can't show different lines than the order does.
Names repeat
Some lines are placeholders that get a real product later, and their short names repeat. Two different items with the same short name would land on one line. That line would show one item's code, both quantities and a price that may fit neither. The drop total would still be right, so a check on totals would miss it.
Key on the ID
The same day the key switched away from the name.
The key is the item's ID, or the trimmed, lowercased name when there is no ID.
The bad test is the part I'd warn anyone about. It gave three stores three different IDs and the same name, and expected one line. It agreed with the name key, so it passed. If you build something like this, these are the tests I'd want:
- The same item from several stores at one drop comes out as one line.
- Two different items with the same name come out as two lines, each with its own code.
- One item going to two drops comes out as one line under each drop.
At first I couldn't say which test matched my real data better. The new code assumes one item keeps one ID across stores, and I'm going with that.
Where the ID key still slips
A row missing its ID won't merge with the same item that has one. If an ID ever got reused for two different items, this key would merge them just like the name did, and nothing in this code would catch it.
Nothing tells me how often the name fallback gets used, so I can't say whether that corner is rare or common.