Too many nested IF functions!

[Skyscan]

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:K4,"4")>1,"4",IF(COUNTIF(C4:K4,"5")>1,"5",IF(COUNTIF(C4:K4,"6")>1,"6",IF(COUNTIF(C4:K4,"7")>1,"7","")))))))

Thank you in advance!!!

Tom

 

[Matthew]

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:K4,"4")>1,"4",IF(COUNTIF(C4:K4,"5")>1,"5",IF(COUNTIF(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

 

[Chip Pearson]

Enter the following formula in C5 and fill across to K5.

=IF(COUNTIF($C$4:$K$4,C4)>1,C4,"")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[T. Valko]

Try this:

=INDEX(C4:K4,MATCH(TRUE,INDEX(COUNTIF(C4:K4,C4:K4)>1,1,),0))

If more than one number is duplicated the formula will return the *first*
matched duplicate.

 

[Rick Rothstein \(MVP - VB\)]

.... The numbers in C4:K4 are randomly 0 through 9, however sometimes

an error occurs and a duplicate number is inserted into the array.

I was wondering how these random numbers are being produced. It is not too
difficult to construct a macro that will produce nine numbers numbers with
no repeats... guaranteed.

Rick

 

[Skyscan]

Thanks everyone for your assistance in answering this question for me. I
apologize for not being more clear in my description of the situation. The
numbers 1-9 (another error I made in my problem description was that I wrote
0 through 9!) are entered manually in a random order, but all 9 numbers must
be represented.

The solution which Biff provided below resolved my situation completely!!!
THANKS SO MUCH!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Harlan Grove]

Try this:

=INDEX(C4:K4,MATCH(TRUE,INDEX(COUNTIF(C4:K4,C4:K4)>1,1,),0))

....

If willing to work from right to left,

=LOOKUP(2,1/(COUNTIF(C4:K4,C4:K4)>1),C4:K4)