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/idmnmzigm12/11_20_09b.xlsx