Can I use a formula that finds the smallest number that occurs twice in a column?

[JasonK]

I have a spreadsheet with a lengthy list of numbers. I need to find
the smallest number that occurs twice. I also need to find the
smallest number that occurs three times, etc. Is there a way to do
this with a formula?

I don't mind running a macro to do this, but I would need help with
that as well. Placing the answer in a msgbox is fine with me.

thanks in advance.
jasonk

[Ardus Petus]

Try this Array formula (validate with Ctrl+Shift+Enter)
=MIN(A1:A999+IF(COUNTIF(A1:A999,A1:A999)=2,0,1E+239))

HTH
--
AP

"JasonK" <[email protected]> a écrit dans le message de
news:[email protected]...
> I have a spreadsheet with a lengthy list of numbers. I need to find
> the smallest number that occurs twice. I also need to find the
> smallest number that occurs three times, etc. Is there a way to do
> this with a formula?
>
> I don't mind running a macro to do this, but I would need help with
> that as well. Placing the answer in a msgbox is fine with me.
>
> thanks in advance.
> jasonk
>

[Bob Phillips]

=MIN(IF(COUNTIF(A1:A20,A1:A20)=2,A1:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

To change the number of occurrence, change the =2 value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JasonK" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet with a lengthy list of numbers. I need to find
> the smallest number that occurs twice. I also need to find the
> smallest number that occurs three times, etc. Is there a way to do
> this with a formula?
>
> I don't mind running a macro to do this, but I would need help with
> that as well. Placing the answer in a msgbox is fine with me.
>
> thanks in advance.
> jasonk
>

[Ardus Petus]

=MIN(999,FALSE) returns 0, but your formula works as expected!

Regards,
--
AP

[JasonK]

Both formulas worked great when i altered the range to my sheet.
thanks guys. i wish i could learn this stuff quicker.
jasonk
unreal.

On Sat, 22 Apr 2006 11:15:04 +0200, "Ardus Petus"
<[email protected]> wrote:

>Try this Array formula (validate with Ctrl+Shift+Enter)
>=MIN(A1:A999+IF(COUNTIF(A1:A999,A1:A999)=2,0,1E+239))
>
>HTH