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:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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