Consolidate data from multiple rows

  • Thread starter Thread starter Karthik
  • Start date Start date
K

Karthik

Hi All,

I have a data which has same item spread over 5 or 6 rows and looks like this

Item # T1 T2 T3 T4 T5
1005 Emp 1
1005 Emp2
1005 Emp3
1005 Emp1
108 Epm3
1005 Epm2

I'm trying to consolidate each Item # into a single Row but couldn't get a
formula to do this. I want this data to look like

Item # T1 T2 T3 T4 T5
1005 Emp 1 Emp2 Emp3 Emp1 Emp2
108 Epm3

Please help me to consolidate this table.


Thanks for your help in advance.
 
You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub MergeRows()
Dim lngRow As Long, lngStartRow As Long, lngFindRow As Long
Dim lngCols As Long
lngStartRow = 1
lngCols = 6

For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To (lngStartRow + 1) Step -1
If WorksheetFunction.CountIf(Range("A" & lngStartRow & ":A" & lngRow - 1), _
Range("A" & lngRow)) > 0 Then
lngFindRow = WorksheetFunction.Match(Range("A" & lngRow), _
Range("A" & lngStartRow & ":A" & lngRow - 1), 0)
For lngCol = 2 To lngCols
If Cells(lngRow, lngCol) <> "" Then
Cells(lngFindRow, lngCol) = Cells(lngRow, lngCol)
End If
Next
Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
 
Back
Top