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:

Parent Item (A)	Component Item (B)	Top Level Parent Item	Parent & Component?
Popsicle Red	Stick	Popsicle Red	FALSE
Popsicle Red	Wrapper	Popsicle Red	FALSE
Popsicle Red	Red Juice	Popsicle Red	TRUE
Red Juice	Red Dye	Popsicle Red	FALSE
Red Juice	Grape Juice	Popsicle Red	FALSE

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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:

Top Level Parent Item	Popsicle Red
Parent & Component?	FALSE
Row Labels
Grape Juice
Red Dye
Stick
Wrapper
Grand Total

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[KaleyAnne]

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