Collating data vertically not horizontally

[rovert]

Afternoon All,

Thank you in advance, i need help in presenting my data vertically and not horizontally. Fig1 is how my data is at the moment, however i need to present it like Fig2.

Fig1
[TABLE="width: 572"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]KNr[/TD]
[TD]Driveshaft[/TD]
[TD]CV Joint[/TD]
[TD]Boot Kit[/TD]
[TD]Hub Nuts[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1381N[/TD]
[TD]BK14[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1159N[/TD]
[TD]BK14[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1381N[/TD]
[TD]BK650[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1159N[/TD]
[TD]BK650[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1381N[/TD]
[TD]CBK7000[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1159N[/TD]
[TD]CBK7000[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1381N[/TD]
[TD]SB04[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD]10447[/TD]
[TD]VA181L[/TD]
[TD]CV1159N[/TD]
[TD]SB04[/TD]
[TD]HN02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fig2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Required Result[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]Hub Nuts[/TD]
[TD]HN02[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]Boot Kit[/TD]
[TD]BK14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]Boot Kit[/TD]
[TD]BK650[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]Boot Kit[/TD]
[TD]CBK7000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]Boot Kit[/TD]
[TD]SB04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]CV Joint[/TD]
[TD]CV205N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]CV Joint[/TD]
[TD]CV1159N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VA181L[/TD]
[TD]CV Joint[/TD]
[TD]CV1381N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

I guess the data starts on sheet 1 in row 2.
Change the name sheet1 for the sheet with data and the name sheet2 for the sheet with the result.

Sub Collating_data()
  Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, llave As String, ky As Variant, dict As Object
  Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
  sh2.Rows("2:" & Rows.Count).ClearContents
  Set dict = CreateObject("scripting.dictionary")
  For i = 2 To sh1.Range("B" & Rows.Count).End(xlUp).Row
    dict.Item(sh1.Cells(i, "B") & "|" & sh1.Cells(1, "E") & "|" & sh1.Cells(i, "E")) = Empty
    dict.Item(sh1.Cells(i, "B") & "|" & sh1.Cells(1, "D") & "|" & sh1.Cells(i, "D")) = Empty
    dict.Item(sh1.Cells(i, "B") & "|" & sh1.Cells(1, "C") & "|" & sh1.Cells(i, "C")) = Empty
  Next
  sh2.Range("A2").Resize(dict.Count) = Application.Transpose(dict.Keys)
  sh2.Range("A:A").TextToColumns sh2.Range("A1"), xlDelimited, xlNone, , , , , , True, "|"
End Sub