Another combining-lists Excel question

January 10, 2020 8:51 AM

I've got 2 lists in an Excel doc. I want to associate every item in list 1 with every item in list 2. The lists have 25 and 13 lines so I'll end up with 325 lines, and every once in a while an item is added. So I'd like to automate the process rather than doing a huge copy & paste job every time. Can I do this just with formulas (i.e., without VBA)?

Example:

List 1:

Rose
Daisy
Sunflower

List 2:

Deer
Moose
Squirrel

What I want is:

Deer     |Rose
Deer     |Daisy
Deer     |Sunflower
Moose    |Rose
Moose    |Daisy
Moose    |Sunflower
Squirrel |Rose
Squirrel |Daisy
Squirrel |Sunflower

The order of each column doesn't matter, and if I need one of them grouped I can just ask Excel to sort the columns.

To me, Formulas > Copy & Paste > VBA, so if there's no good way to do this without VBA I'll just be really careful when I copy and paste.

posted by Tehhund to Computers & Internet (8 answers total)

What's the end goal here? Because it seems like Excel might not be the right tool for this job.
posted by adamrice at 9:08 AM on January 10, 2020

I found an example of how to do this on stack exchange. I created a google sheets example.
posted by gregr at 9:16 AM on January 10, 2020

The plan is to use Excel for some sanity checks (e.g., number of each item is correct) then copy and paste them into another program instead of typing them over and over. If I can get another program (say, a simple SQL DB?) to generate the big list I can just copy it back into Excel to finish the workflow.

Thanks gregr, I'll try that out.
posted by Tehhund at 9:20 AM on January 10, 2020

With a little massaging that works for my real data - thanks!
posted by Tehhund at 9:34 AM on January 10, 2020

In SQL this is called a cross join. That stack exchange kludge is a perfect example of "just because you can doesn't mean you should" and looks like a nightmare to maintain. This is also a trivial loop in any scripting or programming language.
posted by epo at 9:49 AM on January 10, 2020

How to Make a Cartesian product of two tables in Excel
posted by oceano at 10:54 AM on January 10, 2020

I would put one set in column A, starting in row 2 and the other set in row 1, starting in column B and leave A1 empty. You can change a row into a column by using Paste Special... Transpose. Now put this in cell B2: =$A2&B$1 and select that cell. Drag that cell down column B, using the lower right corner of the cell so it copies the formula down and doesn't move the cell itself (this is called autofill). Now highlight the cells in column B from 2 down to the last entry in the column. Now autofill the rest of the columns by dragging the highlighted area over (again just the corner) to the last column. The dollar signs keep the row or column the same when you drag, so you are always having the formula look in column A and row 1.
posted by soelo at 2:46 PM on January 10, 2020

This formula =$A2&B$1 will just string both values together. If you want something separating them you can add it in quotes with another ampersand after it: =$A2&" - "&B$1.

Also, if you are copying these values, you will want to use Copy and then Paste Special... Values. That way you get the results of the formula and not the actual formula.
posted by soelo at 2:49 PM on January 10, 2020