On 4 Jul, 18:20, Skyscan wrote:
I am trying to determine if a data array has duplicate numbers. The numbers
in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a
duplicate number is inserted into the array. If a duplicate number is
entered, I want the duplicate number to show up in the cell. I am running up
against the max limit of 7 nested IF() functions. The function string below
works, however I need to check for duplicate 8's and 9's. How can I get
around this nested IF limit?

=IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") ))))))

Thank you in advance!!!

Tom

Tom,

Trying to understand. You need 9 random numbers with no duplicates
chosen from whole integers between 1 and 9 but to also include all
numbers ?

Rather than constructing a nested if statement why not select 1 number
from 1 to 9 at random ? If you need more than one selection then if it
needs to be random you will need repeats or else you will end up with
a probability of 1:9 of getting any number and this would not be
random.

If you gave some more context I may be able to help better.

Matthew