transform table into list / how to "unpivot" a table

fab54

New Member
Joined
Nov 14, 2008
Messages
30
Hi All,
I have a bit of a challenge for you...at least it is for me :)

I have a table of quantity of cars sold by brand (more than two dimensions) that I would like to tranform into a list (5 columns).

Please have a look at the data below

Your help is HIGHLY appreciated...as always ;)

PS:I get the xls files in the input format and I would like a list in order to use a pivot tables


INPUT
<table x:str="" style="border-collapse: collapse; width: 288pt;" width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="6" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">Show</td> <td class="xl25" style="width: 48pt;" width="64">Date</td> <td class="xl25" style="width: 48pt;" width="64">Seller</td> <td class="xl24" style="width: 48pt;" width="64">BMW</td> <td class="xl24" style="width: 48pt;" width="64">Audi</td> <td class="xl24" style="width: 48pt;" width="64">Mercedes</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">A</td> <td class="xl26" x:num="">5</td> <td class="xl26" x:num="">0</td> <td class="xl26" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">B</td> <td class="xl26" x:num="">5</td> <td class="xl26" x:num="">10</td> <td class="xl26" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39826">13-Jan</td> <td class="xl26">A</td> <td class="xl26" x:num="">0</td> <td class="xl26" x:num="">1</td> <td class="xl26" x:num="">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Munich</td> <td class="xl27" x:num="39839">26-Jan</td> <td class="xl26">C</td> <td class="xl26" x:num="">0</td> <td class="xl26" x:num="">4</td> <td class="xl26" x:num="">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr><td valign="top">Desired Output:
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Show</td> <td class="xl25">Date</td> <td class="xl25">Seller</td> <td class="xl25">Brand</td> <td class="xl25">Sold</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26"> A</td> <td class="xl26">BMW</td> <td class="xl26" x:num="">5</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">B</td> <td class="xl26">BMW</td> <td class="xl26" x:num="">5</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39825">12-Jan</td> <td class="xl26">B</td> <td class="xl26">Audi</td> <td class="xl26" x:num="">10</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39826">13-Jan</td> <td class="xl26">A</td> <td class="xl26">Audi</td> <td class="xl26" x:num="">1</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Geneva</td> <td class="xl27" x:num="39826">13-Jan</td> <td class="xl26">A</td> <td class="xl26">Mercedes</td> <td class="xl26" x:num="">3</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Munich</td> <td class="xl27" x:num="39839">26-Jan</td> <td class="xl26">C</td> <td class="xl26">Audi</td> <td class="xl26" x:num="">4</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">Munich</td> <td class="xl27" x:num="39839">26-Jan</td> <td class="xl26">C</td> <td class="xl26">Mercedes</td> <td class="xl26" x:num="">4</td> <td class="xl26">
</td> </tr> </tbody></table>
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
fab54,

Please setup your worksheet names as "Input" and "Output", and sheet "Output" titles as depicted below, before you run the macro.


Before the macro:


Excel Workbook
ABCDEF
1ShowDateSellerBMWAudiMercedes
2Geneva12-JanA500
3Geneva12-JanB5100
4Geneva13-JanA013
5Munich26-JanC044
6
Input



Excel Workbook
ABCDE
1ShowDateSellerBrandSold
2
3
4
5
6
7
8
9
Output



After the macro:


Excel Workbook
ABCDE
1ShowDateSellerBrandSold
2Geneva12-JanABMW5
3Geneva12-JanBBMW5
4Geneva12-JanBAudi10
5Geneva13-JanAAudi1
6Geneva13-JanAMercedes3
7Munich26-JanCAudi4
8Munich26-JanCMercedes4
9
Output




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Code:
Option Explicit
Sub MoveData()
Dim LC As Long, LRO As Long, NR As Long, HowMany As Long, a As Long, b As Long
Dim c As Range, rng As Range
Application.ScreenUpdating = False
LRO = Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Row
If LRO > 1 Then Sheets("Output").Range("A2:E" & LRO).ClearContents
NR = 2
With Sheets("Input")
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set rng = .Range(.Cells(c.Row, 3), .Cells(c.Row, LC))
    HowMany = Application.WorksheetFunction.CountIf(rng, ">0")
    If HowMany > 0 Then
      .Range("A" & c.Row & ":C" & c.Row).Copy Sheets("Output").Range("A" & NR & ":A" & NR + HowMany - 1)
      b = NR
      For a = 4 To LC Step 1
        If .Cells(c.Row, a).Value > 0 Then
          Sheets("Output").Range("D" & b) = .Cells(1, a)
          Sheets("Output").Range("E" & b) = .Cells(c.Row, a)
          b = b + 1
        End If
      Next a
      NR = NR + HowMany
    End If
  Next c
End With
Sheets("Output").Select
Application.ScreenUpdating = True
End Sub

Then run the "MoveData" macro.
 
Last edited:

fab54

New Member
Joined
Nov 14, 2008
Messages
30
Hey Hiker95,
Your Macro does exactly what I needed :biggrin:
Great job...Many thanks
 

fab54

New Member
Joined
Nov 14, 2008
Messages
30
Hiker,
I need your help a bit further :biggrin:
What changes do I have to make to the code in order to have only values in the output list?
The thing is that in the cells of the input table I have references to others cells in the same sheet (which are not kept in the output list)

Many thanks
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
fab54,

Try:

Code:
Option Explicit
Sub MoveData()
Dim LC As Long, LRO As Long, NR As Long, HowMany As Long, a As Long, b As Long
Dim c As Range, rng As Range
Application.ScreenUpdating = False
LRO = Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Row
If LRO > 1 Then Sheets("Output").Range("A2:E" & LRO).ClearContents
NR = 2
With Sheets("Input")
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set rng = .Range(.Cells(c.Row, 3), .Cells(c.Row, LC))
    HowMany = Application.WorksheetFunction.CountIf(rng, ">0")
    If HowMany > 0 Then
      .Range("A" & c.Row & ":C" & c.Row).Copy
      Sheets("Output").Range("A" & NR & ":A" & NR + HowMany - 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
      b = NR
      For a = 4 To LC Step 1
        If .Cells(c.Row, a).Value > 0 Then
          Sheets("Output").Range("D" & b).Value = .Cells(1, a).Value
          Sheets("Output").Range("E" & b).Value = .Cells(c.Row, a).Value
          b = b + 1
        End If
      Next a
      NR = NR + HowMany
    End If
  Next c
  Sheets("Output").Range("B2:B" & NR).NumberFormat = "dd-mmm"
End With
Sheets("Output").Select
Range("F1").Select
Application.ScreenUpdating = True
End Sub
 

me huong tra

New Member
Joined
Aug 6, 2011
Messages
5
I'm doing my thesis and face a very similar problem. I really appreciate if someone could help me. Thanks a lot.

INPUT

<table style="border-collapse: collapse; width: 288pt;" width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">Date
</td> <td class="xl25" style="width: 48pt;" width="64">Argentina</td><td class="xl25" style="width: 48pt;" width="64"></td> <td class="xl24" style="width: 48pt;" width="64">Brazil</td> <td class="xl24" style="width: 48pt;" width="64">China</td> <td class="xl24" style="width: 48pt;" width="64"> Egypt</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">1/1/2001</td> <td class="xl27">12</td> <td class="xl26">
</td> <td class="xl26">15
</td> <td class="xl26">67
</td> <td class="xl26"> 10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">2/1/2001
</td> <td class="xl27">12</td> <td class="xl26">
</td> <td class="xl26">14
</td> <td class="xl26">70
</td> <td class="xl26"> 9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">3/1/2001</td> <td class="xl27">13</td> <td class="xl26">
</td> <td class="xl26">13
</td> <td class="xl26">73
</td> <td class="xl26"> 8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">4/1/2001
</td> <td class="xl27">16</td> <td class="xl26">
</td> <td class="xl26">16
</td> <td class="xl26">75
</td> <td class="xl26"> 9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr><td valign="top">Desired Output:
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Date</td> <td class="xl25">Country</td> <td class="xl25"> Index</td><td class="xl25"></td> <td class="xl25">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">1/1/2001</td> <td class="xl27">Argentina
</td> <td class="xl26"> 12</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">2/1/2001</td> <td class="xl27">Argentina</td> <td class="xl26"> 12</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">3/1/2001
</td> <td class="xl27">Argentina</td> <td class="xl26"> 13</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">4/1/2001</td> <td class="xl27">Argentina</td> <td class="xl26"> 16</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">1/1/2001
</td> <td class="xl27">Brazil</td> <td class="xl26"> 15</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">2/1/2001</td> <td class="xl27">Brazil</td> <td class="xl26"> 14</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">3/1/2001</td> <td class="xl27">Brazil</td> <td class="xl26"> 13
</td> <td class="xl26">
</td> <td class="xl26">
</td></tr></tbody></table>and so on
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
me huong tra,

Welcome to the MrExcel forum.

This original post is very old.

Please create your own New Post with the same information as above.

In your own New Post you could include a link to this post as a reference.

You could also send me a Private Message with the link to your New Post, and I would be happy to assist you.

What are the worksheet names?


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:


I'm doing my thesis
What are you studying?

Is your request similar to asking for help on a homework assignment? If it is, then you should put some effort into solving the problem, and let us see the code you have written.
 

gusio

New Member
Joined
Feb 20, 2014
Messages
23
fab54,

Please setup your worksheet names as "Input" and "Output", and sheet "Output" titles as depicted below, before you run the macro.


Before the macro:


Input

*ABCDEF
1ShowDateSellerBMWAudiMercedes
2Geneva12-JanA500
3Geneva12-JanB5100
4Geneva13-JanA013
5Munich26-JanC044
6******

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:73px;"><col style="width:64px;"><col style="width:60px;"><col style="width:56px;"><col style="width:91px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Output

*ABCDE
1ShowDateSellerBrandSold
2*****
3*****
4*****
5*****
6*****
7*****
8*****
9*****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:73px;"><col style="width:64px;"><col style="width:91px;"><col style="width:56px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


After the macro:


Output

*ABCDE
1ShowDateSellerBrandSold
2Geneva12-JanABMW5
3Geneva12-JanBBMW5
4Geneva12-JanBAudi10
5Geneva13-JanAAudi1
6Geneva13-JanAMercedes3
7Munich26-JanCAudi4
8Munich26-JanCMercedes4
9*****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:73px;"><col style="width:64px;"><col style="width:91px;"><col style="width:56px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Code:
Option Explicit
Sub MoveData()
Dim LC As Long, LRO As Long, NR As Long, HowMany As Long, a As Long, b As Long
Dim c As Range, rng As Range
Application.ScreenUpdating = False
LRO = Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Row
If LRO > 1 Then Sheets("Output").Range("A2:E" & LRO).ClearContents
NR = 2
With Sheets("Input")
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set rng = .Range(.Cells(c.Row, 3), .Cells(c.Row, LC))
    HowMany = Application.WorksheetFunction.CountIf(rng, ">0")
    If HowMany > 0 Then
      .Range("A" & c.Row & ":C" & c.Row).Copy Sheets("Output").Range("A" & NR & ":A" & NR + HowMany - 1)
      b = NR
      For a = 4 To LC Step 1
        If .Cells(c.Row, a).Value > 0 Then
          Sheets("Output").Range("D" & b) = .Cells(1, a)
          Sheets("Output").Range("E" & b) = .Cells(c.Row, a)
          b = b + 1
        End If
      Next a
      NR = NR + HowMany
    End If
  Next c
End With
Sheets("Output").Select
Application.ScreenUpdating = True
End Sub

Then run the "MoveData" macro.
This code runs great , however does not apply to my table I have the following data Would you be able to help me to change the code around to get my needed results
0ABC123
0ABC124 6.55 6.55 7.06 7.64
0ABC125
0ABC126
0ABC127 123.24 123.24 132.72 143.78
0ABC128 123.24
0ABC129 11.7 11.7 12.6 13.65
0ABC130 8.64 8.64 10.08 10.08
0ABC131 8.64 8.64 10.08 10.08

I am looking for a table listing like this: 3 column , however I don't want to list items where the third column is null, need to skip. The end result should be like this.

a 0ABC123
b 0ABC123
c 0ABC123
d 0ABC123
a 0ABC124 6.55
b 0ABC124 6.55
c 0ABC124 7.06
d 0ABC124 7.64
a 0ABC125
b 0ABC125
c 0ABC125
d 0ABC125
a 0ABC126
b 0ABC126
c 0ABC126
d 0ABC126
a 0ABC127 123.24
b 0ABC127 123.24
c 0ABC127 132.72
d 0ABC127 143.78
a 0ABC128 123.24
b 0ABC128
c 0ABC128
d 0ABC128
a 0ABC129 11.7
b 0ABC129 11.7
c 0ABC129 12.6
d 0ABC129 13.65
a 0ABC130 8.64
b 0ABC130 8.64
c 0ABC130 10.08
d 0ABC130 10.08
a 0ABC131 8.64
b 0ABC131 8.64
c 0ABC131 10.08
d 0ABC131 10.08
Any help would be appreciated.
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top