Table to list

[rcommer]

Hi,

I have asked this question in the main excel forum but have no successful response. I am hoping the you guys can help me here:

I have a series of tables like one below

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]

[/TD]
[TD]Monday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Tuesday
[/TD]
[TD]Tuesday
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]Morning
[/TD]
[TD]Afternoon
[/TD]
[TD]Morning
[/TD]
[TD]Afternoon
[/TD]
[TD]Evening
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]Class A
[/TD]
[TD]Class A
[/TD]
[TD]Class B
[/TD]
[TD]Class C
[/TD]
[TD]Class C
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]Unit 6
[/TD]
[TD]Unit 6
[/TD]
[TD]Unit 5
[/TD]
[TD]Unit 5
[/TD]
[TD]Unit 5
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD]Sally
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]Peter
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[/TR]
</tbody>[/TABLE]


I wonder if there is an efficient way to change this table into a list like below:

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Tom
[/TD]
[TD]Monday
[/TD]
[TD]Morning
[/TD]
[TD]Class A
[/TD]
[TD]Unit 6
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]Monday
[/TD]
[TD]Afternoon
[/TD]
[TD]Class A
[/TD]
[TD]Unit 6
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]Tuesday
[/TD]
[TD]Morning
[/TD]
[TD]Class B
[/TD]
[TD]unit 5
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions?

Thanks,
Com
<!-- / attachments -->

 

[MickG]

Try this:-
Note your data assumed to start row 2.
Results on sheet2, Starting row 2.

[COLOR=Navy]Sub[/COLOR] MG02Jul53
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Ac          [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] c           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
ReDim ray(1 To Rng.Count * Lst, 1 To 6)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]For[/COLOR] Ac = 2 To Lst
        [COLOR=Navy]If[/COLOR] Dn <> "" [COLOR=Navy]Then[/COLOR]
            c = c + 1
            ray(c, 1) = Dn: ray(c, 2) = Cells(2, Ac): ray(c, 3) = Cells(3, Ac)
            ray(c, 4) = Cells(4, Ac): ray(c, 5) = Cells(5, Ac): ray(c, 6) = Dn.Offset(, Ac - 1)
          [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]Next[/COLOR] Dn
Sheets("Sheet2").Range("A2").Resize(c, 6) = ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[scottsen]

Sorry Com, this is the Power Pivot forum, which is not just "the ninjas of pivot tables", it is a specific product. The main excel forum was the correct place for your question.

 

[rcommer]

Thanks Mick,

it works perfectly. I wonder if it's possible to tweak it so that it count the number of data column automatically? there are 4 columns in the above example, but I actually have tables with random number of columns.

Sorry Scott - i was thinking there may be a miracle function in power pivot to turn tables into list, same way that pivot can turn list into tables.

Thanks,
Com

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Jul46
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
ReDim ray(1 To Rng.Count * Lst, 1 To Lst)


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Ac = 2 To Lst
        [COLOR="Navy"]If[/COLOR] Dn <> "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ray(c, 1) = Dn: ray(c, 2) = Cells(2, Ac): ray(c, 3) = Cells(3, Ac)
            ray(c, 4) = Cells(4, Ac): ray(c, 5) = Cells(5, Ac): ray(c, 6) = Dn.Offset(, Ac - 1)
          [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn


Sheets("Sheet2").Range("A2").Resize(c, 6) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick