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.
--
Karthi

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
---------------
Jacob Skaria


"Karthik" wrote:

> 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.
> --
> Karthi

Excel 2007 Tables
Just formulas.
http://www.mediafire.com/file/idmnmz...11_20_09b.xlsx