Rearrange / Transpose data, cases dividend by white row

[Keen_to_learnExcel]

Hi all,

I’m kind of a excel newbie so I tried to get the answer by searching the web but I can’t figure it out.

I have a question regarding transposing my data. Al my data is vertical like this:

Every case is separated by an empty row. I want to pivot the data per case. That it looks like this in an new sheet:

The text in bold is the heading of the row/ column, this isn’t important that this also pivots with the rest of the data. I can put that in manually, but I have more than 900 individual cases much difference in seize, I know that there is a better way than to do this manually, but I can’t figure out how. Can someone help me please? Much thanks in advance.



Kind regards

 

[DanteAmor]

Hi and welcome to MrExcel

Try this
The data on sheet1 begins in cell A1. The results on Sheet2 starting from A2 onwards.

Sub Transpose_data()
  Dim a As Range, i As Long
  i = 2
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    For Each a In .Range("F2", .Range("F" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, 23).Areas
      .Range("A1:F1").Copy
      Sheets("Sheet2").Range("A" & i).PasteSpecial xlPasteValues, , , True
      .Range(a, a.Offset(, -5)).Copy
      Sheets("Sheet2").Range("B" & i).PasteSpecial xlPasteValues, , , True
      i = i + 7
    Next
  End With
  Application.ScreenUpdating = True
End Sub

 

[Keen_to_learnExcel]

Hi,

Thank you very much, works perfectly!

 

[DanteAmor]

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