Macro help to transpose data in stacked form

F

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
328
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I
May someone help with with this.

I'd like to tabulate this stacked structure of parameters (column A) and values(column B) that has multiple blocks as shown below
in range A1:B3 in the way shown in D1:L14.

First block of data (first row to be tabulated), begins with "WORLD" and begins with "CAPITAL". Then could continue 0 o more blocks containing
only a few parameters, that begins with "COUNTRYCODE" and ends with "CAPITAL". Then could be others blocks that begins with "CONTINENT"
and ends with "PRM". Then could be others blocks that contains only "PRM".

Thee headers in output would be the unique parameters from column A, except "POLCODE" that is not needed to print in output. Each block that
begins with "WORLD" or "COUNTRYCODE" would be a new row in output. For "PRM" values extract the numbers after "ncc" and after "kng".

How to know if "COUNTRYCODE" belongs or not to a bigger block that begins with "WORLD"? Well, one row only could have the value of each
parameter. If one parameter appears again, should go in another row. The same applies to "PRM".

Thanks in advance for any help

COUNTRIES.xlsx
ABCDEFGHIJKL
1WORLD1WORLD CONTINENTCONTINENTNAMECONTINENTCODEGOVERNMENTCOUNTRYCODECOUNTRYCAPITALPRM
2CONTINENT111AMERICAAME01PRE55BRAZILBRASILIA
3CONTINENTNAMEAMERICA11AMERICAAME01PRE51PERULIMA
4CONTINENTCODEAME0111AMERICAAME01PRE1784SAINT_VINCENTKINGSTOWN
5GOVERNMENTPRE13EUROPEEUR03KNGD359BULGARIASOFIA
6COUNTRYCODE5513EUROPEEUR03KNGD36HUNGARYBUDAPEST
7COUNTRYBRAZIL2ASIAEXC35KNGD060-458
8CAPITALBRASILIA2ASIAEXC35KNGD392-392
9COUNTRYCODE515AFRICAEXC35PRE231-251
10COUNTRYPERU5AFRICAEXC35PRE324-224
11CAPITALLIMA5AFRICAEXC35PRE450-261
12COUNTRYCODE17845AFRICAEXC35PRE686-221
13COUNTRYSAINT_VINCENT5AFRICAEXC35PRE788-216
14CAPITALKINGSTOWN5AFRICAEXC35PRE148-235
15WORLD1
16CONTINENT3
17CONTINENTNAMEEUROPE
18CONTINENTCODEEUR03
19GOVERNMENTKNGD
20COUNTRYCODE359
21COUNTRYBULGARIA
22CAPITALSOFIA
23COUNTRYCODE36
24COUNTRYHUNGARY
25CAPITALBUDAPEST
26CONTINENT2
27CONTINENTNAMEASIA
28CONTINENTCODEEXC35
29GOVERNMENTKNGD
30POLCODEASIRPL2
31PRMkng.cc060.ncc458.svfdata
32PRMkng.cc392.ncc392.svfdata
33CONTINENT5
34CONTINENTNAMEAFRICA
35CONTINENTCODEEXC35
36GOVERNMENTPRE
37POLCODEAFRRPL5
38PRMpre.cc231.ncc251.svfdata
39PRMpre.cc324.ncc224.svfdata
40PRMpre.cc450.ncc261.svfdata
41PRMpre.cc686.ncc221.svfdata
42PRMpre.cc788.ncc216.svfdata
43PRMpre.cc148.ncc235.svfdata
Sheet7
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Is the xml string from where this list was generated available? And which is your Office version?
 
Upvote 0
I mean that the list in column A is probabling coming from an xml structred string, a sequence of nested attributes (CONTINENT, CONTINENTNAME, etc) and values (1, AMERICA, etc).
For example:
Rich (BB code): 
<zipcodes>
  <object>
    <country_code>AT</country_code>
    <zipcode>7000</zipcode>
    <place>Sankt Georgen am Leithagebirge</place>
    <state>Burgenland</state>
    <state_code>01</state_code>
    <province>Eisenstadt Stadt</province>
    <province_code>101</province_code>
    <community>Eisenstadt</community>
    <community_code>10101</community_code>
    <latitude>47.85</latitude>
    <longitude>16.55</longitude>
  </object>
  <object>
    <country_code>AT</country_code>
    <zipcode>7000</zipcode>
    <place>Kleinhöflein im Burgenland</place>
    <state>Burgenland</state>
    <state_code>01</state_code>
    <province>Eisenstadt Stadt</province>
    <province_code>101</province_code>
    <community>Eisenstadt</community>
    <community_code>10101</community_code>
    <latitude>47.8415</latitude>
    <longitude>16.5041</longitude>
  </object>
  <object>
    <country_code>AT</country_code>
    <zipcode>7000</zipcode>
    <place>Eisenstadt</place>
    <state>Burgenland</state>
    <state_code>01</state_code>
    <province>Eisenstadt Stadt</province>
    <province_code>101</province_code>
    <community>Eisenstadt</community>
    <community_code>10101</community_code>
    <latitude>47.8457</latitude>
    <longitude>16.5233</longitude>
  </object>
.
.
</zipcodes>


These strings can be partitioned using the function FILTERXML available in your Office version.
Working on the supplied list is an unnecessary complication, but can be done using a macro
 
Upvote 0
Anthony47 said:
I mean that the list in column A is probabling coming from an xml structred string, a sequence of nested attributes (CONTINENT, CONTINENTNAME, etc) and values (1, AMERICA, etc).
For example:
Rich (BB code): 
<zipcodes>
  <object>
    <country_code>AT</country_code>
    <zipcode>7000</zipcode>
    <place>Sankt Georgen am Leithagebirge</place>
    <state>Burgenland</state>
    <state_code>01</state_code>
    <province>Eisenstadt Stadt</province>
    <province_code>101</province_code>
    <community>Eisenstadt</community>
    <community_code>10101</community_code>
    <latitude>47.85</latitude>
    <longitude>16.55</longitude>
  </object>
  <object>
    <country_code>AT</country_code>
    <zipcode>7000</zipcode>
    <place>Kleinhöflein im Burgenland</place>
    <state>Burgenland</state>
    <state_code>01</state_code>
    <province>Eisenstadt Stadt</province>
    <province_code>101</province_code>
    <community>Eisenstadt</community>
    <community_code>10101</community_code>
    <latitude>47.8415</latitude>
    <longitude>16.5041</longitude>
  </object>
  <object>
    <country_code>AT</country_code>
    <zipcode>7000</zipcode>
    <place>Eisenstadt</place>
    <state>Burgenland</state>
    <state_code>01</state_code>
    <province>Eisenstadt Stadt</province>
    <province_code>101</province_code>
    <community>Eisenstadt</community>
    <community_code>10101</community_code>
    <latitude>47.8457</latitude>
    <longitude>16.5233</longitude>
  </object>
.
.
</zipcodes>


These strings can be partitioned using the function FILTERXML available in your Office version.
Working on the supplied list is an unnecessary complication, but can be done using a macro
Click to expand...
I understand, but is not coming from an XML file. The source comes from a text file that doesn´t have xml structure.
 
Upvote 0
Try thi macro:
VBA Code: 
Sub PseudoXmlParse()
Dim xStruct, srPos As Range, dePos As Range
Dim OneLine() As String, I As Long, myMatch, ccVal, ccTag
Dim WData As Boolean, rmData As Long
'
Set srPos = Sheets("Sheet7").Range("A1")            '<<< The source data
Set dePos = Sheets("Sheet7").Range("D1")            '<<< The destination
'
xStruct = Range(dePos, dePos.End(xlToRight)).Value  '!!! Line 1 of dePos contains the "schema"
ReDim OneLine(1 To UBound(xStruct, 2))
'
For I = 1 To srPos.End(xlDown).Row
    ccTag = srPos.Cells(I, 1)
    myMatch = Application.Match(ccTag, xStruct, False)
    If Not IsError(myMatch) Then
        If myMatch > rmData Then
            OneLine(myMatch) = srPos.Cells(I, 2)
            WData = True
            rmData = myMatch
        Else
            If WData Then
                dePos.Offset(10000, 0).End(xlUp).Offset(1, 0).Resize(1, UBound(xStruct, 2)).Value = OneLine
                For j = myMatch To UBound(xStruct, 2)
                    OneLine(j) = ""
                Next j
                WData = False
            End If
            OneLine(myMatch) = srPos.Cells(I, 2)
            WData = True
            rmData = myMatch
        End If
    End If
Next I
If WData Then
    dePos.Offset(10000, 0).End(xlUp).Offset(1, 0).Resize(1, UBound(xStruct, 2)).Value = OneLine
End If
End Sub
The two lines marked <<< at beginning of the code need to be customized with your data

The output will be according the below xl2bb minisheet (the table after the red row)
MULTI_C30521.xlsm
CDEFGHIJKL
1WORLDCONTINENTCONTINENTNAMECONTINENTCODEGOVERNMENTCOUNTRYCODECOUNTRYCAPITALPRM
211AMERICAAME01PRE55BRAZILBRASILIA
311AMERICAAME01PRE51PERULIMA
411AMERICAAME01PRE1784SAINT_VINCENTKINGSTOWN
513EUROPEEUR03KNGD359BULGARIASOFIA
613EUROPEEUR03KNGD36HUNGARYBUDAPEST
72ASIAEXC35KNGD060-458
82ASIAEXC35KNGD392-392
95AFRICAEXC35PRE231-251
105AFRICAEXC35PRE324-224
115AFRICAEXC35PRE450-261
125AFRICAEXC35PRE686-221
135AFRICAEXC35PRE788-216
145AFRICAEXC35PRE148-235
15zzzzzz
1611AMERICAAME01PRE55BRAZILBRASILIA
1711AMERICAAME01PRE51PERULIMA
1811AMERICAAME01PRE1784SAINT_VINCENTKINGSTOWN
1913EUROPEEUR03KNGD359BULGARIASOFIA
2013EUROPEEUR03KNGD36HUNGARYBUDAPEST
2112ASIAEXC35KNGDkng.cc060.ncc458.svfdata
2212ASIAEXC35KNGDkng.cc392.ncc392.svfdata
2315AFRICAEXC35PREpre.cc231.ncc251.svfdata
2415AFRICAEXC35PREpre.cc324.ncc224.svfdata
2515AFRICAEXC35PREpre.cc450.ncc261.svfdata
2615AFRICAEXC35PREpre.cc686.ncc221.svfdata
2715AFRICAEXC35PREpre.cc788.ncc216.svfdata
2815AFRICAEXC35PREpre.cc148.ncc235.svfdata
29
Sheet7


D:\DDownloads\[MULTI_C30521.xlsm]Sheet7
 
Upvote 0
Solution
Anthony47 said:
Try thi macro:
VBA Code: 
Sub PseudoXmlParse()
Dim xStruct, srPos As Range, dePos As Range
Dim OneLine() As String, I As Long, myMatch, ccVal, ccTag
Dim WData As Boolean, rmData As Long
'
Set srPos = Sheets("Sheet7").Range("A1")            '<<< The source data
Set dePos = Sheets("Sheet7").Range("D1")            '<<< The destination
'
xStruct = Range(dePos, dePos.End(xlToRight)).Value  '!!! Line 1 of dePos contains the "schema"
ReDim OneLine(1 To UBound(xStruct, 2))
'
For I = 1 To srPos.End(xlDown).Row
    ccTag = srPos.Cells(I, 1)
    myMatch = Application.Match(ccTag, xStruct, False)
    If Not IsError(myMatch) Then
        If myMatch > rmData Then
            OneLine(myMatch) = srPos.Cells(I, 2)
            WData = True
            rmData = myMatch
        Else
            If WData Then
                dePos.Offset(10000, 0).End(xlUp).Offset(1, 0).Resize(1, UBound(xStruct, 2)).Value = OneLine
                For j = myMatch To UBound(xStruct, 2)
                    OneLine(j) = ""
                Next j
                WData = False
            End If
            OneLine(myMatch) = srPos.Cells(I, 2)
            WData = True
            rmData = myMatch
        End If
    End If
Next I
If WData Then
    dePos.Offset(10000, 0).End(xlUp).Offset(1, 0).Resize(1, UBound(xStruct, 2)).Value = OneLine
End If
End Sub
The two lines marked <<< at beginning of the code need to be customized with your data

The output will be according the below xl2bb minisheet (the table after the red row)
Click to expand...

Excellent Anthony, it works pretty nice. Thanks so much for your help and time.
 
Upvote 0
Anthony47 said:
Good, thank you for the feedback
Click to expand...
Hi again Anthony,

I've checked the FILTERXML function that I haven't used before. I converted the input to XML and is a lot easiest like you said.

The formulas I used, and the output that is correct, but I was wondering how to do the "Fill down" and remove the #N/A?

SAMPLES.xlsm
BCDEFGHIJK
1WORLDCONTINENTCONTINENTNAMECONTINENTCODEGOVERNMENTCOUNTRYCODECOUNTRYCAPITALPOLCODEPRM
211AMERICAAME01PRE55BRAZILBRASILIA  
3     51PERULIMA  
4     1784SAINT_VINCENTKINGSTOWN  
513EUROPEEUR03KNGD359BULGARIASOFIA  
61    36HUNGARYBUDAPEST  
7 2ASIA KNGD   ASIRPL2kng.cc060.ncc458.svfdata
8        ASIRPL2kng.cc392.ncc392.svfdata
9 5AFRICA PRE   AFRRPL5pre.cc231.ncc251.svfdata
10        AFRRPL5pre.cc324.ncc224.svfdata
11        AFRRPL5pre.cc450.ncc261.svfdata
12        AFRRPL5pre.cc686.ncc221.svfdata
13        AFRRPL5pre.cc788.ncc216.svfdata
14        AFRRPL5pre.cc148.ncc235.svfdata
15#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
16#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
17#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
18#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
19#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
20#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
Sheet8
Cell Formulas
RangeFormula
B2:K20B2=IFERROR(FILTERXML($A$1,"/COUNTRIES/group/"&B1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


In A1 is the XML input:
XML: 
"<?xml version=""1.0"" encoding=""UTF-8""?>
<COUNTRIES>
  <group>
    <WORLD>1</WORLD>
    <CONTINENT>1</CONTINENT>
    <CONTINENTNAME>AMERICA</CONTINENTNAME>
    <CONTINENTCODE>AME01</CONTINENTCODE>
    <GOVERNMENT>PRE</GOVERNMENT>
    <COUNTRYCODE>55</COUNTRYCODE>
    <COUNTRY>BRAZIL</COUNTRY>
    <CAPITAL>BRASILIA</CAPITAL>
    <POLCODE></POLCODE>
    <PRM></PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE>51</COUNTRYCODE>
    <COUNTRY>PERU</COUNTRY>
    <CAPITAL>LIMA</CAPITAL>
    <POLCODE></POLCODE>
    <PRM></PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE>1784</COUNTRYCODE>
    <COUNTRY>SAINT_VINCENT</COUNTRY>
    <CAPITAL>KINGSTOWN</CAPITAL>
    <POLCODE></POLCODE>
    <PRM></PRM>
  </group>
  <group>
    <WORLD>1</WORLD>
    <CONTINENT>3</CONTINENT>
    <CONTINENTNAME>EUROPE</CONTINENTNAME>
    <CONTINENTCODE>EUR03</CONTINENTCODE>
    <GOVERNMENT>KNGD</GOVERNMENT>
    <COUNTRYCODE>359</COUNTRYCODE>
    <COUNTRY>BULGARIA</COUNTRY>
    <CAPITAL>SOFIA</CAPITAL>
    <POLCODE></POLCODE>
    <PRM></PRM>
  </group>
  <group>
    <WORLD>1</WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE>36</COUNTRYCODE>
    <COUNTRY>HUNGARY</COUNTRY>
    <CAPITAL>BUDAPEST</CAPITAL>
    <POLCODE></POLCODE>
    <PRM></PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT>2</CONTINENT>
    <CONTINENTNAME>ASIA</CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT>KNGD</GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>ASIRPL2</POLCODE>
    <PRM>kng.cc060.ncc458.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>ASIRPL2</POLCODE>
    <PRM>kng.cc392.ncc392.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT>5</CONTINENT>
    <CONTINENTNAME>AFRICA</CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT>PRE</GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>AFRRPL5</POLCODE>
    <PRM>pre.cc231.ncc251.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>AFRRPL5</POLCODE>
    <PRM>pre.cc324.ncc224.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>AFRRPL5</POLCODE>
    <PRM>pre.cc450.ncc261.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>AFRRPL5</POLCODE>
    <PRM>pre.cc686.ncc221.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>AFRRPL5</POLCODE>
    <PRM>pre.cc788.ncc216.svfdata</PRM>
  </group>
  <group>
    <WORLD></WORLD>
    <CONTINENT></CONTINENT>
    <CONTINENTNAME></CONTINENTNAME>
    <CONTINENTCODE></CONTINENTCODE>
    <GOVERNMENT></GOVERNMENT>
    <COUNTRYCODE></COUNTRYCODE>
    <COUNTRY></COUNTRY>
    <CAPITAL></CAPITAL>
    <POLCODE>AFRRPL5</POLCODE>
    <PRM>pre.cc148.ncc235.svfdata</PRM>
  </group>
</COUNTRIES>"
 
Upvote 0
I used in B2 a formula that is slightly different than yours:
Excel Formula: 
=IFERROR(FILTERXML($A$1,"//COUNTRIES/group["&ROW(A1)&"]/"&B$1),"")
Then copied to the right and down without errors
Try this same formula

MULTI_C30521.xlsm
BCDEFGHIJKLM
1WORLDCONTINENTCONTINENTNAMECONTINENTCODEGOVERNMENTCOUNTRYCODECOUNTRYCAPITALPRM
211AMERICAAME01PRE55BRAZILBRASILIA 
3     51PERULIMA 
4     1784SAINT_VINCENTKINGSTOWN 
513EUROPEEUR03KNGD359BULGARIASOFIA 
61    36HUNGARYBUDAPEST 
7 2ASIA KNGD   kng.cc060.ncc458.svfdata
8        kng.cc392.ncc392.svfdata
9 5AFRICA PRE   pre.cc231.ncc251.svfdata
10        pre.cc324.ncc224.svfdata
11        pre.cc450.ncc261.svfdata
12        pre.cc686.ncc221.svfdata
13        pre.cc788.ncc216.svfdata
14        pre.cc148.ncc235.svfdata
15         
16         
Foglio1
Cell Formulas
RangeFormula
B2:J16B2=IFERROR(FILTERXML($A$1,"//COUNTRIES/group["&ROW(A1)&"]/"&B$1),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Help with formula to tabulate data in stacked form
2
Replies
15
Views
484
Eric W
Eric W
F
  • Question Question
How to tabulate text file with data shown in blocks?
Replies
3
Views
438
Fractalis
F

Forum statistics

Threads
1,224,814
Messages
6,181,121
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top