Who's up for a challenge? Extract the pivot table text in the proper way

[DigitalZugzwang]

Here's the situation. I have a pivot table the presents all the information I need. Now I need to extract the information in a particular way to "concatenate" on to one line based on a condition with a particular format. An example of the table is below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Activity[/TD]
[TD]Component[/TD]
[/TR]
[TR]
[TD]OMG[/TD]
[TD]Install[/TD]
[TD]Antenna[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Line[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Radio
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inspection[/TD]
[TD]PIM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Decommission[/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]Install[/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inspection[/TD]
[TD]PIM[/TD]
[/TR]
</tbody>[/TABLE]

In the above example I would need two lines returned, one per site. So it would look like this:

For OMG - Install: Antenna, Line, Radio; Inspection: PIM; Decommission: Radio
For Test - Install: Radio; Inspection: PIM

I'm open to any way to get this done. I've thought about using TEXTJOIN with an array (not sure how to pull it off) but some users of this spreadsheet will not have excel 2016. Any ideas?

 

[jarjarbingie]

Here's the situation. I have a pivot table the presents all the information I need. Now I need to extract the information in a particular way to "concatenate" on to one line based on a condition with a particular format. An example of the table is below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Activity[/TD]
[TD]Component[/TD]
[/TR]
[TR]
[TD]OMG[/TD]
[TD]Install[/TD]
[TD]Antenna[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Line[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inspection[/TD]
[TD]PIM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Decommission[/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD]Test[/TD]
[TD]Install[/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inspection[/TD]
[TD]PIM[/TD]
[/TR]
</tbody>[/TABLE]

In the above example I would need two lines returned, one per site. So it would look like this:

For OMG - Install: Antenna, Line, Radio; Inspection: PIM; Decommission: Radio
For Test - Install: Radio; Inspection: PIM

I'm open to any way to get this done. I've thought about using TEXTJOIN with an array (not sure how to pull it off) but some users of this spreadsheet will not have excel 2016. Any ideas?

assuming the data is in columns A:C:

D2 = =IF(A2="",D1,A2)
E2 = IF(B2="",E1,B2)
F2 = IF(AND(D2<>D1,B2<>E1),E2&": "&C2,IF(E2<>E1,F1&"; "&E2&": "&C2,F1&", "&C2))

drag all formulas down