Josue Barocio
Board Regular
- Joined
- Mar 8, 2002
- Messages
- 101
I need a unique type of Pivot (I think) table. The file below explains what is needed better than I can in words.
The input file has the name of assemblies in column A and a list of the components needed to produce the assembly in column B. In the list, part 10 is used in assemblies Control and Sensor. Part 14 is used in the Control and Dynamic assemblies and part 20 is used in the Sensor and Dynamic assemblies.
The output file needs to be a matrix identifying at a glance assemblies that have common parts, Control, Sensor, and Dynamic in the example. The only assembly with unique parts is the Titan.
I can approach the objective with a Pivot table, but I have not been able to avoid listing all the components (17 in the example, about 50 assemblies and 1200 records in the actual live file)
Any ideas about functions that may help get to the desired output file would be appreciated. I've tried transposing, vlookup, hlookup, match, etc.
Thanks,
Joe
The input file has the name of assemblies in column A and a list of the components needed to produce the assembly in column B. In the list, part 10 is used in assemblies Control and Sensor. Part 14 is used in the Control and Dynamic assemblies and part 20 is used in the Sensor and Dynamic assemblies.
The output file needs to be a matrix identifying at a glance assemblies that have common parts, Control, Sensor, and Dynamic in the example. The only assembly with unique parts is the Titan.
I can approach the objective with a Pivot table, but I have not been able to avoid listing all the components (17 in the example, about 50 assemblies and 1200 records in the actual live file)
Any ideas about functions that may help get to the desired output file would be appreciated. I've tried transposing, vlookup, hlookup, match, etc.
Thanks,
Joe
Matrix.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | INPUTFILE | ||||||
2 | ASSEMBLY | PART | |||||
3 | Control | 10 | |||||
4 | Control | 11 | |||||
5 | Control | 12 | |||||
6 | Control | 13 | |||||
7 | Control | 14 | |||||
8 | Sensor | 20 | |||||
9 | Sensor | 21 | |||||
10 | Sensor | 10 | |||||
11 | Sensor | 22 | |||||
12 | Dynamic | 30 | |||||
13 | Dynamic | 14 | |||||
14 | Dynamic | 20 | |||||
15 | Dynamic | 31 | |||||
16 | Dynamic | 32 | |||||
17 | Titan | 40 | |||||
18 | Titan | 41 | |||||
19 | Titan | 42 | |||||
20 | |||||||
21 | OUTPUTFILE | ||||||
22 | Control | Sensor | Dynamic | Titan | |||
23 | Control | x | x | x | |||
24 | Sensor | x | x | x | |||
25 | Dynamic | x | x | x | |||
26 | Titan | x | |||||
Sheet1 |