Compare multiple columns in VBA

Hello Pazzaki

Why in VBA when a simple conditional formatting will do the job:
Select the 3 columns ABC - Home - Conditional Formatting - New Rule - Use a formula .. and enter the following formula:
=AND($A1<>"";COUNTIFS($F$1:$F$5000;$A1;$G$1:$G$5000;$B1;$H$1:$H$5000;$C1)=0) being careful with the $ at the right places
before choosing the fill color
Repeat the operation for columns FGH with the formula
=AND($G1<>"";COUNTIFS($A$1:$A$5000;$G1;$B$1:$B$5000;$H1;$C$1:$C$5000;$H1)=0)

Modify the references if your table goes below or beyond row 5000

Best regards
Via

Re

I was absent this afternoon

I would like to reuse the code in other Excel files
Ok provided that the information is placed in the same columns, otherwise you will still have to adapt the macro code

Macro using the same principle as the MFC, by a COUNTIFS (Countifs in VBA) we count the rows with the same items, if 0, color in red and display in the last column "Different" or "OK"

Sub Result() Dim LastRow As Long, Row As Long 'variables 'choose the sheet here With Worksheets("Feuil1") LastRow = .Range("B" & Rows.Count).End(xlUp).Row 'determine the last non-empty row number in column B in the LastRow variable For Row = 1 To LastRow 'from the first row to the last 'check that block in ABCD exists in FGHI If Application.WorksheetFunction.CountIfs(.Range("F1:F" & LastRow), .Range("A" & Row), .Range("G1:G" & LastRow), .Range("B" & Row), .Range("H1:H" & LastRow), .Range("C" & Row), .Range("I1:I" & LastRow), .Range("D" & Row)) = 0 Then .Range("E" & Row) = "Different" .Range("A" & Row & ":D" & Row).Select Selection.Interior.Color = 255 Else .Range("E" & Row) = "OK" .Range("A" & Row & ":D" & Row).Interior.Pattern = xlNone End If 'check that block in FGHI exists in ABCD If Application.WorksheetFunction.CountIfs(.Range("A1:A" & LastRow), .Range("F" & Row), .Range("B1:B" & LastRow), .Range("G" & Row), .Range("C1:C" & LastRow), .Range("H" & Row), .Range("D1:D" & LastRow), .Range("I" & Row)) = 0 Then .Range("J" & Row) = "Different" .Range("F" & Row & ":I" & Row).Select Selection.Interior.Color = 255 Else .Range("J" & Row) = "OK" .Range("F" & Row & ":I" & Row).Interior.Pattern = xlNone End If Next End With End Sub

The macro works correctly if both groups of 4 columns have the same number of rows
Otherwise, you would need to add between the two parts of the code that do the checks:

 Next LastRow = .Range("F" & Rows.Count).End(xlUp).Row 'determine the last non-empty row number in column F in the LastRow variable For Row = 1 To LastRow 'from the first row to the last

Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein

Hello,

Indeed, I had incorrectly defined the last column to write "Different" in the next one.
To change the number of columns, there is only one variable to modify.
I need to be away, I will send you the modifications and explanations early this afternoon.

Hello,

could you put a copy of your workbook, which would make my work easier (working on real data)
for that

Put the workbook without confidential data as an attachment on 
 https://mon-partage.fr
In the download link
 right-click- copy the link address and paste it into your ongoing message on ccm 

There is a possibility without too much problem to compare your 2 blocks of different heights
especially if you have a large number of lines (how many?)

attached is an example of comparing 2 lists of 1 column over 10000 lines in less than a second and which would of course need to be modified to adapt to your issue
https://mon-partage.fr/f/SPyJRJWN/

in waiting

Here is the modified code for comparing 2 sets of 4 columns, with the first series separated from the second by an empty column.

Option Explicit
Dim i%, n%, dl%, x%, dc%
Sub Compare()
' i = row number
'n = number of 1st series of columns
'dl = last row
'dc = last column
'x = n + 4 (columns to compare)

dl = Range("A65536").End(xlUp).Row
dc = Cells(1, Columns.Count).End(xlToLeft).Column
 For n = 1 To 4
 For i = 1 To dl
 x = n + 5
 If Cells(i, n) <> Cells(i, x) Then
 Cells(i, n).Font.ColorIndex = 3
 Cells(i, x).Font.ColorIndex = 3
 Cells(i, dc + 1) = "Different"
 End If
 Next i
 Next n
End Sub

A priori, si j'ai bien compris la réponse, le code fonctionne pour une série des premières colonnes comparées à un même nombre d'une deuxième série de colonnes.

Il s'agirait maintenant de comparer les données de deux colonnes, a priori rien de plus facile, mais vous indiquez :

Il s'agirait de comparer toute une ligne de la liste 1 avec les lignes de la liste 2 (dans le désordre du coup)

et là, il y a un besoin de précision.

Il n'est pas nécessaire de joindre un fichier, mais au moins un exemple précis et explicité.

I have reread your response: for the case where you would have compacted the first 4 columns into one and the other 4 as well, it would be even simpler, of course, but is that the case?
Here, I have planned an empty column between the two others, if that is not the case, just modify:
"pc+2" to "pc+1" (twice) and pc+3 to pc+2

Option Explicit
Dim pl%, pc%, dl%

Sub Compare()
pl = 1 'number of the first row
pc = 1 'first column
dl = Range("A65536").End(xlUp).Row
'here we set the color black to the data of columns 1 and 3, then we delete column 4
'for the case where you have modified and want to run again
Range(Cells(pl, pc), Cells(dl, pc+2)).Select
Selection.Font.ColorIndex = 0
Columns(4).Delete Shift:=xlToLeft
For i = pl To dl
If Cells(i, pc) <> Cells(i, pc + 2) Then
Cells(i, pc).Font.ColorIndex = 3
Cells(i, pc + 2).Font.ColorIndex = 3
Cells(i, pc + 3) = "Different"
End If
Next i
End Sub

Hello everyone,

It's in trial on about 30 lines in column A: there is no need for test column J
I have to be away for a few hours, thanks for being patient

but if you have several thousand lines, it would be necessary to approach it differently in terms of speed
--
Michel

Hello,

As Via55 writes, if the number of rows is constant for the two groups of three columns, it should work:

Option Explicit
Dim i%, n%, dl%, x%
Sub Compare()
dl = Range("A65536").End(xlUp).Row
 For n = 1 To 3
 For i = 1 To dl
 x = n + 4
 If Cells(i, n) <> Cells(i, x) Then
 Cells(i, n).Font.ColorIndex = 3
 Cells(i, x).Font.ColorIndex = 3
 Cells(i, n + 1) = "Different"
 End If
 Next i
 Next n
End Sub

Of course, we can modify the number of columns (value of "n").

The response from via55 perfectly corresponds to my issue, I am closing the topic, thank you to everyone who took the time to help me! :)