Finding Missing and duplicated Numbers
 

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?

Finding Missing and duplicated Numbers
 

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?

Finding Missing and duplicated Numbers
 

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?

Finding Missing and duplicated Numbers
 

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?

.

Finding Missing and duplicated Numbers
 

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?

.