transform table into list - unpivot to table

[laserx]

Hi All,

I have a table of quantity of hearing instruments sold by date that I would like to transform into a list (3 columns): item; date; qty

As a total I have:
1. 114 different dates in columns
2. 760 different items in rows


Please have a look at the data below

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

I'll appriciate all kind of support!!! Thank you in advance!!!
INPUT
[TABLE="class: grid, width: 397"]
<TBODY>[TR]
[TD]Item</SPAN>[/TD]
[TD="align: right"]2012-03-08</SPAN>[/TD]
[TD="align: right"]2012-03-09</SPAN>[/TD]
[TD="align: right"]2012-03-12</SPAN>[/TD]
[TD="align: right"]2012-03-13</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-110-00</SPAN>[/TD]
[TD="align: right"]-78</SPAN>[/TD]
[TD="align: right"]-133</SPAN>[/TD]
[TD="align: right"]-38</SPAN>[/TD]
[TD="align: right"]-83</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-113-00</SPAN>[/TD]
[TD="align: right"]-17</SPAN>[/TD]
[TD="align: right"]-28</SPAN>[/TD]
[TD="align: right"]-8</SPAN>[/TD]
[TD="align: right"]-36</SPAN>[/TD]
[/TR]
[TR]
[TD]108832</SPAN>[/TD]
[TD="align: right"]-23</SPAN>[/TD]
[TD="align: right"]-50</SPAN>[/TD]
[TD="align: right"]-18</SPAN>[/TD]
[TD="align: right"]-70</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=3></COLGROUP>[/TABLE]

Desired
OUTPUT
[TABLE="class: outer_border, width: 283"]
<TBODY>[TR]
[TD]item</SPAN>[/TD]
[TD]date</SPAN>[/TD]
[TD]qty</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-110-00</SPAN>[/TD]
[TD="align: right"]2012-03-08</SPAN>[/TD]
[TD="align: right"]-78</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-110-00</SPAN>[/TD]
[TD="align: right"]2012-03-09</SPAN>[/TD]
[TD="align: right"]-133</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-110-00</SPAN>[/TD]
[TD="align: right"]2012-03-12</SPAN>[/TD]
[TD="align: right"]-38</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-110-00</SPAN>[/TD]
[TD="align: right"]2012-03-13</SPAN>[/TD]
[TD="align: right"]-83</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-113-00</SPAN>[/TD]
[TD="align: right"]2012-03-08</SPAN>[/TD]
[TD="align: right"]-23</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-113-00</SPAN>[/TD]
[TD="align: right"]2012-03-09</SPAN>[/TD]
[TD="align: right"]-50</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-113-00</SPAN>[/TD]
[TD="align: right"]2012-03-12</SPAN>[/TD]
[TD="align: right"]-18</SPAN>[/TD]
[/TR]
[TR]
[TD]102-76-113-00</SPAN>[/TD]
[TD="align: right"]2012-03-13</SPAN>[/TD]
[TD="align: right"]-70</SPAN>[/TD]
[/TR]
[TR]
[TD]108832</SPAN>[/TD]
[TD="align: right"]2012-03-08</SPAN>[/TD]
[TD="align: right"]-23</SPAN>[/TD]
[/TR]
[TR]
[TD]108832</SPAN>[/TD]
[TD="align: right"]2012-03-09</SPAN>[/TD]
[TD="align: right"]-50</SPAN>[/TD]
[/TR]
[TR]
[TD]108832</SPAN>[/TD]
[TD="align: right"]2012-03-12</SPAN>[/TD]
[TD="align: right"]-18</SPAN>[/TD]
[/TR]
[TR]
[TD]108832</SPAN>[/TD]
[TD="align: right"]2012-03-13</SPAN>[/TD]
[TD="align: right"]-70</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[MickG]

Try this:-
Results sheet (2)

[COLOR="Navy"]Sub[/COLOR] MG14Aug07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] RngAc [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c
c = 2
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Set[/COLOR] RngAc = Range(Range("B" & Dn.Row), Cells(Dn.Row, Columns.count).End(xlToLeft))
        [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
            .Range("A1").Resize(, 3) = Array("Item", "Date", "Qty")
            .Cells(c, 1).Resize(RngAc.count) = Dn
            .Cells(c, 2).Resize(RngAc.count) = Application.Transpose(Rng(1).Offset(-1, 1).Resize(, RngAc.count).Value)
            .Cells(c, 3).Resize(RngAc.count) = Application.Transpose(RngAc.Value)
            c = c + RngAc.count
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[laserx]

It works, thanks a lot for helping me!!!

BR
Pawel

 

[MickG]

Your welcome
Regards Mick