Transpose range of cells based on the whether a cell repeats

[vimal606]

I have an input sheet with a lot of names in Column A which repeats (Maximum 3 times) and rest of the cells (from column C) are different.
I need to generate a sheet with unique names and rest of the cells transposed.

INPUT SHEET

Employee ID Date Of Joining Effective Date Action ReasonA 05 November 2014 05 November 2014 Hire First Hire
A 04 May 2015 04 May 2015 Pay correction First Hire
B 04 May 2015 04 May 2015 Hire First Hire
B 04 February 2016 04 February 2016 Hire First Hire
B 02 March 2016 02 March 2016 Hire regular conversion


REQUIRED OUTPUT
Employee ID Date Of Joining Effective Date Action Reason Effective Date Action Reason
A 05 November 2014 05 November 2014 Hire First Hire 04 May 2015 Pay correction First Hire
B 02 March 2016 04 May 2015 Hire First Hire 04 February 2016 Hire First Hire

I tried looking into other conversations but it only talks about transposing a single column and not a range.
Can somebody help?

 

[JLGWhiz]

This assumes that the data will be sorted on employee ID.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name - Input sheet
Set sh2 = Sheets("Sheet2") 'Edit sheet name - Output sheet
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c.Value <> sh2.Cells(Rows.Count, 1).End(xlUp).Value Then
            c.Resize(1, 5).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        ElseIf c.Value = sh2.Cells(Rows.Count, 1).End(xlUp).Value Then
            c.Offset(, 2).Resize(1, 3).Copy sh2.Cells(Cells(Rows.Count, 1).End(xlUp).Row, Columns.Count).End(xlToLeft).Offset(, 1)
        End If
    Next
End Sub

 

[vimal606]

thanks for the reply.

When i apply the code, it retains the first row of every unique id and rest is deleted.
What i need is to have one row for every unique id with 3 cells from the duplicate to be pasted in the adjacent columns.

Employee ID I Date of joining I Effective date I Action I Reason I Effective date I Action I Reason

 

[JLGWhiz]

thanks for the reply.

When i apply the code, it retains the first row of every unique id and rest is deleted.
What i need is to have one row for every unique id with 3 cells from the duplicate to be pasted in the adjacent columns.

Employee ID I Date of joining I Effective date I Action I Reason I Effective date I Action I Reason

The code in post #2 does not delete anything. It only copies from sheet 1 to sheet 2 and in my test set up it copies as expected. It does not put headers on sheet 2. I assumed you would already have that set up. I have no idea how you are getting rows deleted.