Transposing Dat

Bassai

New Member
Joined
Sep 3, 2012
Messages
9
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 Import20 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>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
The spacing between rows is not clear in your data. Assuming the following pattern
10
2Infinity Auto Import
30
40
520 Puriri Street, New Lynn, Auckland
609 9512882
70
8IV Cars
90
100
1190 Portage Road, Otahuhu, Auckland
1209 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

Well-known Member
Joined
Oct 24, 2015
Messages
5,318
maybe something like this using PowerQuery

with your example

Column1Custom.1Custom.2Custom.3
Infinity Auto ImportInfinity Auto Import20 Puriri Street, New Lynn, Auckland09 9512882
IV Cars90 Portage Road, Otahuhu, Auckland09 9512843
20 Puriri Street, New Lynn, Auckland
09 9512882
IV Cars
90 Portage Road, Otahuhu, Auckland
09 9512843

Code:
[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]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,044
Messages
5,466,210
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top