Last year a friend who runs a clothing business asked me to put together an upload sheet. The requirement was simple: he had 5 colours and 4 sizes, one SKU per row, plus a material column with 3 options. I did it by hand in Excel; the first version with 20 rows took almost an hour, and by the time it reached 60 rows I found I had missed one colour in the smallest size and duplicated two rows. Checking took longer than doing it again. That was when I started treating list-every-combination as a job for a tool rather than a chore that care alone can handle.
Row counts multiply, they do not add
This is the easiest place to misjudge. Five colours and four sizes is only 9 inputs and produces 20 rows; fold in 3 materials, 12 inputs, and the row count becomes 60. I first estimated the effort as three more items, a few more rows, and that intuition fails completely once combinations go multi-dimensional.
| Dimensions | Combination rows | Cells to fill | Time it took me by hand |
|---|---|---|---|
| 5 colours x 4 sizes | 20 | 40 | Nearly an hour, two typos |
| 5 colours x 4 sizes x 3 materials | 60 | 180 | One combination missed, two rows duplicated |
| Plus 2 packaging types | 120 | 480 | Never tried; assumed it would go wrong |
| 8 colours x 6 sizes x 4 materials x 2 versions | 384 | 1536 | Not realistic |
| 12 cities x 25 products x 4 channels | 1200 | 3600 | Not realistic |
| Times 3 more years | 3600 | 14400 | First ask whether this sheet is needed |
The cell column is rows times dimensions: 60 rows across 3 columns is 180 cells, and 3600 rows across 4 columns is 14400. If every dimension has 10 options, two dimensions give 100 rows, three give 1000 and four give 10000, so every added dimension multiplies by ten. My habit now is to compute the product before deciding whether to do the job at all: past a few thousand rows I go back and ask whether the full cross product is really needed or only the sensible part of it.
One pass takes two lists; more dimensions go round by round
The Cartesian product in Split and Merge Columns takes two text lists and outputs every pairing in one go. With three or more dimensions I work in rounds: put 5 colours and 4 sizes in the first round and get 20 rows, feed those 20 rows in as the left input for a round with the 3 materials to get 60, and add another round for a fourth dimension - 60 rows with 2 packaging types is 120.
Only later did I realise the advantage of working in rounds: I can read and spot-check every intermediate result. When the output really does run past a thousand rows I check the count at the end of each round - the previous round's row count times the number of items in this round must equal the new count exactly. 20 times 3 should be 60, and a result of 55 means one group is missing an item or has a blank line in it.
For separators, when joining two columns into one line I use a tab or a plain comma, because the next step is usually pasting into spreadsheet software. Commas between columns, and absolutely no commas inside any column; that is the other trap I have fallen into, and I will come to it below.
Where splitting goes wrong most often: one field more than separators
The tool can also split each line on Tab, comma, space or a custom delimiter, and keep only chosen columns. What caught me when taking columns out of a CSV was the segment count: 5 commas in a line produce 6 segments, so one stray comma at the end adds an empty field and every column number after it shifts. When I selected the unit price column thinking it was segment 4, one field in the middle contained a comma, split into two, and the whole line moved one place to the right.
So before selecting columns I check how many segments the first and last lines have, and only proceed when they agree. If the field content itself contains the delimiter, either change the delimiter or clean the data first; do not count on spotting a bad split by eye afterwards.
Splitting on spaces was another crash. One column held address shorthand made of two words, the city and the district, so splitting on spaces produced two or three extra segments per line and every column number went wrong. The same dataset also mixed "Shanghai Pudong New Area" with "Shanghai Pudong", which split into different segment counts. For cases like that I switch to a separator string that cannot occur in the content, re-import and split again, instead of hoping for the best.
Keeping only the columns you need is where this feature really saves time. On an export with a dozen columns I often want just SKU, colour and size; once those three are selected, the remaining width is usable for cross-referencing against another list. Rows whose segment count does not match I pull out and inspect separately rather than leaving them in the result, because a bad split usually does not raise an error - it hands you a column that looks right and means something else entirely.
Two things I always do after generating
The first is deduplication. Multi-dimensional output easily contains semantically duplicate rows, for instance the same colour and size arriving from two different lists. I run it through Text Sort and Dedupe and pull the duplicates out on the way; duplicates are not a small problem, they are duplicate SKUs in an upload sheet and will collide with the backend. The second is formatting. The combination table comes out as bare values, and I usually add a uniform prefix or sequence number to each row; Line Operations does bulk prefixes and suffixes faster than dragging a fill handle, and without the rows you drag past by accident.
When building test cases from parameter combinations I add one more step: confirm whether the full cross product is really wanted. Four parameters with 5 values each is 625 cases, while pairwise coverage often finds the problems - that is a trade-off, not something a tool decides for you.
Common questions
Q: Can two lists of different lengths be merged?
Not the way I would do it by rows. Different lengths mean the two sides are not the same size of data, and a forced row merge leaves some rows half empty. Crossing every combination is a Cartesian product job, which is a different thing from a row-by-row merge.
Q: The product produced 1200 rows; how do I know nothing is missing?
Check with multiplication. The previous round's row count times this round's item count must equal the result count, and then confirm how many rows each colour occupies: across 1200 rows, 12 cities should each appear 100 times, and a city with only 95 means something was dropped.
Q: Can copy and paste in Excel not do this?
Two dimensions and 20 rows, yes; I did it by hand at the cost of an hour and two typos. Three dimensions and up means nested pasting, and once it goes wrong you cannot tell which layer dropped something. My line is three dimensions or more than 60 rows, and it goes to a tool.
Q: Comma or Tab as the separator?
Look at whether the content contains commas. Colour names and channel names often come with their own commas or spaces, and in that case I use Tab, or a custom string that cannot appear in the content. Pick the wrong separator and the columns come out shifted across the board.
That clothing upload sheet was finally handed over like this: colours and sizes to 20 rows, materials to 60, packaging to 120, with a multiplication check at each round and a dedupe sort afterwards. The hour of manual work bought two typos; the few minutes with a tool bought a row-count relationship you can verify. That is also how I now decide whether to do something by hand: if the result cannot be checked with a multiplication, it should not be done by hand.