Consolidate data from multiple rows



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.

Jacob Skaria

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
End If
End Sub

If this post helps click Yes

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question