Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. -- Karthi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate data from multiple rows
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate data from multiple rows
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match consolidate multiple rows data into one row | Excel Worksheet Functions | |||
Consolidate data from worksheet (more then 65536 rows) | Excel Discussion (Misc queries) | |||
Consolidate data from multiple worksheets into one | Excel Discussion (Misc queries) | |||
How to open/consolidate details for multiple rows in a PivotTable | Excel Discussion (Misc queries) | |||
How to consolidate data from multiple worksheets. | Excel Worksheet Functions |