compare
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
123456789
234567890 (plus 1,000 other 9 digit #'s)
file b also contains
123456789
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
=IF(ISERROR(MATCH(A1,$C$1:$C$1465,0)),"",A1)
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
=IF(ISERROR(VLOOKUP(A1,$B$1:$B$2000,1,FALSE)),A1,"")
this function put in c1 and autofilled down will show the A1 value,
IF A1 does not exist anywhere in B1:B2000 -
