Spotting the difference between two colums of text

  • Thread starter Thread starter Wowbagger
  • Start date Start date
W

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?
 
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
 
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
 
Oh, if only everything could be this simple!
See the equally simple response just posted which crossed your reply here

---
 
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 .... ?
 
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)
 
What if you don't know how many rows there are?


Max said:
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)
Click to expand...
 
What if you don't know how many rows there are?
Double click on the fill handle of the top cell?

---
 
Back
Top