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

 

[sncb]

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? -
Yes this is absolutely correct.

Let me explain in another way. Im comparing two items, one of which has records setup in all warehouse cities (Cols A and B) and the second item which are setup in a lesser no of warehouses (Cols C and D). What Im trying to get with the VBA is prepared data that I can upload into my system. Therefore my final goal is to get the data from Col C in Col E (exactly the same) and then the warehouse cities of Col D that are missing in Col B in Col F.

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

Hope this is clearer. Thanks again.

 

[Cubist]

You mentioned formula is cumbersome but would this work for your purposes?

Book1
	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
Sheet6

Cell Formulas
Range	Formula
E1:E3	E1	=TAKE(C1:C5,COUNTA(F1#))
F1:F3	F1	=UNIQUE(VSTACK(B1:B8,D1:D5),,1)
Dynamic array formulas.

 

[Joe4]

OK, this update to the code should do what you want:

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 value from column B to column F
            Cells(r, "B").Copy Cells(x, "F")
'           Copy first value in column C to column E
            Cells(1, "C").Copy Cells(x, "E")
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[sncb]

Yes. This worked. Thanks a lot for your help and effort. Appreciate it very much.

 

[Joe4]

You are welcome.
Glad we were able to help!

Note that if you are using Excel 365, Cubist did offer a formula solution that is not too cumbersome.

 

[sncb]

You mentioned formula is cumbersome but would this work for your purposes?

Book1
	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
Sheet6

Cell Formulas
Range	Formula
E1:E3	E1	=TAKE(C1:C5,COUNTA(F1#))
F1:F3	F1	=UNIQUE(VSTACK(B1:B8,D1:D5),,1)
Dynamic array formulas.

Hi Cubist. yes this also worked great. I'll use this as well too. Thanks