Transpose Random Data

[domizi]

Hello, I have been trying but nothing works, I have a lot of data (96,345 records) It seems easy but I can't figure it out how to do it.

I can't explain very good, I just need to copy data in the form of a table

A1 B1
A2
A3 B3 C3
A4 B4
Space
A6 B6
A7
A8 B8
Space

Into column, like this
A1 B1 A2 A3 B3 C3 A4 B4
Space (or not, I don't care)
A6 B6 A7 A8 B8

This file will make it clearer, Sheet Actual is how I have it, Wanted is how I need it.
data example.xls

Thanks in advance for any help

Greetings

[mike7952]

Give this a try

Sub abc()
 Dim i As Long, n As Long, LastRow As Long
 Dim a, s
 
    
    LastRow = Cells(Rows.Count, "a").End(xlUp).Row + 1
    a = Range("a1:c" & LastRow)
    For i = 1 To UBound(a)
        If Not IsEmpty(a(i, 1)) Then
            s = s & a(i, 1) & "," & a(i, 2) & "," & a(i, 3)
        Else
            n = n + 1
            s = Split(s, ",")
            Worksheets("Wanted").Range("a" & n).Resize(, UBound(s) + 1) = s
            s = ""
        End If
    Next
End Sub

[domizi]

Thanks mate, it worked perfectly, I'm glad to know that there are people like you helping other like me with less knowledge

[mike7952]

Your welcome...