Transposing Dat

[Bassai]

Hi

Can someone help please. I have this massive document to process and I wish to move data from one column like this:

[TABLE="width: 262"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Infinity Auto Import[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]20 Puriri Street, New Lynn, Auckland[/TD]
[/TR]
[TR]
[TD]09 9512882[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]IV Cars[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]90 Portage Road, Otahuhu, Auckland[/TD]
[/TR]
[TR]
[TD]09 9512843[/TD]
[/TR]
[TR]
[TD]
To look like this in multiple columns:

Column A Columns B Column C
[TABLE="width: 562"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Infinity Auto Import[/TD]
[TD]20 Puriri Street, New Lynn, Auckland [/TD]
[TD]09 9512882[/TD]
[/TR]
[TR]
[TD]IV Cars



[/TD]
[TD]90 Portage Road, Otahuhu, Auckland [/TD]
[TD]09 9512843

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 262"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]First column has business name
Second Column business address
Third Column Phone number

I tried the =offset command but couldnt get it to work[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ravishankar]

The spacing between rows is not clear in your data. Assuming the following pattern
[TABLE="width: 282"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Infinity Auto Import[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]20 Puriri Street, New Lynn, Auckland[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]09 9512882[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]IV Cars[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]90 Portage Road, Otahuhu, Auckland[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]09 9512843
[/TD]
[/TR]
</tbody>[/TABLE]

=Indirect("A" & row()*6-10)
=indirect("A" & row()*6-7)
=indirect('A"& row()*6-6)
will give the desired output.
change the numbers to match the row number from which it needs to be pulled.
ravishankar

 

[sandy666]

maybe something like this using PowerQuery

with your example

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][td=bgcolor:#70AD47]Custom.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Infinity Auto Import[/td][td][/td][td=bgcolor:#E2EFDA]Infinity Auto Import[/td][td=bgcolor:#E2EFDA]20 Puriri Street, New Lynn, Auckland[/td][td=bgcolor:#E2EFDA]09 9512882[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]IV Cars[/td][td]90 Portage Road, Otahuhu, Auckland[/td][td]09 9512843[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]20 Puriri Street, New Lynn, Auckland[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]09 9512882[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]IV Cars[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]90 Portage Road, Otahuhu, Auckland[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]09 9512843[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    Filter = Table.SelectRows(Source, each ([Column1] <> null)),
    IntegerDiv = Table.TransformColumns(Table.AddIndexColumn(Filter, "Index", 0, 1), {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    Group = Table.Group(IntegerDiv, {"Index"}, {{"Count", each _, type table}}),
    Extract = Table.TransformColumns(Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column1")), {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    RemOthers = Table.SelectColumns(Split,{"Custom.1", "Custom.2", "Custom.3"})
in
    RemOthers[/SIZE]