Compare 2 columns for non-matching numbers

Hi Rochelle, welcome (back?) to the board!
Which columns are the phone numbers in? (Are they on the same sheet?)
And where would you like to put the ones that don't match?

Dan

Hi!
Heres one solution to that.
This checks if the entry in B is in column A. if it find no match then it will write that value in column C.

This check for booth column A and column B.

Thanks!

Dan, for the purposes of this exercise, assume ColumnA and ColumnC contain the lists of numbers, and I'd like to put the exceptions in ColumnE.

I tried the other formula and nothing happened... I'm probably doing something wrong...

Rochelle

Hi SixthSense

That second formula seems to work a treat! Thank you. I'll probably have another question shortly...

R

OK, then using SIXTH SENSE's fine formula you would enter this in E1:
=IF(COUNTIF(A:A,C1)=0,C1,"")
and copy this down Col. E as far as needed.

Myself, I was thinking of some VBA (as I suck at formulas and therefore tend to go the other route), but this looks like it would work just dandy.
It will show in Col. E just the numbers in Col. C that don't exist in Col. A.

This get it done for you?
Dan

Dan, you and Sixth Sense are my new best friends! And, hey, I stink at formulas AND VBA (but I can't leave a problem unsolved so I usually work things out myself). So if you want to write some code to further enhance my education, I'd be only too happy to give it a go!

R :wink: ray:

Oh, and one more thing - What if I now want to count the results? How do I separately count those records that were missing from columnA and ColumnB??

Well, I don't know what this will do for anyone's education as there are more efficient ways to get things done than using loops (and it's easier for me to find numbers that are there than ones that aren't ...) but this is one way to get it done. If you don't have tens of thousands of cells to go through the speed shouldn't be much of an issue.
I assume the "newest" list (and therefore likely the longest of the 2) is the list in Col.C (?) If not then we'll have to make a few minor changes... Try this out and see if it would work for you.
Dan

HalfAce said:

Well, I don't know what this will do for anyone's education as there are more efficient ways to get things done than using loops (and it's easier for me to find numbers that are there than ones that aren't ...) but this is one way to get it done. If you don't have tens of thousands of cells to go through the speed shouldn't be much of an issue.
I assume the "newest" list (and therefore likely the longest of the 2) is the list in Col.C (?) If not then we'll have to make a few minor changes...

Code:

Sub FindTheNewNumbers()
Application.ScreenUpdating = False
Dim i As Range
Dim j As Range
Set i = Range("A1", Range("A1").End(xlDown))
Set j = Range("C1", Range("C1").End(xlDown))
       
       For Each x In i
         For Each y In j
             If x.Value = y.Value Then
                x.Copy y.Offset(, 2)
             End If
         Next y
       Next x

Count = 0
For Each cell In Range("C1", Range("C65536").End(xlUp))
    If cell.Offset(, 2) <> "" Then
    cell.Offset(, 2) = ""
    Else:
    cell.Offset(, 2) = cell.Value
    Count = Count + 1
    End If
    Next cell

Application.ScreenUpdating = True

MsgBox ("There are " & Count & " phone numbers in Column C  that are not in Column A"), , "Differences"

End Sub

Try this out and see if it would work for you.
Dan

Click to expand...

Hi Ace,
Your code takes a lot of time in checking each value in A for each value in B. Meaning you will have ColumnA*ColumnB number of loops. and if you will also check B against A, then, thats twice as much.

I think this runs faster.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyUnique()
      <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Range("A65536").End(xlUp).Row
           <SPAN style="color:#00007F">If</SPAN> Application.WorksheetFunction.CountIf(Range("b:b"), Range("a" & i)) = 0 <SPAN style="color:#00007F">Then</SPAN>
                Range("d" & 1 + Range("d65536").End(xlUp).Row) = Range("A" & i)
           <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
      <SPAN style="color:#00007F">Next</SPAN> i
      <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Range("B65536").End(xlUp).Row
           <SPAN style="color:#00007F">If</SPAN> Application.WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 0 <SPAN style="color:#00007F">Then</SPAN>
                Range("E" & 1 + Range("E65536").End(xlUp).Row) = Range("B" & i)
           <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
      <SPAN style="color:#00007F">Next</SPAN> i

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>