Pivot the Same Data Multiple Times

[KaleyAnne]

Hello -

I am trying to pivot the same data multiple times to understand the all sub-level components of my finished parts. I have a list of parent items and the items that make them up which looks like this:
Parent Item (A) Component Item (B)

I can pivot the parent to component to get all the details on what parts make up the parent part, HOWEVER there are sub-assemblies in there.

For example, say I make Popsicles, my data looks like this:
Parent Item (A)......Component Item (B)
Popsicle Red ...........Stick
Popsicle Red ...........Wrapper
Popsicle Red ...........Red Juice
Red Juice ...............Red Dye
Red Juice ...............Grape Juice
...

When I pivot on Red Popsicle all I get is:
Stick
Wrapper
Red Juice

But I want:
Stick
Wrapper
Red Dye
Grape Juice

Is there a way to get there?

Thanks for the help!

 

[steve the fish]

Hi. There maybe alternatives but this works. I can only see how you could do it with helper columns not just from your data as stands. Take this table:

[TABLE="width: 657"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Parent Item (A)[/TD]
[TD]Component Item (B)[/TD]
[TD]Top Level Parent Item[/TD]
[TD]Parent & Component?[/TD]
[/TR]
[TR]
[TD]Popsicle Red[/TD]
[TD]Stick[/TD]
[TD]Popsicle Red[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Popsicle Red[/TD]
[TD]Wrapper[/TD]
[TD]Popsicle Red[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Popsicle Red[/TD]
[TD]Red Juice[/TD]
[TD]Popsicle Red[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Red Juice[/TD]
[TD]Red Dye[/TD]
[TD]Popsicle Red[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Red Juice[/TD]
[TD]Grape Juice[/TD]
[TD]Popsicle Red[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

The formula i used in C2:

=IF(ISNUMBER(MATCH(A2,B:B,0)),INDEX(A:A,MATCH(A2,B:B,0)),A2)

In D2:

=ISNUMBER(MATCH(B2,A:A,0))

This table can be pivoted using the helper columns in the filter area of the pivot to produce:

[TABLE="width: 290"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Top Level Parent Item[/TD]
[TD]Popsicle Red[/TD]
[/TR]
[TR]
[TD]Parent & Component?[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grape Juice[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red Dye[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stick[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wrapper[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[KaleyAnne]

This is an excellent solution. Thank you! It worked great!