.... or if it is intended to construct an output with "" and the digits (that
are not found),

=IF(SUMPRODUCT(--(c7:K23=1)),"",1)&IF(SUMPRODUCT(--(c7:K23=2)),"",2)
etc.

for the digits that are found:

=IF(SUMPRODUCT(--(c7:K23=1)),1,"")&IF(SUMPRODUCT(--(c7:K23=2)),2,"")
etc.

Anthony


"Anthony D" wrote:

Hi Martin,

One possibility to test for the occurrence of a number in a contiguous
range, say a 1 in c7:k23, is:

=SUMPRODUCT(--(c7:K23=1))

which counts the number of occurrences.

This can be adapted to search for any number 1 to 9 in the range:

=SUMPRODUCT(--(c7:K23=1),--(c7:K23<=9))

and the result converted to 0 or 1 for any occurrences:

=--(SUMPRODUCT(--(c7:K23=1),--(c7:K23<=9))0)

Hope this helps

Anthony

"MartinW" wrote:

Hi Group,

I need to shorten the following formula. In my example it only tests for
1 and 2, I will need it to test for all digits 1 to 9.
The target cells are a mixture of contiguous and non contiguous ranges
I could group the contiguous sections together which would shorten it a
little, however, I think my whole approach needs to be changed.

=IF(OR(C7=1, C9=1, C11=1, C13=1, C15=1, C17=1, C19=1, C21=1,
C23=1, D7=1, D9=1, D11=1, E7=1,E9=1, E11=1, F7=1, G7=1, H7=1,
I7=1, J7=1, K7=1),"",1)&IF(OR(C7=2, C9=2, C11=2, C13=2, C15=2,
C17=2, C19=2, C21=2,C23=2, D7=2, D9=2, D11=2, E7=2,E9=2,
E11=2, F7=2, G7=2, H7=2, I7=2, J7=2, K7=2),"",2)&IF(OR(C7=3,C9=3,
.........etc. etc.

Thanks
Martin