Finding Missing and duplicated Numbers

  • Thread starter Thread starter Ujpest
  • Start date Start date
U

Ujpest

I have a table

name a 15
name b 16
name c 18

There will be 80 names and 54 numbers (1 to 54).

I need a formular that would search the list of numbers and report back any
duplicate numbers and missing numbers.

Is that possible?
 
Here is a simple solution. I put my names in A6:A85 and numbers in B6:B85
If your numbers are somewhere else change the references to $B$6:$B$85 in my
formulas but do not change reference to A1.

In C6 enter this formula =IF(COUNTIF($B$6:$B$85,B6)-1,"Duplicate","")
Copy it down the column by double clicking the fill handle
Numbers that are duplicated will be so indicated

In D6 enter this formulas =IF(ISNA(MATCH(ROW(A1),$B$6:$B$85,0)),ROW(A1),"")
Copy down the column
Numbers that are missing in column B will be displayed in column D

best wishes
 
That is brilliant
thank you

Bernard Liengme said:
Here is a simple solution. I put my names in A6:A85 and numbers in B6:B85
If your numbers are somewhere else change the references to $B$6:$B$85 in my
formulas but do not change reference to A1.

In C6 enter this formula =IF(COUNTIF($B$6:$B$85,B6)-1,"Duplicate","")
Copy it down the column by double clicking the fill handle
Numbers that are duplicated will be so indicated

In D6 enter this formulas =IF(ISNA(MATCH(ROW(A1),$B$6:$B$85,0)),ROW(A1),"")
Copy down the column
Numbers that are missing in column B will be displayed in column D

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



.
Click to expand...
 
Back
Top