MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Finding Missing and duplicated Numbers

• Follow

```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

```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

```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?

```
 0

```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?
>
> .
>
```
 0

```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?
>>
>> .
>>
```
 0

4 Replies
674 Views

12/3/2013 4:18:03 PM

8/3/2012 3:14:19 PM