CSE Array question

[HWL]

Hi there, supposed I have in col A, a bunch of "X" but not in sequence -- so perhaps an "X" in A2, then in A8, then in A12.

In col B I have some data too.

What I want is to be able to in col C, have a formula I can drag down that will look at col A an if there is an "X", bring the data from col B into col C, but in sequence. So column C would have data in C1-C5 assuming there are 5 "X" in col A.

I know this requires a ctrl+shift+enter array but not sure how to make it work.
Thanks

 

[Matty]

What version of Excel are you using?

Matty

 

[HWL]

2007, yet the code should be simple enough to run even in OO.

 

[Matty]

2007 and beyond:

=IFERROR(INDEX(B$2:B$21,SMALL(IF(A$2:A$21="X",ROW(A$2:A$21)-ROW(A$2)+1),ROWS(C$2:C2))),"")

Pre 2007:

=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$21,"X"),INDEX(B$2:B$21,SMALL(IF(A$2:A$21="X",ROW(A$2:A$21)-ROW(A$2)+1),ROWS(C$2:C2))),"")

Both of which require committing with CTRL+SHIFT+ENTER.

Matty

 

[HWL]

2007 and beyond:

=IFERROR(INDEX(B$2:B$21,SMALL(IF(A$2:A$21="X",ROW(A$2:A$21)-ROW(A$2)+1),ROWS(C$2:C2))),"")

Pre 2007:

=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$21,"X"),INDEX(B$2:B$21,SMALL(IF(A$2:A$21="X",ROW(A$2:A$21)-ROW(A$2)+1),ROWS(C$2:C2))),"")

Both of which require committing with CTRL+SHIFT+ENTER.

Matty

Excel-lent! works exactly how I imagined. Thanks much.

 

[Matty]

Excel-lent! works exactly how I imagined. Thanks much.

Glad it worked. Happy to help.

Matty

 

[HWL]

Re: CSE Array question **Alphabetize**

The following code will work to list entries in range b3:b16 in ordinal format (be it letters or numbers - must be one or the other), however if there is formula in b3:b16 that returns a blank it is not considering it as blank and is causing the following code to break:

=IF(ISERROR(INDEX($B$3:$B$16, MATCH(SMALL(IF(ISBLANK($B$3:$B$16), "", COUNTIF($B$3:$B$16, "<"&$B$3:$B$16)), ROW(1:1)), IF(ISBLANK($B$3:$B$16), "", COUNTIF($B$3:$B$16, "<"&$B$3:$B$16)), 0))),"",INDEX($B$3:$B$16, MATCH(SMALL(IF(ISBLANK($B$3:$B$16), "", COUNTIF($B$3:$B$16, "<"&$B$3:$B$16)), ROW(1:1)), IF(ISBLANK($B$3:$B$16), "", COUNTIF($B$3:$B$16, "<"&$B$3:$B$16)), 0)))

The problem is, ISBLANK($b$3:$b$16) is counting a ="" as not blank.


Any ideas how to ignore blank code just as I am ignoring actual banks?

 

[Matty]

Could you post an example of what you want the formula to do?

Posting existing formulas that don't deliver what's required rarely helps - better to show things in a simple illustration.

Matty

 

[pgc01]

Re: CSE Array question **Alphabetize**

... however if there is formula in b3:b16 that returns a blank it is not considering it as blank

IsBlank() only returns true if the cell is empty, which is not the case as you say the cell has a formula.

Instead of

ISBLANK($b$3:$b$16)

that only returns true is the cell is empty

use

$b$3:$b$16=""

This returns true both if the cell is empty or the cell contains a null string, as in your case your formula returns.

 

[pgc01]

P. S. it's better to avoid the word blank in excel, as it is ambiguous.

Check my post on this subject here:

http://www.mrexcel.com/forum/showthread.php?p=2660059#10