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.

Location

France

Germany

Shop Name

Aldi

Employee

Sally

Harry

Peter

Location

France

Shop Name

Carrefour

Employee

Francis

Petra

Location

USA

Shop Name

Walmart

Location

UK

Shop Name

Tesco

Morrison's

Employee

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

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

Location	Shop Name	Employee
France
Germany	Aldi	Sally
		Harry
		Peter
France	Carrefour	Francis
		Petra
USA	Walmart
UK	Tesco
	Morrison's	Jerry

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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?

	Option1
CNT	Location	Shop Name	Employee
1​	France,Germany	Aldi	Sally,Harry,Peter
2​	France	Carrefour	Francis,Petra
3​	USA	Walmart
4​	UK	Tesco,Morrison's	Jerry
	Option2
CNT	Location	Shop Name	Employee
1​	France	Aldi	Sally
1​	France	Aldi	Harry
1​	France	Aldi	Peter
1​	Germany	Aldi	Sally
1​	Germany	Aldi	Harry
1​	Germany	Aldi	Peter
2​	France	Carrefour	Francis
2​	France	Carrefour	Petra
3​	USA	Walmart
4​	UK	Tesco	Jerry
4​	UK	Morrison's	Jerry

 

[sandy666]

and Option2 with PivotTable:

Location	Shop Name	Employee
France	Aldi	Harry
		Peter
		Sally
	Carrefour	Francis
		Petra
Germany	Aldi	Harry
		Peter
		Sally
UK	Morrison's	Jerry
	Tesco	Jerry
USA	Walmart	(blank)

 

[Jerry138889]

something like this?

	Option1
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CNT[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Location[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Shop Name[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Employee[/COLOR]
1​	France,Germany	Aldi	Sally,Harry,Peter
2​	France	Carrefour	Francis,Petra
3​	USA	Walmart
4​	UK	Tesco,Morrison's	Jerry
	Option2
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CNT[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Location[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Shop Name[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Employee[/COLOR]
1​	France	Aldi	Sally
1​	France	Aldi	Harry
1​	France	Aldi	Peter
1​	Germany	Aldi	Sally
1​	Germany	Aldi	Harry
1​	Germany	Aldi	Peter
2​	France	Carrefour	Francis
2​	France	Carrefour	Petra
3​	USA	Walmart
4​	UK	Tesco	Jerry
4​	UK	Morrison's	Jerry

<tbody>
</tbody>

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;">

Location	Shop Name	Employee
France1	BLANK	BLANK
Germany	Aldi	Sally
Germany	Aldi	Harry
Germany	Aldi	Peter
France2	Carrefour	Francis
France2	Carrefour	Petra
USA	Walmart	BLANK
UK	Tesco	BLANK
UK	Morrison's	Jerry

<tbody>
</tbody>

 

[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:

Portfolio

Portfolio A

Portfolio B

Legal Entity

Legal Entity 1

Class ID

Class I

Class II

Class III

Portfolio

Portfolio C

Legal Entity

Legal Entity 2

Class ID

Class II

Class I

Portfolio

Portfolio D

Legal Entity

Legal Entity 3

Portfolio

Portfolio E

Portfolio F

Legal Entity

Legal Entity 4

<tbody>
</tbody>

Which really means this:

Portfolio	Legal Entity	Class ID
Portfolio A
Portfolio B	Legal Entity 1	Class I
Portfolio B	Legal Entity 1	Class II
Portfolio B	Legal Entity 1	Class III
Portfolio C	Legal Entity 2	Class I
Portfolio C	Legal Entity 2	Class II
Portfolio D	Legal Entity 3
Portfolio E
Portfolio F	Legal Entity 4

<tbody>
</tbody>

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.