Convert a list into rows

[Colmans]

Hi

I've downloaded some data which comprises of business name, address and post/zip code. The data has arrived in a single column but I now need to sort so that I can effectively swivel the company data 90 degrees so each business lists across a row.

Not all of the data is consistent as some have more address lines than others.

I've tried to run simple macro by recording the actions, but it links this to the original cells, and not from my current starting point.

Any advice or ideas to help me speed this up would be appreciated, and thanks in advance for your help.

 

[alansidman]

Suggest you post about a dozen lines of sample data that is representative of your actual data. Without seeing any data, it is difficult to offer any type of solution for this type of issue.

 

[Colmans]

[TABLE="width: 136"]
<tbody>[TR]
[TD]Company A[/TD]
[/TR]
[TR]
[TD]The High Street[/TD]
[/TR]
[TR]
[TD]Big Town[/TD]
[/TR]
[TR]
[TD]AA1 3AA[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[/TR]
[TR]
[TD]The Mews[/TD]
[/TR]
[TR]
[TD]Main Road[/TD]
[/TR]
[TR]
[TD]Little Town[/TD]
[/TR]
[TR]
[TD]AA5 5AA[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[/TR]
[TR]
[TD]3 Market Square[/TD]
[/TR]
[TR]
[TD]Big Town[/TD]
[/TR]
[TR]
[TD]AA1 3ZZ


All of the above is in column A[/TD]
[/TR]
</tbody>[/TABLE]

And convert to

[TABLE="width: 510"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Company A[/TD]
[TD]The High Street[/TD]
[TD]Big Town[/TD]
[TD]AA1 3AA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]The Mews[/TD]
[TD]Main Road[/TD]
[TD]Little Town[/TD]
[TD]AA5 5AA[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]3 Market Square[/TD]
[TD]Big Town[/TD]
[TD]AA1 3ZZ[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

EDIT - just spotted that you have posted some data


Name and address lists are notoriously inconsistent and error prone so I endeavour to FIX data layout to make it more consistent BEFORE trying to transpose anything most of which can generally be achieved with VBA

To help we need to know a bit more about your data and in particular any patterns that can be identified

- Is the first cell usually the name?
- Is the last cell usually the zip code?
- Is the cell above the zip code usually the city?
- Is there a blank cell
- Is there a marker to identify the beginning (or end) of each block (please provide details)
- are there any blank cells within "address blocks"
- are all addresses US addresses?
- any obvious oddballs?
- how many rows are there?

Ideally each "address" is a contiguous block (no empty cells) ...followed by an empty cell
see worksheet below where name is consistently the FIRST cell and zip code consistently the LAST cell of block
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

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

1​

[/td][td][/td][/tr]

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

2​

[/td][td]Name1[/td][/tr]

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

3​

[/td][td]addr1 1[/td][/tr]

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

4​

[/td][td]addr1 2[/td][/tr]

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

5​

[/td][td]City 1[/td][/tr]

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

6​

[/td][td]zip1[/td][/tr]

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

7​

[/td][td][/td][/tr]

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

8​

[/td][td]Name2[/td][/tr]

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

9​

[/td][td]addr2 1[/td][/tr]

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

10​

[/td][td]addr2 2[/td][/tr]

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

11​

[/td][td]City 2[/td][/tr]

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

12​

[/td][td]zip2[/td][/tr]

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

13​

[/td][td][/td][/tr]

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

14​

[/td][td]Name3[/td][/tr]

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

15​

[/td][td]addr3 1[/td][/tr]

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

16​

[/td][td]addr3 2[/td][/tr]

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

17​

[/td][td]addr3 3[/td][/tr]

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

18​

[/td][td]City 3[/td][/tr]

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

19​

[/td][td]zip3[/td][/tr]

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

20​

[/td][td][/td][/tr]

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

21​

[/td][td]name4[/td][/tr]

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

22​

[/td][td]addr4 1[/td][/tr]

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

23​

[/td][td]City 4[/td][/tr]

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

24​

[/td][td]zip1[/td][/tr]

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

25​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: List[/td][/tr][/table]

 

[Yongle]

That data in post#3 does not look like a typical UK adddress list (no counties??? etc)
- simplifying to that extent may be unhelpful

We need to understand what the actual data looks like (warts and all)
- see questions on previous post

Please confirm that you simply want the data form each row to go into the next column and not have all postcodes in one column etc

thanks

 

[sandy666]

with PowerQuery (Get&Transform)

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Company A[/td][td][/td][td=bgcolor:#E2EFDA]Company A[/td][td=bgcolor:#E2EFDA]The High Street[/td][td=bgcolor:#E2EFDA]Big Town[/td][td=bgcolor:#E2EFDA]AA1 3AA[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]The High Street[/td][td][/td][td]Company B[/td][td]The Mews[/td][td]Main Road[/td][td]Little Town[/td][td]AA5 5AA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Big Town[/td][td][/td][td=bgcolor:#E2EFDA]Company C[/td][td=bgcolor:#E2EFDA]3 Market Square[/td][td=bgcolor:#E2EFDA]Big Town[/td][td=bgcolor:#E2EFDA]AA1 3ZZ[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]AA1 3AA[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

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

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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AA5 5AA[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]3 Market Square[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]AA1 3ZZ[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Condition = Table.AddColumn(Source, "Company", each if Text.Contains([raw], "Company") then [raw] else null),
    FillD = Table.FillDown(Condition,{"Company"}),
    Filter = Table.SelectRows(FillD, each not Text.Contains([raw], "Company")),
    Group = Table.Group(Filter, {"Company"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Address", each Table.Column([Count],"raw")),
    Extract = Table.TransformColumns(List, {"Address", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount = Table.AddColumn(Extract, "Split Count", each List.Count(Text.Split([Address],","))),
    MaxCount = List.Max(SplitCount[Split Count]),
    Split = Table.SplitColumn(Extract, "Address", Splitter.SplitTextByDelimiter(","), MaxCount)
in 
    Split[/SIZE]

 

[Colmans]

That data in post#3 does not look like a typical UK adddress list (no counties??? etc)
- simplifying to that extent may be unhelpful

We need to understand what the actual data looks like (warts and all)
- see questions on previous post

Please confirm that you simply want the data form each row to go into the next column and not have all postcodes in one column etc

thanks

Yongle

The data I listed is how the data is. It is very basic and it is for UK addresses and is taken from a public data. I'm not concerned about all of the post codes being in the same column as I can easily adress that after.

 

[Yongle]

If you are happy with a VBA solution ....

The function below will ultimately be called from another VBA procedure if it works as expected when you test it


First of all give this a try in a copy of your workbook
- it is a starting point to help identify the END of each address block
- the formula in column B should return nothing except where there is a valid postcode in the adjacent cell in column A

All you need do is ...
1. insert the code (see below)
2. then use this formula in B1 copied down
=IFERROR(ValidatePostCode(A1),"")

Place code in a STANDARD VBA module (see notes below)

Function ValidatePostCode(ByVal PostCode As String)
'datanology UK Postcode Validator
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*")
End If
If ValidatePostCode Then
ValidatePostCode = "Valid"
Else
ValidatePostCode = ""
End If
End Function

Notes
visit VBA window with {ALT}{F11}
Insert a new Module with {ALT} I M
Paste code into that window (ensure EVERYTHING up to and including End Function is copied)
return to Excel with {ALT}{F11}

 

[Yongle]

The VBA below (which uses the function provided in post#8) creates a new sheet containing the transposed data as requested
- your sample data contained a UK postcode as the last line of each address block
- that pattern determines each range copied and transposed
- rows containing blank cells in column A are deleted early in the code

Test on a COPY of your workbook

The "dummy" postcodes in your sample data were replaced with valid UK postcodes and the sheet named "List"
Sample data used
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

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

1​

[/td][td][/td][/tr]

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

2​

[/td][td]Company A[/td][/tr]

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

3​

[/td][td]The High Street[/td][/tr]

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

4​

[/td][td]Big Town[/td][/tr]

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

5​

[/td][td]LL30 3AA[/td][/tr]

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

6​

[/td][td]Company B[/td][/tr]

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

7​

[/td][td]The Mews[/td][/tr]

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

8​

[/td][td]Main Road[/td][/tr]

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

9​

[/td][td]Little Town[/td][/tr]

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

10​

[/td][td]CA27 5AA[/td][/tr]

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

11​

[/td][td]Company C[/td][/tr]

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

12​

[/td][td]3 Market Square[/td][/tr]

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

13​

[/td][td]Big Town[/td][/tr]

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

14​

[/td][td]E1 7DP[/td][/tr]

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

15​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: List[/td][/tr][/table]

Running TransposeAdresses returned this

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

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

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

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

2​

[/td][td]Company A[/td][td]The High Street[/td][td]Big Town[/td][td]LL30 3AA[/td][td][/td][/tr]

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

3​

[/td][td]Company B[/td][td]The Mews[/td][td]Main Road[/td][td]Little Town[/td][td]CA27 5AA[/td][/tr]

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

4​

[/td][td]Company C[/td][td]3 Market Square[/td][td]Big Town[/td][td]E1 7DP[/td][td][/td][/tr]

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

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]

Place code below in standard module
Function ValidatePostCode required
- amend the sheet name (List) and first cell (A2) as appropriate

Sub TransposeAdresses()
    Dim aName As Range, Last As Range, Block As Range, Cel As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("[I][COLOR=#ff0000]List[/COLOR][/I]")
    ws1.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Set ws2 = Sheets.Add: ws2.Range("A:G").ColumnWidth = 30
    Set Last = ws1.Range("A" & Rows.Count).End(xlUp)            'last cell with value in column A
    Set aName = ws1.Range("[I][COLOR=#ff0000]A2[/COLOR][/I]")                                 'first cell

    Do
'find next cell containing postcode
        Set Cel = aName                                         'begin with first cell
        Do Until ValidatePostCode(Cel) = "Valid"                'does it contain a postcode?
            Set Cel = Cel.Offset(1)                             'try next cell
            If Cel.Address = Last.Address Then Exit Do          'stop at last cell with value
        Loop
'set address block, copy and paste transposed
        Set Block = Range(aName, Cel)
        Block.Copy
        With ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
            .PasteSpecial xlPasteAll, Transpose:=True
        End With
'move to next name
        Set aName = Block.Offset(Block.Rows.Count).Resize(1)
        If aName = "" Then Exit Do                              'stop if cell is blank
    Loop
End Sub