Convert Data in Sub-Total Format to Pivot Friendly

Jerry138889

New Member
Joined
May 23, 2013
Messages
28
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>


LocationShop Name Employee
France
Germany Aldi Sally
Harry
Peter
FranceCarrefour Francis
Petra
USAWalmart
UKTesco
Morrison's Jerry

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

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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

New Member
Joined
May 23, 2013
Messages
28
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
 
Last edited:

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,789
something like this?

Option1
CNTLocationShop NameEmployee
1​
France,GermanyAldiSally,Harry,Peter
2​
FranceCarrefourFrancis,Petra
3​
USAWalmart
4​
UKTesco,Morrison'sJerry
Option2
CNTLocationShop NameEmployee
1​
FranceAldiSally
1​
FranceAldiHarry
1​
FranceAldiPeter
1​
GermanyAldiSally
1​
GermanyAldiHarry
1​
GermanyAldiPeter
2​
FranceCarrefourFrancis
2​
FranceCarrefourPetra
3​
USAWalmart
4​
UKTescoJerry
4​
UKMorrison'sJerry
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,789
and Option2 with PivotTable:

LocationShop NameEmployee
FranceAldiHarry
Peter
Sally
CarrefourFrancis
Petra
GermanyAldiHarry
Peter
Sally
UKMorrison'sJerry
TescoJerry
USAWalmart(blank)
 
Last edited:

Jerry138889

New Member
Joined
May 23, 2013
Messages
28
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,GermanyAldiSally,Harry,Peter
2​
FranceCarrefourFrancis,Petra
3​
USAWalmart
4​
UKTesco,Morrison'sJerry
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​
FranceAldiSally
1​
FranceAldiHarry
1​
FranceAldiPeter
1​
GermanyAldiSally
1​
GermanyAldiHarry
1​
GermanyAldiPeter
2​
FranceCarrefourFrancis
2​
FranceCarrefourPetra
3​
USAWalmart
4​
UKTescoJerry
4​
UKMorrison'sJerry

<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;">
LocationShop NameEmployee
France1BLANKBLANK
GermanyAldiSally
GermanyAldiHarry
GermanyAldiPeter
France2CarrefourFrancis
France2CarrefourPetra
USAWalmartBLANK
UKTescoBLANK
UKMorrison'sJerry

<tbody>
</tbody>
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,789
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
 
Last edited:

Jerry138889

New Member
Joined
May 23, 2013
Messages
28
@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:

PortfolioLegal EntityClass ID
Portfolio A
Portfolio BLegal Entity 1Class I
Portfolio BLegal Entity 1Class II
Portfolio BLegal Entity 1Class III
Portfolio CLegal Entity 2Class I
Portfolio CLegal Entity 2Class II
Portfolio DLegal Entity 3
Portfolio E
Portfolio FLegal 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.
 

Forum statistics

Threads
1,082,018
Messages
5,362,702
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top