Comparing Values that are NOT equal

[quakeworld]

hello. i'm trying to accomplish this in excel vba:

say i have 2 columns of numbers

col A has 1, 2, 3, 4 5

col B has 1,2,6,7,8

how would i go about comparing the two columns using VBA to find out what values in column B (6,7,8) that are NOT in col A? kinda like a vlookup but using VBA? thanks.

 

[Tweak155]

This is easiest solved with a cell formula, but sounds like you really want VBA, where another infinite set of solutions exist

A simple go through the rows approach:

Dim lngRow as Long

For lngRow = 1 To Cells(Rows.Count, 2).End(xlUp).Row
    If Cells(lngRow, 1).Value <> Cells(lngRow, 2).Value Then
    'Do something useful here!
        MsgBox "Column B has value " & Cells(lngRow, 2).Value & ", which doesn't match Column A on row " & lngRow & "!"
    End If
Next lngRow

 

[quakeworld]

This is easiest solved with a cell formula, but sounds like you really want VBA, where another infinite set of solutions exist

A simple go through the rows approach:

Dim lngRow as Long

For lngRow = 1 To Cells(Rows.Count, 2).End(xlUp).Row
    If Cells(lngRow, 1).Value <> Cells(lngRow, 2).Value Then
    'Do something useful here!
        MsgBox "Column B has value " & Cells(lngRow, 2).Value & ", which doesn't match Column A on row " & lngRow & "!"
    End If
Next lngRow

thanks for the quick reply. your code compares row to row though. even with a 2nd loop i can't make it work. im doing something wrong.

edit:

what if col A is:

1,2,3,4,5,6

col B is:

5,2,3,9,1,7

the output im looking for is 9 and 7

 

[Tweak155]

thanks for the quick reply. your code compares row to row though. even with a 2nd loop i can't make it work. im doing something wrong.

edit:

what if col A is:

1,2,3,4,5,6

col B is:

5,2,3,9,1,7

the output im looking for is 9 and 7

Ah, did not realize that is what you meant. How efficient does the solution have to be? That can be a real dog with a large data set.

 

[quakeworld]

Ah, did not realize that is what you meant. How efficient does the solution have to be? That can be a real dog with a large data set.

right now im just trying to get it to run. the data set can get quite large. im actually comparing values in excel against records in an access table. im trying to identify what values in excel are not in the access table. im thinking once the algorithm is figured out i can apply it in this scenario.

 

[Tweak155]

The double loop:

Dim strExtraValues As String

Dim lngColA As Long, lngColB As Long, blnMatch As Boolean

For lngColB = 1 To Cells(Rows.Count, 2).End(xlUp).Row
    blnMatch = False
    For lngColA = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(lngColA, 1).Value = Cells(lngColB, 2).Value Then
            blnMatch = True
            Exit For
        End If
    Next lngColA
    If Not blnMatch Then
        strExtraValues = strExtraValues & Cells(lngColB, 2).Value & ", "
    End If
Next lngColB

If Len(strExtraValues) > 0 Then strExtraValues = Left(strExtraValues, Len(strExtraValues) - 2)

MsgBox "Col B has these extra values: " & strExtraValues

 

[Tweak155]

right now im just trying to get it to run. the data set can get quite large. im actually comparing values in excel against records in an access table. im trying to identify what values in excel are not in the access table. im thinking once the algorithm is figured out i can apply it in this scenario.

Wow this is way easier if you can use SQL. You can set up a connection to Access in Excel and just run a query inside the loop.

 

[quakeworld]

The double loop:

Dim strExtraValues As String

Dim lngColA As Long, lngColB As Long, blnMatch As Boolean

For lngColB = 1 To Cells(Rows.Count, 2).End(xlUp).Row
    blnMatch = False
    For lngColA = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(lngColA, 1).Value = Cells(lngColB, 2).Value Then
            blnMatch = True
            Exit For
        End If
    Next lngColA
    If Not blnMatch Then
        strExtraValues = strExtraValues & Cells(lngColB, 2).Value & ", "
    End If
Next lngColB

If Len(strExtraValues) > 0 Then strExtraValues = Left(strExtraValues, Len(strExtraValues) - 2)

MsgBox "Col B has these extra values: " & strExtraValues

this is great. will study this and see if i can apply it in my situation. this should get me started. thanks for the help!

 

[Tweak155]

this is great. will study this and see if i can apply it in my situation. this should get me started. thanks for the help!

NP!

 

[Fallen Kell]

Can't you just use ColumnDifferences?

https://msdn.microsoft.com/en-us/library/office/ff197752.aspx


Edit:
hmmm... maybe not. Looks like it can only compare against a single cell and not a range of cells like I initially thought. You would think that by now Microsoft would have added basic mathematical set functions, like union, complement, etc., to excel as you just want the complement....

 

[Ken g6]

right now im just trying to get it to run. the data set can get quite large. im actually comparing values in excel against records in an access table. im trying to identify what values in excel are not in the access table. im thinking once the algorithm is figured out i can apply it in this scenario.

The double loop:

When you get it working, a faster way would be:

1. Sort one of the columns of numbers. Preferably the small one, actually. There's a range.sort method, and you should try to use that; any fast sorting algorithm you write yourself would be rather complicated.

2. Go through the other list of numbers and perform a binary search for each number in the first list. Here are some examples of binary search code, though none are in VBA. I do suggest you work from something someone else has done, though - binary search is deceptively simple but hard to get exactly right.

 

[Ophir]

Aside from Access SQL, the fastest way I found is to loop through each value in one set and use the range.find method to find the value in the second set. If you sort both sets first, you can reset your find range to start from the next value down in the second set the next time through the loop.

I was able to trim the time to search through two ~50,000 row sets from 12 min to 3 compared with the two loops way.

Also, don't forget to turn off screen updating.

Pretty sure I don't have that code anymore - sorry.

 

[Tweak155]

When you get it working, a faster way would be:

1. Sort one of the columns of numbers. Preferably the small one, actually. There's a range.sort method, and you should try to use that; any fast sorting algorithm you write yourself would be rather complicated.

2. Go through the other list of numbers and perform a binary search for each number in the first list. Here are some examples of binary search code, though none are in VBA. I do suggest you work from something someone else has done, though - binary search is deceptively simple but hard to get exactly right.

If performance becomes an issue, I'd have recommended storing one column in a dictionary so the one loop only happens once and only once instead of searching every time.

 

[Tweak155]

Aside from Access SQL, the fastest way I found is to loop through each value in one set and use the range.find method to find the value in the second set. If you sort both sets first, you can reset your find range to start from the next value down in the second set the next time through the loop.

I was able to trim the time to search through two ~50,000 row sets from 12 min to 3 compared with the two loops way.

Also, don't forget to turn off screen updating.

Pretty sure I don't have that code anymore - sorry.

A dictionary would have made it seconds most likely.

 

[Tweak155]

Dictionary method:

Dim dicColA As Object

Set dicColA = CreateObject("Scripting.Dictionary")

Dim lngRow As Long

'Store Column A for faster searching
For lngRow = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Not dicColA.exists(Cells(lngRow, 1).Value) Then
        dicColA.Add Cells(lngRow, 1).Value, 1
    End If
Next lngRow

Dim strExtraValues As String

'Now loop through B to check what is in A
For lngRow = 1 To Cells(Rows.Count, 2).End(xlUp).Row
    If Not dicColA.exists(Cells(lngRow, 2).Value) Then
        strExtraValues = strExtraValues & Cells(lngRow, 2).Value & ", "
    End If
Next lngRow

If Len(strExtraValues) > 0 Then strExtraValues = Left(strExtraValues, Len(strExtraValues) - 2)

MsgBox "Col B has these extra values: " & strExtraValues

EDIT:

Time of dictionary method on 48383 rows: .443 seconds or 443 milliseconds.
Time on double the rows (96766 rows): 1.302 seconds
Time on 96766 rows with almost 100% mismatch: 3.421 seconds

I only gave the double loop method because that is how new developers start learning.