VBA to compare two pairs of columns and return missing values

[sncb]

Good Day,

I have data that looks like this and would like to use VBA to obtain results faster. Essentially Im comparing pairs of columns A/B with C/D and getting the missing values in E/F

Data starts in A1 and lies in Sheet1

Right now I'm managing with excel formulas but the repetition is cumbersome so thinking that VBA might be a better way forward. Thanks for any help with this.

Book3
	A	B	C	D	E	F
1	A	Austin	B	Austin	B	Chicago
2	A	Boston	B	Boston	B	Houston
3	A	Chicago	B	Dallas	B	Portland
4	A	Dallas	B	New York
5	A	Houston	B	Seattle
6	A	New York
7	A	Portland
8	A	Seattle
Sheet1

 

[Joe4]

Try this VBA code:

Sub MyUnmatchMacro()

    Dim lr As Long, r As Long
    Dim x As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows in column B
    For r = 1 To lr
'       See if value in column B is not found in column D
        If Application.WorksheetFunction.CountIf(Range("D:D"), Cells(r, "B").Value) = 0 Then
'           Increment unmatch counter
            x = x + 1
'           Copy values to column E and F
            Range(Cells(r, "A"), Cells(r, "B")).Copy Cells(x, "E")
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[sncb]

Thanks Joe,

However in column E, Im getting the values from Col A when the expected result should be data from Col C. The data in Col F is correct and so are number of records.

 

[Joe4]

Thanks Joe,

However in column E, Im getting the values from Col A when the expected result should be data from Col C. The data in Col F is correct and so are number of records.

I am confused by your description then.
You said:
Essentially Im comparing pairs of columns A/B with C/D and getting the missing values in E/F

It looked to me like you wanted to match column B to column D, and then return the unmatched values from A/B to E/F.
If that is not the case, what is the purpose of column A?
Please explain exactly how you want the match to work, and where the values in columns E and F are coming from.
(Note that if you are matching BOTH columns A and B to C and D, you have NO matches in your example, and all records from A & B would be returned in E & F!)

 

[sncb]

Hi Joe,

Thanks for your reply. Yes maybe I should have framed my question better. What Im trying to get in columns E and F is the combination of data in Col C with the values missing in D in comparison to col B. So in my example Chicago, Houston, Portland are missing setup with the data in Col C.

I agree there's no need of Col A from the perspective of the code itself but i just wanted to show how my data is before I run the code.

 

[Joe4]

Still a little unclear.
I can see that the values in column B which are not found in column D are being put in column F.
But where is the value in column E coming from?
How are columns A and C being used in this at all?

Perhaps you have oversimplified the problem/data to the point where we cannot see the relation, and it isn't making much sense.

 

[sncb]

Hi Joe, Data in Col E is simply the data from Col C. Col A is not needed at all for the purpose of the code but I was just displaying my existing data.

I'm just trying to have prepared data in Cols E and F using the VBA output for an upload.

 

[sncb]

I Hope this is clear. Thanks again Joe.

 

[Joe4]

If we are returning the UNMATCHED records, how do we know what value from column C to put in column E?
If it was matched records, it would be obvious. But since they are unmatched, how do we know where to pull the value from?
Or will all the records in column C be exactly the same for every single record, so it doesn't matter which record from column C we grab?

Or, is it that column C is really related to column B and NOT column D.
So when we find a value from column B that is not in column D, we grab the values from column B and C from that row, flip them around, and put them in columns E and F?

 

[Joe4]

Actually, it does not look like my last thought is correct, as there are values in B6:B8 but not in C6:C8.
So for the example of Portland, that you show in column E, how do you know that a "B" should be in column F?
Where does that come from for this Portland record?