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" <Ujpest@discussions.microsoft.com> wrote in message 
news:CBCAA1E3-B5FE-4088-9575-16478B25F07F@microsoft.com...
> 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" <Ujpest@discussions.microsoft.com> wrote in message 
> news:CBCAA1E3-B5FE-4088-9575-16478B25F07F@microsoft.com...
> > 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" <Ujpest@discussions.microsoft.com> wrote in message 
news:18988CDF-A3BB-439B-9076-8C3BBE87450A@microsoft.com...
> 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" <Ujpest@discussions.microsoft.com> wrote in message
>> news:CBCAA1E3-B5FE-4088-9575-16478B25F07F@microsoft.com...
>> > 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?
>>
>> .
>>