Formula to concatenate addresses formatted vertically

[Arigato123]

Hi everyone,

I have been given a spreadsheet with just over 20000 lines of data, addresses to be exact. all formatted vertically in different cells e.g.

mini sheet 1.xlsx
	A
1	Customer 1
2	Address 1
3	Address 2
4	Address 3
5	Address 4
6	Address 5
7	Tel
8	Web
9	Customer 2
10	Address 1
11	Address 2
12	Address 3
13	Address 4
14	Address 5
15	Tel
16	Web
17	Customer 3
18	Address 1
19	Address 2
20	Address 3
21	Address 4
22	Address 5
23	Tel
24	Web
Sheet1

As you can see above (I have removed the data and replaced with just dummy data) they are all formatted vertically, is there a way to fix this so that they all run across the rows instead of down the numbered rows?

 

[mohadin]

Hi
Do you mean some thing like this?

Sub test2()
    Dim A, B
    Dim I As Long
    Dim II As Long
    Dim K As Long
    Dim c
    c = 1
    With Sheets("sheet1")
        A = .Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row)
        ReDim B(1 To UBound(A) / 8, 1 To 8)
        For I = 1 To UBound(A) Step 8
            For II = 1 To 8
                B(c, II) = A(K + II, 1)
            Next
            c = c + 1
            K = K + 8
        Next
        .Cells(3, 3).Resize(UBound(B), UBound(B, 2)) = B
    End With
End Sub

 

[Arigato123]

Hi
Do you mean some thing like this?

Sub test2()
    Dim A, B
    Dim I As Long
    Dim II As Long
    Dim K As Long
    Dim c
    c = 1
    With Sheets("sheet1")
        A = .Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row)
        ReDim B(1 To UBound(A) / 8, 1 To 8)
        For I = 1 To UBound(A) Step 8
            For II = 1 To 8
                B(c, II) = A(K + II, 1)
            Next
            c = c + 1
            K = K + 8
        Next
        .Cells(3, 3).Resize(UBound(B), UBound(B, 2)) = B
    End With
End Sub

Yes thanks, it has worked. However there is one problem. Some of the addresses, web and telephone details are 7 lines and some are 8 which makes the data run out of sync. Is there a way to get the VBA to account for that?