Fetch rows provided there is a difference in one of the cells

[sofas]

Hello. I have on file sheets 1 and 2 sheet 1 column B includes customer names that are sometimes repeated.and from column c to t customer data. I want when writing his name in Sheet2 cell m4 All rows related to the same name are brought to Sheet2 starting from cell b6 provided there is a difference in one of the cells and in case of the same values are ignored

 

[DanteAmor]

Very nice it only lacks fetching column c

Use this:

Sub comparecells_v2()
  Dim i As Long, j As Long, k As Long, m As Long
  Dim a As Variant, b As Variant
  a = Sheets("Sheet1").Range("C6:T" & Sheets("Sheet1").Range("D" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1) - 1 Step 2
    For j = 3 To UBound(a, 2)
      If a(i, j) <> a(i + 1, j) Then
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          b(k + 1, m) = a(i + 1, m)
        Next
        k = k + 1
        Exit For
      End If
    Next
  Next
  Application.ScreenUpdating = False
  Sheets("Sheet2").Range("C6").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Application.ScreenUpdating = True
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

"A bird in the hand is better than hundreds in the air"
"mas vale pájaro en mano que cientos volando"
-dicho popular-​

 

[sofas]

 

[sofas]

Are there any suggestions?

 

[Alex Blakenburg]

To do this we would either need to be sure that when you have a customer multiple times that it appears "the same number of times and in the same order" on both sheets or have a second reference that we can use to match the specific customer line on each sheet, so we have a unique match.

 

[sofas]

To do this we would either need to be sure that when you have a customer multiple times that it appears "the same number of times and in the same order" on both sheets or have a second reference that we can use to match the specific customer line on each sheet, so we have a unique match.

Maybe I couldn't communicate the idea well. What I want is to copy the data from one sheet to another provided there is a difference between the values of the first and second rows in one of the cells and ignore the clients with the same values in both rows
Because each customer has a row with their real data and a description of the modification.
The goal is to get the customer whose data has been modified

 

[sofas]

To do this we would either need to be sure that when you have a customer multiple times that it appears "the same number of times and in the same order" on both sheets or have a second reference that we can use to match the specific customer line on each sheet, so we have a unique match.

Maybe I couldn't communicate the idea well. What I want is to copy the data from one sheet to another provided there is a difference between the values of the first and second rows in one of the cells and ignore the clients with the same values in both rows
Because each customer has a row with their real data and a description of the modification.
The goal is to get the customer whose data has been modified

 

[DanteAmor]

Considering your last example, the data starts in cell D6.

Try the following macro:

Sub comparecells()
  Dim i As Long, j As Long, k As Long
  Application.ScreenUpdating = False
  k = 6
  With Sheets("Sheet1")
    For i = 6 To .Range("D" & Rows.Count).End(3).Row Step 2
      For j = 5 To .Cells(i, Columns.Count).End(1).Column
        If .Cells(i, j).Value <> .Cells(i + 1, j) Then
          .Rows(i & ":" & i + 1).Copy Sheets("Sheet2").Range("A" & k)
          k = k + 2
          Exit For
        End If
      Next
    Next
  End With
  Application.ScreenUpdating = True
End Sub

------------------------
This is probably faster:

Sub comparecells_v2()
  Dim i As Long, j As Long, k As Long, m As Long
  Dim a As Variant, b As Variant
  a = Sheets("Sheet1").Range("D6:T" & Sheets("Sheet1").Range("D" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1) - 1 Step 2
    For j = 1 To UBound(a, 2)
      If a(i, j) <> a(i + 1, j) Then
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          b(k + 1, m) = a(i + 1, m)
        Next
        k = k + 1
        Exit For
      End If
    Next
  Next
  Application.ScreenUpdating = False
  Sheets("Sheet2").Range("D6").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Application.ScreenUpdating = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[sofas]

Considering your last example, the data starts in cell D6.

Try the following macro:

Sub comparecells()
  Dim i As Long, j As Long, k As Long
  Application.ScreenUpdating = False
  k = 6
  With Sheets("Sheet1")
    For i = 6 To .Range("D" & Rows.Count).End(3).Row Step 2
      For j = 5 To .Cells(i, Columns.Count).End(1).Column
        If .Cells(i, j).Value <> .Cells(i + 1, j) Then
          .Rows(i & ":" & i + 1).Copy Sheets("Sheet2").Range("A" & k)
          k = k + 2
          Exit For
        End If
      Next
    Next
  End With
  Application.ScreenUpdating = True
End Sub

------------------------
This is probably faster:

Sub comparecells_v2()
  Dim i As Long, j As Long, k As Long, m As Long
  Dim a As Variant, b As Variant
  a = Sheets("Sheet1").Range("D6:T" & Sheets("Sheet1").Range("D" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1) - 1 Step 2
    For j = 1 To UBound(a, 2)
      If a(i, j) <> a(i + 1, j) Then
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          b(k + 1, m) = a(i + 1, m)
        Next
        k = k + 1
        Exit For
      End If
    Next
  Next
  Application.ScreenUpdating = False
  Sheets("Sheet2").Range("D6").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Application.ScreenUpdating = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Very nice it only lacks fetching column c

 

[sofas]

Use this:

Sub comparecells_v2()
  Dim i As Long, j As Long, k As Long, m As Long
  Dim a As Variant, b As Variant
  a = Sheets("Sheet1").Range("C6:T" & Sheets("Sheet1").Range("D" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1) - 1 Step 2
    For j = 3 To UBound(a, 2)
      If a(i, j) <> a(i + 1, j) Then
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
          b(k + 1, m) = a(i + 1, m)
        Next
        k = k + 1
        Exit For
      End If
    Next
  Next
  Application.ScreenUpdating = False
  Sheets("Sheet2").Range("C6").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Application.ScreenUpdating = True
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

"A bird in the hand is better than hundreds in the air"
"mas vale pájaro en mano que cientos volando"
-dicho popular-​

Thank you, thank you, thank you....... I really appreciate your cooperation. You're a wonderful person.