Unique Rows - Transpose Data to Columns

[shameem2k]

Hello All Experts,
Please can you help getting through my data organized, I have the below data:

Item ID Question Answer
2781923 Host ABCME234561
2781923 Login_ID AA12345
2781923 Justification Need USB
2345432 Host CGH89765321
2345432 Login_ID BB23467
2345432 Justification Need drive


And I would need to maintain unique row by transposing the data in Answer column.
The data is uniform and only the data in Answer column changes. Just to be clear, I want the output as:
Item ID Host Login_ID Justification
2781923 ABCME234561 AA12345 Need USB
2345432 CGH89765321 BB23467 Need drive

Request if someone can help with some macro or some functions with which I can achieve the desired output.
Thanks in advance,
Shameem

 

[VoG]

Try this with a copy of your sheet

Sub xpose()
Dim Area As Range
Dim LR As Long, i As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then
        Rows(i).Insert
    End If
Next i
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 3).Resize(, 3).Value = Application.Transpose(Area.Offset(, 2).Resize(3))
Next Area
Columns("B:C").Delete
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B1:D1").Value = Array("Host", "Login_ID", "Justification")
End Sub

 

[shameem2k]

Peter,

You are a STAR, it worked perfect.

Many thanks mate for your help, really appreciate it

Regards,

Shameem