Matching 2 list

[larper]

I'm trying to match two sets of records to identify which records are not contain in the othere. I have two output reports that have over 40,000 records. They are both made from the same data base, and seemingly the same criteria, but I'm off 1.5m. Any help would be a headache saver!

 

[Aladin Akyurek]

Here an initial step...

=--ISNUMBER(MATCH(A2,List2,0))

where A2 is the first cell of List1.

 

[tusharm]

The MATCH function is adequate. No need to use the --ISNUMBER. That way if there is a match you will know the location of the match and you'll see a #N/A if there is no match -- easy to spot in a sea of numbers.

You may also want to use conditional formatting. Suppose your lists are named list1 and list2. Also suppose the first cell in each list is A1.

Select one, let's say list1. Select Format | Conditional Formatting... Set the first drop down to 'Formula Is' In the adjacent field enter the formula =ISNA(MATCH(A1,list2,0)). Set the Format to approriately (say a red font). Any cell that doesn't have a matching entry in list2 will be shown in red. Do the same for list2. That will highlight any cell in list2 that doesn't have a matching entry in list1.

 

[larper]

Thanks for the replies. I certainly learn some new turn. What I ended up doing is using a countif (=COUNTIF(timspos,C1156) where RangeName is
timspos" and "c" column hold each po# I need to verify if indeed is the other data base, then I just filter for "0" since it had no counts. The real problem is that they had hard coded filter names into access, and we open a new warehouse and no one update the VBA code in Access. Its done not.

Thanks Again ray:

 

[Aladin Akyurek]

Thanks for the replies. I certainly learn some new turn. What I ended up doing is using a countif (=COUNTIF(timspos,C1156) where RangeName is
timspos" and "c" column hold each po# I need to verify if indeed is the other data base, then I just filter for "0" since it had no counts. The real problem is that they had hard coded filter names into access, and we open a new warehouse and no one update the VBA code in Access. Its done not.

Thanks Again ray:

MATCH is faster than COUNTIF, even if wrapped inside ISNUMER (and the result is coerced).