Merging two rows together with the purpose of having one updated record

[ikey99]

Hi, so I have thousands of records that are each in a set of two rows. The data in these two rows need to be combined into one row so that the data is updated can be imported into our CRM system. So for a while, our company was using two CRM systems for inputting customer data, therefore each record has two rows. some columns are missing in each row and they're not consistent. How do I go about combining these two rows into one complete row with updated correct data?

For the most part, it will be as easy as one row will have a blank column where the other doesn't and they can be easily combined, but not sure how to go about rows that both have data but they are different. Obviously, I can find which one is the updated data but idk how to scale that for 5000+

 

[Peter_SSs]

Welcome to the MrExcel board!

but not sure how to go about rows that both have data but they are different.

You would really need to tell us what to do in that situation as we cannot make an informed decision.

 

[ikey99]

Welcome to the MrExcel board!

You would really need to tell us what to do in that situation as we cannot make an informed decision.

Yeah, I guess the only thing that can be done is to add the two entries together with a spacer and then go through them one by one within the two CRM systems to find out which entry has the later creation date

 

[Peter_SSs]

I guess the only thing that can be done is to add the two entries together with a spacer

OK, give this a try with a copy of your workbook (since the code over-writes the original data )

Sub CombineRows()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  With Range("A1").CurrentRegion.Offset(1)
    a = .Value
    uba2 = UBound(a, 2)
    ReDim b(1 To UBound(a), 1 To uba2)
    For i = 1 To UBound(a) - 2 Step 2
      k = k + 1
      For j = 1 To uba2
        Select Case True
          Case a(i, j) = a(i + 1, j)
            b(k, j) = a(i, j)
          Case IsEmpty(a(i, j)) Or IsEmpty(a(i + 1, j))
            b(k, j) = a(i, j) & a(i + 1, j)
          Case Else
            b(k, j) = a(i, j) & "|" & a(i + 1, j)
        End Select
      Next j
    Next i
    .Value = b
    .Replace What:="*|*", Replacement:="#N/A"
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).Interior.Color = vbCyan
    On Error GoTo 0
    .Value = b
  End With
End Sub

Here is my small sample data

The code assumes always 2 rows for each record as you stated with not "|" characters in the data.
For any columns that have two non-blank values that do not match for any record, both values are included in the results, separated by the "|" character and highlighted to make checking easier.

This is the result for my sample data

 

[ikey99]

OK, give this a try with a copy of your workbook (since the code over-writes the original data )

Sub CombineRows()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  With Range("A1").CurrentRegion.Offset(1)
    a = .Value
    uba2 = UBound(a, 2)
    ReDim b(1 To UBound(a), 1 To uba2)
    For i = 1 To UBound(a) - 2 Step 2
      k = k + 1
      For j = 1 To uba2
        Select Case True
          Case a(i, j) = a(i + 1, j)
            b(k, j) = a(i, j)
          Case IsEmpty(a(i, j)) Or IsEmpty(a(i + 1, j))
            b(k, j) = a(i, j) & a(i + 1, j)
          Case Else
            b(k, j) = a(i, j) & "|" & a(i + 1, j)
        End Select
      Next j
    Next i
    .Value = b
    .Replace What:="*|*", Replacement:="#N/A"
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).Interior.Color = vbCyan
    On Error GoTo 0
    .Value = b
  End With
End Sub

Here is my small sample data

View attachment 49308

The code assumes always 2 rows for each record as you stated with not "|" characters in the data.
For any columns that have two non-blank values that do not match for any record, both values are included in the results, separated by the "|" character and highlighted to make checking easier.

This is the result for my sample data

View attachment 49309

Thanks for the help, however, each time I try to run the code I get the error: 'Run-time error ‘13’: Type mismatch’

 

[Peter_SSs]

Thanks for the help, however, each time I try to run the code I get the error: 'Run-time error ‘13’: Type mismatch’

Since it runs without error on the dummy sample data that I provided, it seems logical that there is something significantly different between my data and yours.
Given that we cannot copy anything from an image like you posted, perhaps you could give us a small set of dummy sample data (no sensitive information included) with XL2BB?

It may also be helpful to know which line of the code produced that error.

 

[ikey99]

Since it runs without error on the dummy sample data that I provided, it seems logical that there is something significantly different between my data and yours.
Given that we cannot copy anything from an image like you posted, perhaps you could give us a small set of dummy sample data (no sensitive information included) with XL2BB?

It may also be helpful to know which line of the code produced that error.

I don't get any indication of what line of the code produced the error.

but sample data here:

Sample data removed by moderator

 

[ikey99]

I had taken the excerpt from the export and pasted it as just values if that is of any significance

 

[Peter_SSs]

One issue is that you told us ...

thousands of records that are each in a set of two rows.

.. and my post stated ..

The code assumes always 2 rows for each record as you stated

.. but in fact your data is not always sets of two rows. In that data there are 5 rows that are not in pairs (rows 66, 67, 72, 73 & 82)

I will have another think about it given this change of circumstance.

 

[ikey99]

The issue is that you told us ...


.. and my post stated ..

.. but in fact your data is not always sets of two rows. In that data there are 5 rows that are not in pairs (rows 66, 67, 72, 73 & 82)

I will have another think about it given this change of circumstance.

I see, when I was given the data I was told that was the case. I really should've done my due diligence and confirmed that was the case. Sorry, my bad!