concatenate an array?

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

Aladin

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

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

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<>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?

Aladin

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

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

Aladin,
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
Column B represents exam grades

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

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)))))

Aladin

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

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

Aladin,

thank you so much.
Best wishes,
Graham