Evaluate Secondary Set of Columns to Identify Value Mismatch

[censo]

PREFACE

A current forum member helped me put the following code together which successfully accomplishes the following:

Identifies missing values from one column as compared to another (Columns A:C; aka SKU to SKU)
If a value is missing in Column A (which is present in Column C), report the missing values in Column E:F
If a value is missing in Column C (which is present in Column A), report the missing values in Column G:H

Sub SKUChecker()
    Application.ScreenUpdating = False

   Worksheets("TRUE-UP").Activate

    Dim lrA As Long
    Dim lrC As Long
    Dim rngA As Range
    Dim rngC As Range
    Dim cell As Range

    
'   Find last row with data in column A
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
'   Find last row with data in column C
    lrC = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Set data ranges
    Set rngA = Range("A2:A" & lrA)
    Set rngC = Range("C2:C" & lrC)
    
'   Loop through all rows in column C
    For Each cell In rngC
'       Search for value in column A
        If Application.WorksheetFunction.CountIf(rngA, cell.Value) = 0 Then
'           Copy value to column E
            Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell

'   Loop through all rows in column A
    For Each cell In rngA
'       Search for value in column C
        If Application.WorksheetFunction.CountIf(rngC, cell.Value) = 0 Then
'           Copy value to column G
            Cells(Rows.Count, "G").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell

'Corresponding Identifier for Column F
'=IF(E2<>"",VLOOKUP(E2,C:D,2,0),"")

    With Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = _
        "=IF(RC[-1]<>"""",VLOOKUP(RC[-1],C[-3]:C[-2],2,0),"""")"
    .Value = .Value
End With


'Corresponding Identifier for Column H
'=IF(G2<>"",VLOOKUP(G2,A:B,2,0),"")

    With Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = _
        "=IF(RC[-1]<>"""",VLOOKUP(RC[-1],C[-7]:C[-6],2,0),"""")"
    .Value = .Value
End With

    Application.ScreenUpdating = True
End Sub

OBJECTIVE
To build upon this solution, I would like to compare a secondary set of Columns (B:D) to see if they match each other.


REQUIREMENT
Only successful (unique) matches from Columns A:C in the previous check are to be evaluated. In other words, the results produced as missing values from Columns A:C (which are visually represented in Columns E:H) are not to be evaluated


GREATER DETAIL

1. Stated differently - Source Identifier (Column B) pairs with its respective Source SKU (Column A), so if the matching Remote SKU (Column C) has a different Remote Identifier, display the results as shown in Columns I:K
2. The evaluation only occurs in one direction, meaning Columns A:B are the key fields to which Column D must match in order to be accurate.
3. To make the anomaly easy to view, I've highlighted the non-matching values in Orange in the diagram below.

DESIRED RESULT
Columns I:K represent the desired result of the macro which I wish to be integrated into the code provided above.

MRE-FORMUM.SMPL.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	SOURCE SKU	SOURCE IDENTIFIER	REMOTE SKU	REMOTE IDENTIFIER	MISSING FROM A	MISSING FROM B	MISSING FROM C	MISSING FROM D	SOURCE SKU	SOURCE IDENTIFIER MISMATCH	IDENTIFIER MISMATCH
2	ABC-123-001	A11QEUX	ABC-123	A11QESF	ABC-123	A11QESF	ABC-123-003	A11QESI	ABC-123-001	A11QEUX	A11QESG
3	ABC-123-002	A11QESH	ABC-123-001	A11QESG	ABC-123-005	A11QESK			ABC-123-004	A11QETR	A11QESJ
4	ABC-123-003	A11QESI	ABC-123-002	A11QESH	ABC-123-006	A11QESL
5	ABC-123-004	A11QETR	ABC-123-004	A11QESJ
6			ABC-123-005	A11QESK
7			ABC-123-006	A11QESL
TRUE-UP

 

[Joe4]

OK, using a lot of the same concepts we did in the other question, and making use of the multi-criteria COUNTIFS function, this seems to work to populate columns I:K for the example you posted:

Sub PerformMatch2()

    Dim lrA As Long
    Dim lrC As Long
    Dim rngA As Range
    Dim rngC As Range
    Dim rngD As Range
    Dim rngRI As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
'   Find last row with data in column C
    lrC = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Set data ranges
    Set rngA = Range("A2:A" & lrA)
    Set rngC = Range("C2:C" & lrC)
    Set rngD = Range("D2:D" & lrC)
    
'   Loop through all rows in column A
    For Each cell In rngA
'       First see if value from column A is found in column C
        If Application.WorksheetFunction.CountIf(rngC, cell.Value) > 0 Then
'           Search for matching values to columns A and D in columns C and D
            If Application.WorksheetFunction.CountIfs(rngC, cell.Value, rngD, cell.Offset(0, 1).Value) = 0 Then
'               Copy value to columns I and J
                Range(cell, cell.Offset(0, 1)).Copy Cells(Rows.Count, "I").End(xlUp).Offset(1, 0)
'               Put missing value from column D in column K
                Set rngRI = rngC.Find(cell.Value).Offset(0, 1)
                Cells(Rows.Count, "K").End(xlUp).Offset(1, 0) = rngRI.Value
            End If
        End If
    Next cell
     
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[censo]

Nailed it first try. God send.

Thank you!

 

[Joe4]

You are welcome.
Hope it all makes sense.
Let me know if you have any questions about any of the code.

 

[censo]

Haha, I would love to throw all of my Macro files at ya to help me re-write them as I can already tell that your code/structure is way more efficient than what I've pieced together throughout the years (coincidentally, using the Macro recorder as I'm a trained Network Admin guy, not a coder).

 

[Joe4]

using the Macro recorder as I'm a trained Network Admin guy, not a coder)

The Macro Recorder is a great tool to get snippets of code. Even us "pros" still use it to get code snippets.
That being said, it is very literal, so there is usually some clean-up you can do afterwards to make it a little shorter and a little more efficient (usually all the "Select" and "Scroll" lines of code are unnecessary).

 

[censo]

... it is very literal

It is because of that structure that I've been able to understand the VBA language to the degree I know it. When you and others use these (for lack of better words) "tags" like Dim, Lr, Set, and If ... that's where I check-out because now it looks like a very different language to me that I can't reverse-engineer (without training, of course) like I can with the Macro Recorder.

That's why your notation has been super helpful to me and I cannot express how grateful I am for the additional effort you put into your contributions.

 

[Joe4]

You are welcome.

Just a few small tidbits that may help a little in your understanding a little...

You use "Dim" to declare variables that you want to use in VBA (it is not required that you declare them before using them, but using them helps you identity what they are and what they can contain).
Here is an article on that: Declaring variables (VBA)

So anything listed after the word "Dim " is just a variable name (like "Lr"), and you can name it pretty much anything you like (except avoid using reserved words - things already used by Excel like "SUM", "LEFT", etc).

"Set" is just used to assign an object to object variables (like ranges, worksheets, workbooks), i.e.

Set rng = Range("A1:A100")

Variables that are not objects do not use the "Set" command when assigning them values, i.e.

myName = "Joe"
myVal = Range("A1").Value

VBA has its own functions (like "If", "With", etc). Many have the same name and similar structures as Excel worksheet functions (like "Left", "Right", "+", "="), but not all. Sometimes Excel has an equivalent that is named differently (i.e. the VBA "Format" function is similar to the Excel "Text" function).

Here are two of the most common clean-ups that you can usually do on recorded code:

1. Whenever you have one line that ends in "Select" and the next starts with "Selection" or "ActiveCell", they can usually be combined together (as you usually do not need to select ranges in order to work with them).
So, for example, something like this:

    Range("G11").Select
    ActiveCell.FormulaR1C1 = "Test"

can be simplified to this:

    Range("G11").FormulaR1C1 = "Test"

or even more so to this:

    Range("G11") = "Test"

(since "Test" is a literal string and not a formula, you do not really need the "FormulaR1C1" part.

2. You can remove all screen scrolling commands, as they are not necessary to the code (it is just the Macro Recorder recording your scrolling literally).
So you can delete rows that look like this:

    ActiveWindow.SmallScroll Down:=36

I know those are just a "drop in the bucket" to the VBA language, but I hope it helps increase your understanding a little, and gives you a few good tips.

 

[censo]

I know those are just a "drop in the bucket" to the VBA language, but I hope it helps increase your understanding a little, and gives you a few good tips.

It most definitely does - thanks again, my friend.