Array Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chad - 07 Mar 2007 19:50 GMT

I would like to create an array that is built off of criteria that I specify.
For example, if column A contains numbers and column B contains letters, I
would like to create a formula that would return all of the letters
associated with number 12. Any help would be greatly appreciated.

Thanks in advance,
Chad

Reply to this Message

T. Valko - 07 Mar 2007 20:02 GMT

Try this:

Asume numbers in A1:A10
Letters in B1:B10
D1 = number of interest = 12

Enter this formula in E1 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ROWS($1:1)<=COUNTIF(A$1:A$10,D$1),INDEX(B$1:B$10,SMALL(IF(A$1:A$10=D$1,ROW(A$1:A$10)-MIN(ROW(A$1:A$10))+1),ROWS($1:1))),"")

Copy down until you get blanks

Biff

>I would like to create an array that is built off of criteria that I
>specify.
[quoted text clipped - 4 lines]
> Thanks in advance,
> Chad

Reply to this Message

Chad - 07 Mar 2007 20:25 GMT

Biff,
Thanks for your quick response but when I drag the formula down, itis
still just returning the first record that meets my requirements (in your
example it would be that the number would be 12). Additionally I am not sure
if that is going to return what I am looking for: I would like to return all
of the data found in column B that matches my criteria in one cell (as an
array). Here is what I have been trying to use but has not been working:

{=INDEX($B$1:$B$8,MATCH(1,($A$1:$A$8=2),0))}

Any suggestions?

Chad

> Try this:
>
[quoted text clipped - 19 lines]
> > Thanks in advance,
> > Chad

Reply to this Message

T. Valko - 07 Mar 2007 21:24 GMT

>when I drag the formula down, itis still just returning
>the first record that meets my requirements

It sounds like you have calculation set to manual. Set calculation to
automatic.

>I would like to return all of the data found....in one cell

Depending on how many cells meet the criteria you could use the formula I
suggested and then concatenate those results into a single cell.

For example:

The array formula results are in E1:E5

Then use this formula to concatenate those results:

=E1&","&E2&","&E3&","&E4&","&E5

If that's not acceptable the only other option is a VBA procedure. I can't
help you with that.

Biff

> Biff,
> Thanks for your quick response but when I drag the formula down, itis
[quoted text clipped - 36 lines]
>> > Thanks in advance,
>> > Chad

Reply to this Message