Array Help Req'd

Posted by Malc on April 26, 2001 12:42 AM

I have a 3 by 12 array of numbers in cells A1:C12
What formula will tell me if a number appears more than once in the array.

Posted by Dave Hawley on April 26, 2001 2:00 AM

Hi Malc

Take a look on my Website under "Handy Hints" you will see an example of how to highlight duplicates using Condintional Formatting.

...Or if you like a could whip up a quick macro to give you a detailed list ?

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on April 26, 2001 2:19 AM

Is it not simply:

=COUNTIF(A1:C12,Num) or
=IF(COUNTIF(A1:C12,Num)>1,1,0)

where 1 as second arg of IF stands for Yes and Num is the name of the cell in which the target number is.

Aladin

Posted by Dave Hawley on April 26, 2001 2:34 AM

Malc, as alternative to my Conditional Formatting method, you could select a range of equal size and enter this formula as an array in the active cell.

=IF(COUNTIF($A$1:$C$12,$A$1:$C$12)>1,A1:C12)

Dave
OzGrid Business Applications

Posted by Malc on April 27, 2001 12:35 AM

Thanks Here's what I did

So here's what I did
{=IF(COUNTIF($A$2:$C$13,$A$2:$C$13
>1,"Duplicated","Not Dup'd")}

So an array formula is a quick way of calculating a number of results for an array?

=IF(COUNTIF($A$2:$C$13,A2)>1,"Duplicated","Not Dup'd")
Copied into the same array shape does the same thing? or am I missing something

I didn't do the quick macro thing as I generated the numbers in a macro in the first place and wanted some other check to make sure of validity.

So anyone want a macro that picks 12 random numbers for each month of the year where the same number is not repeated for 3 at least 3 months. Hmmm lotto results anyone.