Spotting the difference between two colums of text

[Wowbagger]

If I have a series of values (serial numbers) derived from one source in
column A and a series of values (also serial numbers) derived from a second
source in column B, is there a way to quickly identify which numbers do not
appear in both columns?

 

[Dave]

Hi Wowbagger,
I don't know if there's a worksheet function to do what you want, but
the following code, pasted into a module of your workbook will find
all data common to both columns, and turn them red. The non-red cells
will be the ones not found in both colomns.
I have assumed that there is a blank cell at the end of both columns,
but they do not have to be of equal length.
I have assumed that your data starts in Row 1. If not, change A and B
accordingly, as notorised.
The code does not tell you if a number appears more than once in the
same column. This could be easily added.
Regards - Dave.

Sub check_Duplicates()
A = 1 '1st row number of first column
B = 1 '1st row number of second column
Do Until Cells(A, 1).Value = ""
Do Until Cells(B, 2).Value = ""
If Cells(A, 1) = Cells(B, 2) Then
Cells(A, 1).Font.ColorIndex = 3
Cells(B, 2).Font.ColorIndex = 3
Exit Do
End If
B = B + 1
Loop
B = 1
A = A + 1
Loop
End Sub

 

[Wowbagger]

Thank you for your response.

I have stumbled upon this link -
http://office.microsoft.com/en-us/excel/HA011039151033.aspx - that has
something even easier. (I don't know if it works with versions prior to
Office 2007)

=ISNA(MATCH(E3,$I$3:$I$14,FALSE)).

Oh, if only everything could be this simple!

 

[Max]

For quick diagnostics, think you could also use this:

Assume source data in A2:B2 down
In C2: =IF(A2="","",--ISNUMBER(MATCH(A2,B:B,0)))
In D2: =IF(B2="","",--ISNUMBER(MATCH(B2,A:A,0)))
Copy C22 down to the last row of source data

Col C checks col A against col B
It will return: 0,1 or blanks: "", where
0 = col A value is not found in col B
1 = col A value is found in col B
blanks: "" means there's nothing in col A to be checked

Col D returns similarly for the converse checks of col B against col A

You could then easily apply/use autofilter on cols C & D

 

[Max]

Oh, if only everything could be this simple!
See the equally simple response just posted which crossed your reply here

 

[Wowbagger]

Thank you.

Now, is there a quick and easy way to paste copies in a cell down to the
last used row?

For example: if I have data in A1 : A720, what is the quickest way to paste
the formula into cells C1 : C720 without having to scroll and scroll and
scroll and .... ?

 

[Max]

One way, with the formula pasted in C1

Click inside the namebox*, enter the range: C1:C720
Press Enter, this selects the range with C1 active
*the box with the dropdown just to the left of the formula bar

Click inside the formula bar, press CTRL+ENTER
This will fill the entire range (same as dragging down from C1)

 

[Wowbagger]

What if you don't know how many rows there are?

One way, with the formula pasted in C1

Click inside the namebox*, enter the range: C1:C720
Press Enter, this selects the range with C1 active
*the box with the dropdown just to the left of the formula bar

Click inside the formula bar, press CTRL+ENTER
This will fill the entire range (same as dragging down from C1)

 

[Max]

What if you don't know how many rows there are?
Double click on the fill handle of the top cell?