stefanaalten
Board Regular
- Joined
- Feb 1, 2011
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
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?
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.