# concatenate an array?

Posted by Graham Johnson on June 27, 2001 1:39 PM

Is it possible to "concatenate" the contents of a given array of cells?
Graham

Posted by Aladin Akyurek on June 27, 2001 2:17 PM

Can you give an example of short array and the expected result?

Posted by AB on June 27, 2001 2:45 PM

You know, I've always thought it was odd that CONCATENATE wouldn't accept a range (or array) reference. It's really no more useful than stringing togther a bunch of "&". Not even sure why MS bothered with it since it's such a long word to type.

Puzzled,
AaronThe Excel Logic Page

Posted by Graham Johnson on June 28, 2001 12:43 AM

Actually, all the elements of the one dimensional array except one (at maximum) will be zero. One of the cells may have one character (e.g. "A",or "E"). I want to extract the one non-blank character from the array. I am hoping to incorporate this within a CSE formula.
Graham

Posted by Aladin Akyurek on June 28, 2001 10:25 AM

Extracting the one non-blank character from an array

Graham,

Not quite sure whether the following is what you're looking for.

Lets consider the "array" that follows:

{0;0;"A";0;0;0} [ This occupies A1:A6. 0 stands for the number zero. ]

=IF(COUNTA(A1:A6)=COUNTIF(A1:A6,0),"",INDEX(A1:A6,MATCH(TRUE,INDEX(A1:A6&LT;&GT;0,0),0)))

will extract the first (single) thing that is not 0 or "the one non-blank character from the array."

Is this what you want?

=============

Posted by Graham Johnson on June 28, 2001 12:11 PM

Re: Extracting the one non-blank character from an array

thanks for staying with this one.

Your routine works, of course. However, I need to explain more fully:

suppose a1:a9 = {1;1;1;1;2;2;3;3;3}
and b1:b9 = {0;A;0;0;0;E;0;0;0} where "0" represents a blank cell.

Column A represents candidate numbers

I want to interrogate column B on three separate occasions so as to extract the exam grade for each student. I want to do it for student 1, then for student 2, then for student 3. When I said that there will only be one non-blank entry, what I meant was that there will only be one grade (maximum) per student (there may be no grade to extract ie student 3 has no grade)

I want to end up with:

1 A
2 E
3 "" (ie blank)

thanks!
Graham

Posted by Aladin Akyurek on June 28, 2001 2:29 PM

Re: Extracting the one non-blank character from an array

Graham,

Enter distinct candidate numbers in D from D1 on.

In E1 array-enter: =INDEX(\$B\$1:\$B\$9,MAX((\$A\$1:\$A\$9=D1)*(LEN(\$B\$1:\$B\$9)=1)*(ROW(\$B\$1:\$B\$9)))) [ Copy down this for all candidate numbers ]

If you don't like to see a zero appearing for candidates with no grades, in E1 array-enter instead:

=IF(ISBLANK(INDEX(\$B\$1:\$B\$9,MAX((\$A\$1:\$A\$9=D1)*(LEN(\$B\$1:\$B\$9)=1)*(ROW(\$B\$1:\$B\$9))))),"",INDEX(\$B\$1:\$B\$9,MAX((\$A\$1:\$A\$9=D1)*(LEN(\$B\$1:\$B\$9)=1)*(ROW(\$B\$1:\$B\$9)))))