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?


0




Reply

Utf

3/1/2010 12:51:01 PM 

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?


0




Reply

Utf

3/1/2010 1:05:01 PM


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:CBCAA1E3B5FE4088957516478B25F07F@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?


0




Reply

Bernard

3/1/2010 1:27:25 PM


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:CBCAA1E3B5FE4088957516478B25F07F@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?
>
> .
>


0




Reply

Utf

3/1/2010 6:41:01 PM


Thanks for feedback
Bernard
"Ujpest" <Ujpest@discussions.microsoft.com> wrote in message
news:18988CDFA3BB439B90768C3BBE87450A@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:CBCAA1E3B5FE4088957516478B25F07F@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?
>>
>> .
>>


0




Reply

Bernard

3/2/2010 8:10:09 PM



4 Replies
803 Views
(page loaded in 0.006 seconds)
