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

Item</SPAN>	2012-03-08</SPAN>	2012-03-09</SPAN>	2012-03-12</SPAN>	2012-03-13</SPAN>
102-76-110-00</SPAN>	-78</SPAN>	-133</SPAN>	-38</SPAN>	-83</SPAN>
102-76-113-00</SPAN>	-17</SPAN>	-28</SPAN>	-8</SPAN>	-36</SPAN>
108832</SPAN>	-23</SPAN>	-50</SPAN>	-18</SPAN>	-70</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3></COLGROUP>

Desired
OUTPUT

item</SPAN>	date</SPAN>	qty</SPAN>
102-76-110-00</SPAN>	2012-03-08</SPAN>	-78</SPAN>
102-76-110-00</SPAN>	2012-03-09</SPAN>	-133</SPAN>
102-76-110-00</SPAN>	2012-03-12</SPAN>	-38</SPAN>
102-76-110-00</SPAN>	2012-03-13</SPAN>	-83</SPAN>
102-76-113-00</SPAN>	2012-03-08</SPAN>	-23</SPAN>
102-76-113-00</SPAN>	2012-03-09</SPAN>	-50</SPAN>
102-76-113-00</SPAN>	2012-03-12</SPAN>	-18</SPAN>
102-76-113-00</SPAN>	2012-03-13</SPAN>	-70</SPAN>
108832</SPAN>	2012-03-08</SPAN>	-23</SPAN>
108832</SPAN>	2012-03-09</SPAN>	-50</SPAN>
108832</SPAN>	2012-03-12</SPAN>	-18</SPAN>
108832</SPAN>	2012-03-13</SPAN>	-70</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[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