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 ?

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

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