Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging rows based on column value
I have an interesting problem. I have the data in the following format: a1|b1|c1|d1|__|__| a1|b1|__|__|e1|f1| a2|b2|c2|d2|__|__| a2|b2|__|__|e2|f2| and I'd like to be able to merge the rows based on the values in the first two columns. So the above table would become: a1|b1|c1|d1|e1|f1| a2|b2|c2|d2|e2|f2| I have a large file in this format, where I must merge 2,3 or sometimes 4 rows in this fashion. Anyone ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging rows based on column value
So the row with the higher number overlays the previous row--but only if that
value isn't blank, right? If yes: Option Explicit Sub testme() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _ And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then 'both match, do some merging For iCol = 3 To _ .Cells(iRow, .Columns.Count).End(xlToLeft).Column If .Cells(iRow, iCol).Value = "" Then 'skip it Else 'move the value up a row .Cells(iRow - 1, iCol).Value _ = .Cells(iRow, iCol).Value End If Next iCol 'delete that row .Rows(iRow).Delete End If Next iRow End With End Sub wrote: I have an interesting problem. I have the data in the following format: a1|b1|c1|d1|__|__| a1|b1|__|__|e1|f1| a2|b2|c2|d2|__|__| a2|b2|__|__|e2|f2| and I'd like to be able to merge the rows based on the values in the first two columns. So the above table would become: a1|b1|c1|d1|e1|f1| a2|b2|c2|d2|e2|f2| I have a large file in this format, where I must merge 2,3 or sometimes 4 rows in this fashion. Anyone ? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging rows based on column value
On Nov 14, 4:25 pm, Dave Peterson wrote:
So the row with the higher number overlays the previous row--but only if that value isn't blank, right? If yes: Option Explicit Sub testme() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _ And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then 'both match, do some merging For iCol = 3 To _ .Cells(iRow, .Columns.Count).End(xlToLeft).Column If .Cells(iRow, iCol).Value = "" Then 'skip it Else 'move the value up a row .Cells(iRow - 1, iCol).Value _ = .Cells(iRow, iCol).Value End If Next iCol 'delete that row .Rows(iRow).Delete End If Next iRow End With End Sub wrote: I have an interesting problem. I have the data in the following format: a1|b1|c1|d1|__|__| a1|b1|__|__|e1|f1| a2|b2|c2|d2|__|__| a2|b2|__|__|e2|f2| and I'd like to be able to merge the rows based on the values in the first two columns. So the above table would become: a1|b1|c1|d1|e1|f1| a2|b2|c2|d2|e2|f2| I have a large file in this format, where I must merge 2,3 or sometimes 4 rows in this fashion. Anyone ? -- Dave Peterson Worked like a charm. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Countings rows based on column criteria | Excel Discussion (Misc queries) | |||
how do i omit rows for printing based on column value | Excel Discussion (Misc queries) | |||
Summing rows based on column headings | Excel Worksheet Functions | |||
Need to select rows to average based on a value in a different column | New Users to Excel |