look for missing numbers between sheets

**mingali** · 02-26-2010, 02:00 PM

Hi

I have worksheets named "Source" and "sheet 2" contains the same info but in different orders. May i have a formula to look for the missing number that cause the discrepencies?

Thanks

**JBeaucaire** · 02-26-2010, 02:19 PM

In C4 on Source, you could try this formula, copied down:

=IF(A4<>"",IF(ISNUMBER(MATCH(A4,Sheet2!A:A, 0)),"","not found"),"")

**mingali** · 02-26-2010, 02:36 PM

it does not work well as some codes show in both sheets also appears as "not found". Any other formula offer?

Thanks

**JBeaucaire** · 02-26-2010, 02:54 PM

If that is true, then there is a problem with your match strings. The formula should only report "not found" when the item is truly not there.

Post up a sample workbook and point out where the formula is giving incorrect results. In those cases, examine the cells...are there hidden spaces in the values?

**martindwilson** · 02-26-2010, 08:20 PM

better still ,try telling us which ones aren't found!then we may have a chance of finding out why.

**mingali** · 02-27-2010, 12:07 AM

I attached the workbook, please have a look. i applied the formulae to those two wks. eg both wks have 20C01, 20C02 and 20C03 etc but still it was stated not found.

Thanks

**JBeaucaire** · 02-27-2010, 12:23 AM

Like so:

=IF(A4<>"",IF(ISNUMBER(MATCH(A4,Sheet2!A:A, 0)),"","not found"),"")

**mingali** · 02-27-2010, 01:10 AM

Thanks for the answer. It helps a lot.

In additon, may i know what formula to use in the event of looking for discrepencies in number given the same code? ie 20M11 is 8 for one wks while it is 7 in another.

Thanks again

**JBeaucaire** · 02-27-2010, 03:39 AM

In C4 of the source sheet:

=IF(A4<>"", IF(ISNUMBER(MATCH(A4, Sheet2!A:A, 0)), IF(INDEX(Sheet2!B:B, MATCH(A4, Sheet2!A:A, 0))=B4, "", "not equal"), "not found"), "")