Another combining-lists Excel question
January 10, 2020 8:51 AM Subscribe
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:
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.
Example:
List 1:
Rose Daisy SunflowerList 2:
Deer Moose SquirrelWhat I want is:
Deer |Rose Deer |Daisy Deer |Sunflower Moose |Rose Moose |Daisy Moose |Sunflower Squirrel |Rose Squirrel |Daisy Squirrel |SunflowerThe 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.
Best answer: 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 [1 favorite]
posted by gregr at 9:16 AM on January 10, 2020 [1 favorite]
Response by poster: 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
Thanks gregr, I'll try that out.
posted by Tehhund at 9:20 AM on January 10, 2020
Response by poster: With a little massaging that works for my real data - thanks!
posted by Tehhund at 9:34 AM on January 10, 2020 [1 favorite]
posted by Tehhund at 9:34 AM on January 10, 2020 [1 favorite]
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 [3 favorites]
posted by epo at 9:49 AM on January 10, 2020 [3 favorites]
How to Make a Cartesian product of two tables in Excel
posted by oceano at 10:54 AM on January 10, 2020 [2 favorites]
posted by oceano at 10:54 AM on January 10, 2020 [2 favorites]
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
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
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
This thread is closed to new comments.
posted by adamrice at 9:08 AM on January 10, 2020