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

KNr	Driveshaft	CV Joint	Boot Kit	Hub Nuts
10447	VA181L	CV1381N	BK14	HN02
10447	VA181L	CV1159N	BK14	HN02
10447	VA181L	CV1381N	BK650	HN02
10447	VA181L	CV1159N	BK650	HN02
10447	VA181L	CV1381N	CBK7000	HN02
10447	VA181L	CV1159N	CBK7000	HN02
10447	VA181L	CV1381N	SB04	HN02
10447	VA181L	CV1159N	SB04	HN02
Fig2
Required Result
VA181L	Hub Nuts	HN02
VA181L	Boot Kit	BK14
VA181L	Boot Kit	BK650
VA181L	Boot Kit	CBK7000
VA181L	Boot Kit	SB04
VA181L	CV Joint	CV205N
VA181L	CV Joint	CV1159N
VA181L	CV Joint	CV1381N

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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