Interesting Multiple Coincidences or Pivot Table Problem

[Josue Barocio]

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

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

 

[Juan Pablo González]

JPG
Is it necessary to follow the route of MS Query?
I just copied the Column of Assembly into another Column & then Made a pivot in which both row & Column field has Assembly as field & the Data field has part into it; This also solves the problem.

Well, that's another approach ! so it's not necessary, but I think it's still the one that is most "maintanable" in time. Mainly because you don't have to, a) modify the input file, and b) create maunally (or with another method) the array of unique assemblies.

 

[Josue Barocio]

JPG
Is it necessary to follow the route of MS Query?
I just copied the Column of Assembly into another Column & then Made a pivot in which both row & Column field has Assembly as field & the Data field has part into it; This also solves the problem.

The method you suggest is exactly what I tried in my first attempt, but I was only able to display intersections of the assemblies, never the common parts. If you are able to pivot the original file directly into the matrix provided in the post, please describe the steps in more detail.

Thanks,

Joe