Matching 2 list

larper

Board Regular
Joined
Jan 13, 2005
Messages
66
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! :oops:
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Here an initial step...

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

where A2 is the first cell of List1.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Board Regular
Joined
Jan 13, 2005
Messages
66
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 :pray:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
larper said:
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 :pray:

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

Forum statistics

Threads
1,147,696
Messages
5,742,679
Members
423,747
Latest member
Shadeslayers09

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top