Convert Data in Sub-Total Format to Pivot Friendly

[Jerry138889]

A program I use outputs data in a non-pivot friendly format. It has already grouped the data with different levels.

I am trying to convert this data into data that is pivot table friendly.

I currently have data like this where Location is always filled out and will have at least one (but possibly more) entries but Shop Name and Employee might have blank/zero values or an actual value.


[TABLE="width: 166"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[/TR]
[TR]
[TD]France[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]Shop Name[/TD]
[/TR]
[TR]
[TD]Aldi[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[/TR]
[TR]
[TD]France[/TD]
[/TR]
[TR]
[TD]Shop Name[/TD]
[/TR]
[TR]
[TD]Carrefour[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[/TR]
[TR]
[TD]Francis[/TD]
[/TR]
[TR]
[TD]Petra[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Shop Name[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[/TR]
[TR]
[TD]Shop Name[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[/TR]
[TR]
[TD]Morrison's[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[/TR]
[TR]
[TD]Jerry

And I want to convert it to this type of data so that I can manipulate it in a pivot table


[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Location[/TD]
[TD="class: xl65, width: 64"]Shop Name[/TD]
[TD="class: xl65, width: 64"] Employee[/TD]
[/TR]
[TR]
[TD="class: xl66"]France[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]Germany[/TD]
[TD="class: xl66"] Aldi[/TD]
[TD="class: xl66"] Sally[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] Harry[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] Peter[/TD]
[/TR]
[TR]
[TD="class: xl66"]France[/TD]
[TD="class: xl66"]Carrefour[/TD]
[TD="class: xl66"] Francis[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] Petra[/TD]
[/TR]
[TR]
[TD="class: xl66"]USA[/TD]
[TD="class: xl66"]Walmart[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]UK[/TD]
[TD="class: xl66"]Tesco[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]Morrison's[/TD]
[TD="class: xl66"] Jerry[/TD]
[/TR]
</tbody>[/TABLE]

 

[GR00007]

The data as given is ambiguous. Is Jerry an employee of Tesco or Morrison's; is Aldi in France too?
Can program be accessed to give a different format of report? Is there an underlying DB that could be accessed directly?

 

[Jerry138889]

The data as given is ambiguous. Is Jerry an employee of Tesco or Morrison's; is Aldi in France too?
Can program be accessed to give a different format of report? Is there an underlying DB that could be accessed directly?

Hi.,

I thought the second grid would have made that clear but if not then my apologies.

Jerry is an employee of Morrison's only, Aldi is in Germany only.

No, this is the only format unfortunately and the underlying DB is very tricky and time consuming to navigate (would take a week+ to get the right team to query it and return the correct data).

Thanks for your help

 

[GR00007]

Please check if there's a "write your own report" option. Could a new report with three columns (Location, Shop Name, Employee) be created?

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td][/td][td=bgcolor:#FFFF00]Option1[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]CNT[/td][td=bgcolor:#70AD47]Location[/td][td=bgcolor:#70AD47]Shop Name[/td][td=bgcolor:#70AD47]Employee[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]France,Germany[/td][td=bgcolor:#E2EFDA]Aldi[/td][td=bgcolor:#E2EFDA]Sally,Harry,Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]France[/td][td]Carrefour[/td][td]Francis,Petra[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]USA[/td][td=bgcolor:#E2EFDA]Walmart[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]UK[/td][td]Tesco,Morrison's[/td][td]Jerry[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td=bgcolor:#FFFF00]Option2[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]CNT[/td][td=bgcolor:#70AD47]Location[/td][td=bgcolor:#70AD47]Shop Name[/td][td=bgcolor:#70AD47]Employee[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]France[/td][td=bgcolor:#E2EFDA]Aldi[/td][td=bgcolor:#E2EFDA]Sally[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]France[/td][td]Aldi[/td][td]Harry[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]France[/td][td=bgcolor:#E2EFDA]Aldi[/td][td=bgcolor:#E2EFDA]Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]Germany[/td][td]Aldi[/td][td]Sally[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Germany[/td][td=bgcolor:#E2EFDA]Aldi[/td][td=bgcolor:#E2EFDA]Harry[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]Germany[/td][td]Aldi[/td][td]Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]France[/td][td=bgcolor:#E2EFDA]Carrefour[/td][td=bgcolor:#E2EFDA]Francis[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]France[/td][td]Carrefour[/td][td]Petra[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]USA[/td][td=bgcolor:#E2EFDA]Walmart[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]UK[/td][td]Tesco[/td][td]Jerry[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]UK[/td][td=bgcolor:#E2EFDA]Morrison's[/td][td=bgcolor:#E2EFDA]Jerry[/td][/tr]
[/table]

 

[sandy666]

and Option2 with PivotTable:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Location[/td][td=bgcolor:#DDEBF7]Shop Name[/td][td=bgcolor:#DDEBF7]Employee[/td][/tr]

[tr=bgcolor:#FFFFFF][td]France[/td][td]Aldi[/td][td]Harry[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Sally[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Carrefour[/td][td]Francis[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Petra[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Germany[/td][td]Aldi[/td][td]Harry[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Peter[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Sally[/td][/tr]

[tr=bgcolor:#FFFFFF][td]UK[/td][td]Morrison's[/td][td]Jerry[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Tesco[/td][td]Jerry[/td][/tr]

[tr=bgcolor:#FFFFFF][td]USA[/td][td]Walmart[/td][td](blank)[/td][/tr]
[/table]

 

[Jerry138889]

something like this?

[TABLE="class: head"]
<tbody>[TR]
[TD][/TD]
[TD="bgcolor: #FFFF00"]Option1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CNT[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Location[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Shop Name[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Employee[/COLOR][/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

1​

[/TD]
[TD="bgcolor: #E2EFDA"]France,Germany[/TD]
[TD="bgcolor: #E2EFDA"]Aldi[/TD]
[TD="bgcolor: #E2EFDA"]Sally,Harry,Peter[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]France[/TD]
[TD]Carrefour[/TD]
[TD]Francis,Petra[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

3​

[/TD]
[TD="bgcolor: #E2EFDA"]USA[/TD]
[TD="bgcolor: #E2EFDA"]Walmart[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]UK[/TD]
[TD]Tesco,Morrison's[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #FFFF00"]Option2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CNT[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Location[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Shop Name[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Employee[/COLOR][/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

1​

[/TD]
[TD="bgcolor: #E2EFDA"]France[/TD]
[TD="bgcolor: #E2EFDA"]Aldi[/TD]
[TD="bgcolor: #E2EFDA"]Sally[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]France[/TD]
[TD]Aldi[/TD]
[TD]Harry[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

1​

[/TD]
[TD="bgcolor: #E2EFDA"]France[/TD]
[TD="bgcolor: #E2EFDA"]Aldi[/TD]
[TD="bgcolor: #E2EFDA"]Peter[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Germany[/TD]
[TD]Aldi[/TD]
[TD]Sally[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

1​

[/TD]
[TD="bgcolor: #E2EFDA"]Germany[/TD]
[TD="bgcolor: #E2EFDA"]Aldi[/TD]
[TD="bgcolor: #E2EFDA"]Harry[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Germany[/TD]
[TD]Aldi[/TD]
[TD]Peter[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

2​

[/TD]
[TD="bgcolor: #E2EFDA"]France[/TD]
[TD="bgcolor: #E2EFDA"]Carrefour[/TD]
[TD="bgcolor: #E2EFDA"]Francis[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]France[/TD]
[TD]Carrefour[/TD]
[TD]Petra[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

3​

[/TD]
[TD="bgcolor: #E2EFDA"]USA[/TD]
[TD="bgcolor: #E2EFDA"]Walmart[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]UK[/TD]
[TD]Tesco[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

4​

[/TD]
[TD="bgcolor: #E2EFDA"]UK[/TD]
[TD="bgcolor: #E2EFDA"]Morrison's[/TD]
[TD="bgcolor: #E2EFDA"]Jerry[/TD]
[/TR]
</tbody>[/TABLE]

My second table is what I'm trying to get to. The first instance of France has no shop and no employees - that is why that row is blank. Perhaps my example data is bad but I can't just put in the real data.

There are instances where the Shop Name and/or Employee field is blank i.e. only the location is specified. If that doesn't make sense as to why it would be like that then forget the actual example and concentrate more on which fields are filled out in the original and the desired table. The desired table is supposed to look like a pivot table. Let me redraw


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65"]Location[/TD]
[TD="class: cms_table_xl65"]Shop Name[/TD]
[TD="class: cms_table_xl65, width: 64"]Employee[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]France1[/TD]
[TD="class: cms_table_xl66"]BLANK[/TD]
[TD="class: cms_table_xl66"]BLANK[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Germany[/TD]
[TD="class: cms_table_xl66"]Aldi[/TD]
[TD="class: cms_table_xl66"]Sally[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Germany[/TD]
[TD="class: cms_table_xl66"]Aldi[/TD]
[TD="class: cms_table_xl66"]Harry[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]Germany[/TD]
[TD="class: cms_table_xl66"]Aldi[/TD]
[TD="class: cms_table_xl66"]Peter[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]France2[/TD]
[TD="class: cms_table_xl66"]Carrefour[/TD]
[TD="class: cms_table_xl66"]Francis[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]France2[/TD]
[TD="class: cms_table_xl66"]Carrefour[/TD]
[TD="class: cms_table_xl66"]Petra[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]USA[/TD]
[TD="class: cms_table_xl66"]Walmart[/TD]
[TD="class: cms_table_xl66"]BLANK[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]UK[/TD]
[TD="class: cms_table_xl66"]Tesco[/TD]
[TD="class: cms_table_xl66"]BLANK[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]UK[/TD]
[TD="class: cms_table_xl66"]Morrison's[/TD]
[TD="class: cms_table_xl66"]Jerry[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry138889]

Please check if there's a "write your own report" option. Could a new report with three columns (Location, Shop Name, Employee) be created?

Lol, unfortunately not!

 

[sandy666]

as mentioned earlier your source data is ambiguous so hard to create something what doesn't exist.
there is no ID for France = blank so France taking all elements from Shop Name and Employee

btw. I don't care about your real data but I'd like to see real structure and type of data in table form (not in description - in case from above I assumed all is text or blank = null) then we can talk again

edit:
don't quote whole post ! I know what I said and post. Just enough: @sandy666 if you answer to me

 

[Jerry138889]

@sandy666
I appreciate what you're saying but the way I have presented the data is how it is output by the system. Why it exports like that I don't know nor can I control.


Let me try another, more realistic example. Here, I have kept the exact structure of the first 25 lines of data but renamed/relabeled appropriately.Maybe I can make the question a bit easier. Instead of trying to turn this into pivot table friendly data, can I just get a formula for a count on how many legal entities are associated with each portfolio?

System output:

[TABLE="width: 118"]
<tbody>[TR]
[TD]Portfolio[/TD]
[/TR]
[TR]
[TD]Portfolio A[/TD]
[/TR]
[TR]
[TD]Portfolio B[/TD]
[/TR]
[TR]
[TD]Legal Entity[/TD]
[/TR]
[TR]
[TD]Legal Entity 1[/TD]
[/TR]
[TR]
[TD]Class ID[/TD]
[/TR]
[TR]
[TD]Class I[/TD]
[/TR]
[TR]
[TD]Class II[/TD]
[/TR]
[TR]
[TD]Class III[/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[/TR]
[TR]
[TD]Portfolio C[/TD]
[/TR]
[TR]
[TD]Legal Entity[/TD]
[/TR]
[TR]
[TD]Legal Entity 2[/TD]
[/TR]
[TR]
[TD]Class ID[/TD]
[/TR]
[TR]
[TD]Class II[/TD]
[/TR]
[TR]
[TD]Class I[/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[/TR]
[TR]
[TD]Portfolio D[/TD]
[/TR]
[TR]
[TD]Legal Entity[/TD]
[/TR]
[TR]
[TD]Legal Entity 3[/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[/TR]
[TR]
[TD]Portfolio E[/TD]
[/TR]
[TR]
[TD]Portfolio F[/TD]
[/TR]
[TR]
[TD]Legal Entity[/TD]
[/TR]
[TR]
[TD]Legal Entity 4[/TD]
[/TR]
</tbody>[/TABLE]

Which really means this:

[TABLE="width: 285"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Legal Entity[/TD]
[TD]Class ID[/TD]
[/TR]
[TR]
[TD]Portfolio A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio B[/TD]
[TD]Legal Entity 1[/TD]
[TD]Class I[/TD]
[/TR]
[TR]
[TD]Portfolio B[/TD]
[TD]Legal Entity 1[/TD]
[TD]Class II[/TD]
[/TR]
[TR]
[TD]Portfolio B[/TD]
[TD]Legal Entity 1[/TD]
[TD]Class III[/TD]
[/TR]
[TR]
[TD]Portfolio C[/TD]
[TD]Legal Entity 2[/TD]
[TD]Class I[/TD]
[/TR]
[TR]
[TD]Portfolio C[/TD]
[TD]Legal Entity 2[/TD]
[TD]Class II[/TD]
[/TR]
[TR]
[TD]Portfolio D[/TD]
[TD]Legal Entity 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio F[/TD]
[TD]Legal Entity 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i.e. Portfolio A does not have any classes or entities associated with it, nor does Portfolio E. Portfolio D has no classes.

None of the portfolios "fold up" into the one above. Each is a separate line item. Just because the system exports Portfolio A and B under the same Portfolio heading does not mean they're related. I don't know why it exports like that.

Also, I don't understand how to paste in a table here so apologies for that.