From column to rows

[Atanas Kisyov]

Hi guys, I have a problem and I hope you can help me.
I have a table like the example bellow:

And I need it to transform like this:

I can use the transpose paste special, but there are around 10 000 rows...
Also some of "Material 1" have different qty of rows for "Material 2"

Example:

 

[davesexcel]

Hello,
Can you explain where the next group is expected?

 

[Atanas Kisyov]

The data is different for every "Material 1", some have one row and others have twenty rows...

 

[davesexcel]

Yes, do you want the next group below the first group or to continue down the same row? You are just showing the outcome of 1 group

 

[JLGWhiz]

See if this does it.

Sub t()
Dim i As Long
With ActiveSheet
    For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If .Cells(i, 1) = "" Then
            Range(.Cells(i, 2), .Cells(i, Columns.Count).End(xlToLeft)).Copy _
            Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
            Rows(i).Delete
        End If
    Next
End With
End Sub

 

[Atanas Kisyov]

See if this does it.

Sub t()
Dim i As Long
With ActiveSheet
    For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If .Cells(i, 1) = "" Then
            Range(.Cells(i, 2), .Cells(i, Columns.Count).End(xlToLeft)).Copy _
            Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
            Rows(i).Delete
        End If
    Next
End With
End Sub

This works perfectly! Thank you!

 

[JLGWhiz]

This works perfectly! Thank you!

You're welcome,
regards, JLG