Excel Array Conversion from 2-Dimensional to One
 

Re : Excel Array Conversion from 2-Dimensional to One

1. Enter an Excel worksheet.

2. Enter into the range A1 : C1 = A, C, E.
3. Enter into the range A2 : C2 = B, U, T.
4. Enter into the range A3 : C3 = C, O, P.
5. Enter into the range A4 : C4 = D, I, G.
6. Enter into the range A5 : C5 = E, L, F.

7. Enter a formula into cell D1 = COUNTIF(A1:C5,A1:C5).

8. Partial evaluation of the formula in the formula bar shows that cell
D1 ={1,2,2;1,1,1;2,1,1;1,1,1;2,1,1} (i.e., 2-dimensional array).

9. Now, the question is, how to transform the formula in cell D1 such
that it would evaluate to D1 = {1;2;2;1;1;1;2;1;1;1;1;1;2;1;1} (i.e.,
One-dimensional array).

10. Please share your comment.

Excel Array Conversion from 2-Dimensional to One
 

You'll need to clarify step 7. If one enters in cell D1:
=COUNTIF(A1:C5,A1:C5)
the result is zero.

If, however, one creates a two-dimensional array with the upper-left corner
in D1 such that the formulas are like so:
D1 = COUNTIF($A$1:$C$5,A1)
D2 = COUNTIF($A$1:$C$5,A2)
D3 = COUNTIF($A$1:$C$5,A3)
.....and so on until
F5 = COUNTIF($A$1:$C$5,C5)

then one returns a two-dimensional array meeting your description
(i.e, {1,2,2;1,1,1;2,1,1;1,1,1;2,1,1})

From there, the dirtiest solution would be to concatenate the values
together to return a fifteen character string: 122111211111211 (with comma
separators optional).

Again, please clarify step 7. Also, if the source array varies in size, a
programmatic solution may be best.

Glenn Ray
MOS Expert

" wrote:

Re : Excel Array Conversion from 2-Dimensional to One

1. Enter an Excel worksheet.

2. Enter into the range A1 : C1 = A, C, E.
3. Enter into the range A2 : C2 = B, U, T.
4. Enter into the range A3 : C3 = C, O, P.
5. Enter into the range A4 : C4 = D, I, G.
6. Enter into the range A5 : C5 = E, L, F.

7. Enter a formula into cell D1 = COUNTIF(A1:C5,A1:C5).

8. Partial evaluation of the formula in the formula bar shows that cell
D1 ={1,2,2;1,1,1;2,1,1;1,1,1;2,1,1} (i.e., 2-dimensional array).

9. Now, the question is, how to transform the formula in cell D1 such
that it would evaluate to D1 = {1;2;2;1;1;1;2;1;1;1;1;1;2;1;1} (i.e.,
One-dimensional array).

10. Please share your comment.

Excel Array Conversion from 2-Dimensional to One
 

Excel Array Conversion from 2-Dimensional to One
 

Thank you for your reply.

7. To rephrase step 7 ; Enter a formula into cell D1 =
COUNTIF(A1:C5,A1:C5) ; to be Array-Entered, then a value 1 is
instantantly visible in cell D1.

D1 = COUNTIF(A1:C5,A1:C5) and D1 {= COUNTIF(A1:C5,A1:C5)} evaluate
similarly in the formula bar (by virtue of partial evaluation after
selecting either form of the formula at the equal sign and move to the
right end).

Regards.

Excel Array Conversion from 2-Dimensional to One
 

Will study your suggestion. Thank you.