Comparing 2 sheets

[PauloG]

I have 2 sheets with serial numbers on them. Sheet 1 has over 19,000 serial
numbers on it and Sheet 2 has 3,000. Sheet 1 also has addresses attached to
them and Sheet 2 is simply the serial numbers. I want to compare these two
sheets and see which Serial Numbers on Sheet 2 appear in Sheet 1. I have
tried a few SUMIF and COUNTIF formulas but to no avail. I am desperate so any
would be appreciated!

 

[Pete_UK]

Assuming the serial numbers are in column A on both sheets, try this
in B1 of Sheet2:

=IF(ISNA(MATCH(A1,Sheet1!A$1:A$19000,0)),"No","Yes")

Copy this down your 3000 rows.

You can then apply autofilter to column B to select just the Yes
returns.

Hope this helps.

Pete

 

[PauloG]

Hi Pete,

Thanks for this but it does not seem to work. If I put the formula in to
Sheet 2 all cells say No and if I put the formula in Sheet 1 all cells say
Yes! Not sure what I am doing wrong.

Cheers

 

[Pete_UK]

The formula is meant to go in cell B1 of Sheet2. It looks to see if A1
in Sheet2 matches any of the 19000 entries in Sheet1 - if it does then
you will get Yes, otherwise No.

Are you sure that there are any exact matches in Sheet1? Both sets of
serial numbers must be of the same format, either real numbers or text
values. There should be no leading or trailing spaces if you do have
text values.

Try the formula out on a small sample of data in a separate file.

Hope this helps.

Pete

 

[PauloG]

Hi Pete,

Seems there was a couple of spaces after the serial numbers on Sheet 2 and
this is why they were not matching up. I removed them and the formula worked
a treat!

Cheers

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Hi Pete,

Seems there was a couple of spaces after the serial numbers on Sheet 2 and
this is why they were not matching up. I removed them and the formula worked
a treat!

Cheers








- Show quoted text -