Array formula

[Frank Kabel]

Hi
enter the following array formula in C1 (entered with
CTRL+SHIFT+ENTER):
=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=1,ROW($A$1:$A$100)),ROW(1:1)))
and copy this down

 

[Richard Deakin]

Anyone help with a tricky Excel problem?

I have a single worksheet with two columns of data. In the first column (say
Column A) there are a series of either zeros or ones. Alongside that in the
next column there are a series of other values, e.g.

Col A Col B

Row 1 1 xxx
Row 2 0 yyy
Row 3 1 zzz
Row 4 0 bbb
Rpw 5 1 aaa

In another column I want to put a formula that will return only the string
in Column B if Column A is a one, but, here is the problem, I need the
string values immediately below each other. So if I was using the above
example and Column C had the formulae, I would want Row 1 to say xxx, Row 2
to say zzz, and Row 3 to say aaa.

Any ideas please?

Thanks
Richard