Excel Array Conversion from 2-Dimensional to One

[tkt_tang]

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.

 

[Glenn Ray]

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

 

[Alan Beban]

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.

I'm not sure what you mean by "D1=(1,2,2;1 . . .)" In fact, if the
formula is array entered into D1:F5 then D1=1, E1=2, F1=2, D2=1, E2=1,
F2=1, etc., through F5; i.e., D1:F5 (not D1) contains the
two-dimensional array you describe.

If instead you want the corresponding "one-dimensional" array (it will
in fact be two-dimensional if transferred to VBA) in D1:R1, then if the
functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook then either
of the following, array entered into D1:R1, will produce the result:

=MakeArray(COUNTIF(A1:C5,A1:C5),1)

=ArrayReshape(COUNTIF(A1:C5,A1:C5),1,15)

Alan Beban

 

[tkt_tang]

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.

 

[tkt_tang]

Will study your suggestion. Thank you.