Transpose data from one column to several ones

[Veyron]

Good night everyone,

I have a list of data all in one column that I need to separate in several columns.

Raw source:

Column A
Text 1
Text 2
Text 3
...
Tex 8
Text 1
Text 2
Text 3
...
Tex 8
Text 1
Text 2
Text 3
...
Tex 8

up to 7,000 lines.

I would like something like this:

Column Text 1	Column Text 2	Column Text 3	Column Text 4	Column Text 5	Column Text 6	Column Text 7	Column Text 8
Text 1	Text 2	Text 3	Text 4	Text 5	Text 6	Text 7	Text 8
Text 1	Text 2	Text 3	Text 4	Text 5	Text 6	Text 7	Text 8

I know there should be many ways to approach this problem that I am trying some of them and in all of them I am missing something.
I have tried pivot tables and couldn't make it work. Tried Power Pivot but I don't have enough knowledge to use it, tried doing a frankenstein macro with similar questions in this forum but it did not work either.

Can someone please give me a hand?

 

[DanteAmor]

How about:

Sub Transpose_Data()
  Dim a As Variant, i As Long, j As Long, k As Long
  a = Range("A1", Range("A" & Rows.Count).End(3)).Value2
  ReDim b(1 To UBound(a), 1 To 8)
  For i = 1 To UBound(a) Step 8
    j = j + 1
    For k = 1 To 8
      b(j, k) = a(i, 1)
    Next
  Next
  Range("B1").Resize(j, 8).Value = b
End Sub

___________________________________________________________
Or this:

Sub transpose_data1()
  Dim i As Long
  For i = 1 To Range("A" & Rows.Count).End(3).Row Step 8
    Range("B" & Rows.Count).End(3)(2).Resize(1, 8).Value = Range("A" & i).Resize(8).Value
  Next
End Sub

 

[Veyron]

How about:
...

Thanks for your kind answer DanteAmor but I have not been able to make it work properly.
Both of them do the same: They correctly take each "Text 1" from the list and create 8 columns but populate all of them with "Text 1".

Example (only talking about text 1):

List
AAA
...
BBB
...
CCC

This is what I get with your codes:

AAA AAA AAA AAA AAA AAA AAA AAA
BBB BBB BBB BBB BBB BBB BBB BBB
CCC CCC CCC CCC CCC CCC CCC CCC

Let me know if you need me to post image or the example with XL2BB tool.

 

[DanteAmor]

Sorry about that, try this:

Sub transpose_data1()
  Dim i As Long
  For i = 1 To Range("A" & Rows.Count).End(3).Row Step 8
    Range("B" & Rows.Count).End(3)(2).Resize(1, 8).Value = Application.Transpose(Range("A" & i).Resize(8).Value)
  Next
End Sub

 

[Veyron]

...

Thanks it works like a charm!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.