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:

Infinity Auto Import

20 Puriri Street, New Lynn, Auckland

09 9512882

IV Cars

90 Portage Road, Otahuhu, Auckland

09 9512843

To look like this in multiple columns: Column A Columns B Column C Infinity Auto Import 20 Puriri Street, New Lynn, Auckland 09 9512882 IV Cars 90 Portage Road, Otahuhu, Auckland 09 9512843 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

First column has business name Second Column business address Third Column Phone number I tried the =offset command but couldnt get it to work

<colgroup><col></colgroup><tbody>
</tbody>

 

[ravishankar]

The spacing between rows is not clear in your data. Assuming the following pattern

1	0
2	Infinity Auto Import
3	0
4	0
5	20 Puriri Street, New Lynn, Auckland
6	09 9512882
7	0
8	IV Cars
9	0
10	0
11	90 Portage Road, Otahuhu, Auckland
12	09 9512843

<colgroup><col span="2"></colgroup><tbody>
</tbody>

=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

Column1		Custom.1	Custom.2	Custom.3
Infinity Auto Import		Infinity Auto Import	20 Puriri Street, New Lynn, Auckland	09 9512882
		IV Cars	90 Portage Road, Otahuhu, Auckland	09 9512843
20 Puriri Street, New Lynn, Auckland
09 9512882
IV Cars
90 Portage Road, Otahuhu, Auckland
09 9512843

[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]