COUNTIF data counting

[joebogey]

I use the COUNTIF function to eliminate duplicate job applications.

Application serial number is in column A, each application ser# is 11 digits
Applicant SSN is in column B
Column C has formula =A1&"."&B1
Column D has formula =COUNTIF(A1:A15000,A1)

The problem that seems to have risen is that the COUNTIF function only looks
at the first 15 digits. What is happening now is we have people with similar
SSN's applying for the same job and they are being grouped together. Is there
a way to make COUNTIF look at the entire string of digits, or is there
another function that can be used?

Thanks for any help.

[Vasant Nanavati]

"joebogey" <[email protected]> wrote in message
news:[email protected]...
> Column D has formula =COUNTIF(A1:A15000,A1)

I'm assuming you mean:

=COUNTIF(C$1:C$15000,C1)

Try instead:

=SUM(--(C1=C$1:C$15000))

entered as an array formula with <Ctrl> <Shift> <Enter>.

--

Vasant

[joebogey]

I tried that and it worked perfectly. I was just wondering if you could give
an explanation of exactly how this is working, such as what the "--" does to
the formula?

Sorry, once I implement this I'll probably have to explain how it works.

Thanks for your help, I really appreciate it.
Joe

"Vasant Nanavati" wrote:

> I'm assuming you mean:
>
> =COUNTIF(C$1:C$15000,C1)
>
> Try instead:
>
> =SUM(--(C1=C$1:C$15000))
>
> entered as an array formula with <Ctrl> <Shift> <Enter>.
>
> --
>
> Vasant

[Vasant Nanavati]

The "double minus" simply converts TRUE and FALSE values to 1 and 0, so the
SUM function adds up all the TRUEs in the array; that is, it counts up the
number of matches.

--

Vasant




"joebogey" <[email protected]> wrote in message
news:[email protected]...
> I tried that and it worked perfectly. I was just wondering if you could
give
> an explanation of exactly how this is working, such as what the "--" does
to
> the formula?
>
> Sorry, once I implement this I'll probably have to explain how it works.
>
> Thanks for your help, I really appreciate it.
> Joe
>
> "Vasant Nanavati" wrote:
>
> > I'm assuming you mean:
> >
> > =COUNTIF(C$1:C$15000,C1)
> >
> > Try instead:
> >
> > =SUM(--(C1=C$1:C$15000))
> >
> > entered as an array formula with <Ctrl> <Shift> <Enter>.
> >
> > --
> >
> > Vasant