Combining two lists into one big list

[stefanaalten]

Hi, I have two worksheets with two columns of text values in each (simplifying slightly):

Worksheet 1 (7,000 rows):
+-------+----------+
|Service|Component1|
+-------+----------+
Service and Component1 may both contain blank values and duplicates in each column, although no duplicate combinations of Service + Component1.

Worksheet 2 (2,700 rows):
+----------+----------+
|Component1|Component2|
+----------+----------+
Component1 and Component2 may contain duplicate values in each column, although no duplicate combinations of Component1 + Component2, and neither column contains any blank values.

I want to create a third worksheet with:

Worksheet 3:
+-------+----------+----------+
|Service|Component1|Component2|
+-------+----------+----------+
i.e. a full list of all Component1-Component2 combinations with the Service related to Component1.

Some Service-Component1 combinations won't have matching Component2, in which case I would like a row for Service|Component1 (and Component2 left blank or populated with something like "No component2"). Other Service-Component1 combinations will have multiple Component2's, and I would like to list each in a separate row.

I have tried to work this out for myself and ... have failed miserably so far. I'm under time pressure to produce the list, and hope someone is feeling smart and can help me with this?

 

[Rambo4711]

I suggest that you first sort your data in worksheet1 by “service” and in second by "component1"</SPAN>

Then in cell C2 in worksheet1 put this formula: </SPAN>=IF(A2&B2=A1&B1,"Delete","")
</SPAN></SPAN>Copy the formula down to the last item</SPAN></SPAN>
Mark column C and Copy – Paste special - Values</SPAN></SPAN>
Then put an Autofilter to column A:C and sort out all “Empty” items</SPAN></SPAN>
Mark all visible cells in column A:B Copy and Paste it to worksheet3</SPAN></SPAN>

Type the header “component2” in cell C1 in worksheet3</SPAN></SPAN>
In cell C2 type this formula: </SPAN></SPAN>=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE)=TRUE),"",VLOOKUP(A2,Sheet2!A:B,2,FALSE))
</SPAN></SPAN>Copy the formula down to the last item</SPAN></SPAN>

 

[J.Ty.]

The operation you need is called join in the databases.
Take the Excel implementation of join from http://www.mimuw.edu.pl/~jty/MrExcel/stefanaalten.xlsx

1) Start by sorting your both input tables by Component1. Remove rows with empty Component1 from the table with Service, they will not contribute to the result anyway.
2) Insert one of your tables (the one with Service) into the green columns, the other into the blue columns, so that Component1 is in front in both of them.
3) Do not put there column headers, the formulas cannot handle them.
4) Then mark the second row and fill down as far as the largest of the relations. Then look at the yellow cell - it will give you the number of rows you need for the whole result. Then fill the second row in columns to the right of the yellow cell to make that many rows.
5) The result will appear in the red columns, in the order Component1, Service, Component2.
6) Check the result carefully, especially in cases where you have empty cells. I did not use my formulas for tables with empty cells, so far.
8) If the empty cells behave oddly (for instance appear as 0), fill empty cells with formula ="", which produces the empty string (will be invisible in the cell).
9) Check again
10) Let me know if everything is OK.

J.Ty.

 

[stefanaalten]

Hi Rambo4711, many thanks, but I'm afraid that doesn't seem to work. For one thing this does not produce a list with the multiple component1-component2 for the same service on separate rows.