Compare Multiple Columns with Multiple Columns

[Kent Heng]

Hi all,

I have 3 base columns of data to compare against 3 other columns of data, and i want to highlight the differences between them. Problem is, the 3 columns to check against, might not be in sequence with the 3 base columns.

Example as below:

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6
POL	POD	ContainerNo	POL	POD	ContainerNo
PGLAE	ZACPT	TRHU3305091	PGMTK	AEJEA	XAAB5961250
PGLAE	MYPGU	DRYU2142162	PGLAE	BDCGP	TRHU3305091
PGLAE	IDSUB	BSIU2922939	PGLAE	AEJEA	DRYU2142162
PGLAE	AEJEA	XAAB5961250	PGLAE	IDSUB	BSIU2922939

<tbody>
</tbody>

The first 3 columns (in black), are the base columns and are correct. The next 3 columns (in red), could have errors in any of the 3 columns. I want to be able to spot the errors. The ContainerNo in Column 3 will be fixed and unique, while POL & POD can be duplicates.

The ideal outcome i want is as below (errors to be highlighted - blue in this example), while correct to remain unchanged:

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6
POL	POD	ContainerNo	POL	POD	ContainerNo
PGLAE	ZACPT	TRHU3305091	PGMTK	AEJEA	XAAB5961250
PGLAE	MYPGU	DRYU2142162	PGLAE	BDCGP	TRHU3305091
PGLAE	IDSUB	BSIU2922939	PGLAE	AEJEA	DRYU2142162
PGLAE	AEJEA	XAAB5961250	PGLAE	IDSUB	BSIU2922939

<tbody>
</tbody>

My thinking is that the formula will be based on Column 3 (ContainerNo), checking against Column 6 (ContainerNo), if match, then Check Column 1 against Column 4, and Column 2 against Column 5. If Column 6 (ContainerNo) does not match with Column 3 (ContainerNo), to highlight that particular ContainerNo in Column 6. (AKA ContainerNo is unique).

Would need the solution (and explanation) in VBA, as the macro will be stored in a worksheet to be used by a department.

Hope the above is clear enough.

Appreciate any help!!

Cheers

 

[Fluff]

How about

Sub Checkdata()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Array(Cl.Offset(, -2).Value, Cl.Offset(, -1).Value)
      Next Cl
      For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            Cl.Interior.Color = 45678
         ElseIf .Item(Cl.Value)(0) <> Cl.Offset(, -2).Value Then
            Cl.Offset(, -2).Interior.Color = 45678
         ElseIf .Item(Cl.Value)(1) <> Cl.Offset(, -1).Value Then
            Cl.Offset(, -1).Interior.Color = 45678
         End If
      Next Cl
   End With
End Sub

 

[Peter_SSs]

Would another possibility be to use Conditional Formatting (applied by vba)?

Sub Apply_CF()
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("D2:F" & lr)
    .FormatConditions.Delete
    .Resize(, 2).FormatConditions.Add Type:=xlExpression, Formula1:=Replace("=AND(COUNTIF($C$2:$C$#,$F2)>0,COUNTIFS($C$2:$C$#,$F2,A$2:A$#,D2)=0)", "#", lr)
    .Resize(, 2).FormatConditions(1).Font.Color = vbBlue
    .Columns(3).FormatConditions.Add Type:=xlExpression, Formula1:=Replace("=ISNA(MATCH(F2,$C$2:$C$#,0))", "#", lr)
    .Columns(3).FormatConditions(1).Font.Color = vbBlue
  End With
End Sub

 

[Kent Heng]

Hi Fluff,

Yes, this works great! Thanks!

 

[Kent Heng]

Hi Peter,

Your code produces the same result as Fluff's (above).

Thanks for your help!!

Would another possibility be to use Conditional Formatting (applied by vba)?

Sub Apply_CF()
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("D2:F" & lr)
    .FormatConditions.Delete
    .Resize(, 2).FormatConditions.Add Type:=xlExpression, Formula1:=Replace("=AND(COUNTIF($C$2:$C$#,$F2)>0,COUNTIFS($C$2:$C$#,$F2,A$2:A$#,D2)=0)", "#", lr)
    .Resize(, 2).FormatConditions(1).Font.Color = vbBlue
    .Columns(3).FormatConditions.Add Type:=xlExpression, Formula1:=Replace("=ISNA(MATCH(F2,$C$2:$C$#,0))", "#", lr)
    .Columns(3).FormatConditions(1).Font.Color = vbBlue
  End With
End Sub

 

[Peter_SSs]

Hi Peter,

Your code produces the same result as Fluff's (above).

There is a difference, though it may not be relevant to your circumstances.
With my code, if any values in the sheet are subsequently changed, the colours would, if relevant, change automatically without having to re-run the code.