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?

Hi,
For duplicates see

http://www.cpearson.com/excel/Duplicates.aspx



"Ujpest" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Ujpest" wrote in message
...
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?

That is brilliant
thank you

"Bernard Liengme" wrote:

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

"Ujpest" wrote in message
...
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?

.

Thanks for feedback
Bernard

"Ujpest" wrote in message
...
That is brilliant
thank you

"Bernard Liengme" wrote:

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

"Ujpest" wrote in message
...
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?

.