Transpose rows to columns in large data set

It may be possible, but we need a sample. One sheet should shows the data as it is now and the second sheet -the desired result-output, but all should be in one single book. To attach a sample, go to advance then attachment.

I added a file containing the first ten rows. Is that enough? The whole data sheet is too large to attach.

Fine!
Sheet1 is the raw data and sheet2 is the output. Am I right?

Yes it is : )

Output in sheet 3

you can try this

Sub test()
Dim lastrow As Long
Dim lastcolumn As Long
Dim initialrow As Long
Dim initialcolumn As Long
lastcolumn = 1
lastrow = 1
initialrow = 1
initialcolumn = 1
While Cells(lastrow, lastcolumn).Value <> ""
    While Cells(lastrow, lastcolumn).Value <> ""
        lastcolumn = lastcolumn + 1
    Wend
    lastrow = lastrow + 1
Wend
While initialcolumn <> lastrow
    While initialrow <> lastcolumn
        Cells(initialrow & initialcolumn).Copy (Worksheets("Sheet2").Cells(initialcolumn & initialrow))
        initialrow = initialrow + 1
    Wend
    initialcolumn = initialcolumn + 1
Wend
End Sub

Another option

Sub trans()
Dim x, y(), i&, j&, k&, t&
x = Sheets("Sheet1").Range("A2").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To UBound(x, 2))

For i = 1 To UBound(x, 1)
   k = k + 1
   y(1, k) = x(i, 1)
Next

t = 0
For i = 1 To UBound(x, 1)

k = 1
t = t + 1
    For j = 2 To UBound(x, 2)
        k = k + 1
          
          y(k, t) = x(i, j)
    Next j
Next i

With Sheets("Sheet3")
    .UsedRange.ClearContents
    .Range("A1").Resize(k, UBound(x, 2)).Value = y()
    .Activate
End With
End Sub

Nothing happened when I tried you macro zalora. Any suggestions?

I tried your macro AB33 and I get an error message: Run time error 7: Out of memory.
Is there a solution?

Larne-O,
I have given you two different codes. one of the code was attached with the sample. I do not believe there is any issue of speed with both, not specially with the second one.
In which line of the code do you get the error- highlighted in yellow?

The other macro you send to me is marked yellow at:
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To UBound(x, 2))
It is this one that gives the run time error message.

I'm sorry AB33 I did not see the first one. It makes it in the example file, but when I try it in my file it does not. Then I first get a Compile Error: Expected End Sum. The yellow mark is over the first row in the code: Sub columnstocol()

Larne-o,
Your actual data must be different to the sample attached. This is normal, do not worry, we will find round it.

Larne-o,
sent you a private message, need to check your messages

ok, it was fine, but it only copied to the next sheet which is not broad enough to fit all columns. I guess the transposed data has to be in several sheets?

Olivia,
I know now what the issue is: Size of excel

1,048,576 rows by 16,384 columns

I though I was gone mad, I did not realise I could not transpose more than 16384 rows in to columns. I have tested my code with exact figure of maximum limit of 16,384 and I then added one more row that 16385, my code then fails.
So, I do not know what do you want me to do. One option would be to break the data in to maximum limit and transpose it. I do not know if this what you wanted to do. Try it yourself by putting a maximum row of 16384 in a sheet and run my code.

if you want copy to other sheet just add

While initialrow <> lastrow
        initialcolumn = 1
        While initialcolumn <> lastcolumn
            Worksheets("Sheet1").Cells(initialrow, initialcolumn).Copy (Worksheets("Sheet2").Cells(initialcolumn, initialrow))
            Worksheets("source Sheet").Cells(initialrow, initialcolumn).Copy (Worksheets("destination sheet").Cells(initialcolumn, initialrow)) <----paste this more than one time if you wan to paste to other        
                                                                                                                                                                                              sheet remember change the sheet name
            initialcolumn = initialcolumn + 1
        Wend
      initialrow = initialrow + 1
 Wend

Zalora,
The code is not the issue, but the size of OP's data. The data have over 44k rows.

Long variables can range from -2,147,483,648 to 2,147,483,647.

don't worry about that i use long data type it can contain more 44K number

I'm sorry zalora I'm not good enough to change the code myself, even if I guess it's simple. I thought that the columns wouldn't fit one sheet. That's why I thought they should go into different sheets, is that what you are suggesting?

u means it when reach the certain number will go into another sheet?