MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?

Aladin

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

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

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

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


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

Aladin

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


Posted by Graham Johnson on June 29, 2001 4:52 AM

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

Aladin,

thank you so much.
Best wishes,
Graham