MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by JTG on January 29, 2001 12:52 PM

I wish to locate duplicate data (ID#'s) in two sets of information from two similar databases.
The data consists of a 9 digit unique ID # (for both files)
File a = ID#'s for a condition during 2000
File b = ID#'s for a condition in 2001
(No actual dates are used in the files, just ID#'s)
What want to do is find matches and/or exceptions to the list i.e.,:
file a contains
234567890 (plus 1,000 other 9 digit #'s)
file b also contains
234567890 (Plus about 1,200 other ID's)
and these are the only two matching ID's contained in both Files a & b (But I an unaware and must locate them)

I was using this
and pasting 2000 info in column a then pasting
2001 into column c then entering this formula into b1 and using the Edit Fill Down command.
Is there a better, more efficient way or a lsee confusing way? Thnaks You

Posted by Loren on January 29, 2001 1:06 PM

Two ways....
given 2 lists of words

1. Select column B (click the letter B)
2. Choose Format > Conditional formatting
" 3. In ""Condition 1"" choose ""Formula is"""
" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<>"")"
" 5. Click the ""Formats"" button and take your pick. OK.OK."

Now every name in B not in A will light up.

Another example
comparison of 2 columns
this function put in c1 and autofilled down will show the A1 value,
IF A1 does not exist anywhere in B1:B2000 -