Spotting the difference between two colums of text
 

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?

Spotting the difference between two colums of text
 

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

Spotting the difference between two colums of text
 

Thank you for your response.

I have stumbled upon this link -
http://office.microsoft.com/en-us/ex...039151033.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!



"Dave" wrote in message
...
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

Spotting the difference between two colums of text
 

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 C2:D2 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wowbagger" <Wowbagger~~ wrote in message
...
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?

Spotting the difference between two colums of text
 

Oh, if only everything could be this simple!
See the equally simple response just posted which crossed your reply here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Spotting the difference between two colums of text
 

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 .... ?

Spotting the difference between two colums of text
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wowbagger" <Wowbagger~~ wrote in message
...
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 .... ?

Spotting the difference between two colums of text
 

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


"Max" wrote in message
...
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wowbagger" <Wowbagger~~ wrote in message
...
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 .... ?

Spotting the difference between two colums of text
 

What if you don't know how many rows there are?
Double click on the fill handle of the top cell?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---