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]

 

[sandy666]

no problem with a table it's only easier to copy structure to the sheet.

if you want formula for countin' Legals for each Portfolio you need wait for any master of formula

Have a nice day

 

[GR00007]

Worked up a VBA that will give what you want based on sequence of raw data only. If you place the raw data on a tab called "Original" and have an empty tab called "Parsed" this should work.

Sub CvrtToPvtFrendly()
    Dim wsOriginal As Worksheet
    Dim wsParsed As Worksheet
    Set wsOriginal = ActiveWorkbook.Sheets("Original")
    Set wsParsed = ActiveWorkbook.Sheets("Parsed")
    vOrigLR = wsOriginal.UsedRange.Rows.Count   'Set last row
    wsParsed.Range("A1").Value = "Portfolio"    'Headers for new data
    wsParsed.Range("B1").Value = "Legal Entity"
    wsParsed.Range("C1").Value = "Class ID"
    vParseRow = 1                               'Set parsed row at 1
    hColA = ""                                  'Initialize hold and column indicator
    hColB = ""
    hColC = ""
    CurCol = ""
    For r = 1 To vOrigLR                        'Iterate through original data
        vRaw = wsOriginal.Range("A" & r).Value  'Current row value
        'Determine column of data to process and clear holds appropriately
        If vRaw = "Portfolio" Or vRaw = "Legal Entity" Or vRaw = "Class ID" Then
            If vRaw = "Portfolio" Then
                hColA = ""
                hColB = ""
                hColC = ""
                CurCol = "A"
            ElseIf vRaw = "Legal Entity" Then
                hColB = ""
                hColC = ""
                CurCol = "B"
            Else
                hColC = ""
                CurCol = "C"
            End If
        Else
            If CurCol = "A" Then                'Depending on collumn and hold increment row
                vParseRow = vParseRow + 1
            ElseIf CurCol = "B" Then
                If hColB <> "" Then vParseRow = vParseRow + 1
            Else
                If hColC <> "" Then vParseRow = vParseRow + 1
            End If
            If CurCol = "A" Then                'Populate output with appropriate data
                hColA = vRaw
                wsParsed.Range("A" & vParseRow).Value = hColA
            ElseIf CurCol = "B" Then
                hColB = vRaw
                wsParsed.Range("A" & vParseRow).Value = hColA
                wsParsed.Range("B" & vParseRow).Value = hColB
            Else
                hColC = vRaw
                wsParsed.Range("A" & vParseRow).Value = hColA
                wsParsed.Range("B" & vParseRow).Value = hColB
                wsParsed.Range("C" & vParseRow).Value = hColC
            End If
        End If
    Next
End Sub

 

[Jerry138889]

Hey @GR00007

Really appreciate your effort. That didn't seem to work for me. I created a new book with named tabs as you described and ran the macro. Data populated the Parsed tab. However, all the original data went into Column A while only the headers went into columns B and C so now it looks like this:

[TABLE="width: 224"]
<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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class ID[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class I[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class II[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class III[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class ID[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class II[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I ended up doing a really rough count formula that sort of gave me the answer I was looking for even though it's not great and I'm lucky there's only a few notable values.

So, again thank you, and also thank you to everybody else for their help. We can probably close this as I have got to where I needed.

I essentially filtered on "portfolio" and in column B gave a value for 1 every time it said portfolio. Then i filtered on Legal Entity and gave a value of 2. In column C, I did a sum formula to see if the value was equal to 3 (which would be the case if there was only one Legal Entity, thereby highlighting any cases where there were >1 legal entities as the value would not equal 3. That worked well enough to give me my answer, and although manual and not very clean, it allowed me to see how many portfolios had >1 legal entities associated with them.

[TABLE="width: 436"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] Sum[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Ignored as first value[/TD]
[/TR]
[TR]
[TD]Portfolio A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LegalEntity[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD] (=sum(B4+B6)[/TD]
[/TR]
[TR]
[TD]Portfolio B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LegalEntity[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Legal Entity 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Portfolio[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD] (=sum(B11+B13)
=/= 1 legal entity - Investigate[/TD]
[/TR]
[TR]
[TD]Portfolio C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[GR00007]

On the Original tab that code was expecting the column headers to be exactly Portfolio, Legal Entity, and Class ID. It appears the headers used actually have a cr/lf between the words in columns B and C, so it couldn't find them.

Oh, well, glad you were able to get the information you needed anyway.

 

[Jerry138889]

Ah, very good! Thanks again. I do appreciate your help and effort.