Extracting Data From A List

[mfronczak]

Okay, I'm pretty good with Excel now, but there is on problem I'm having. How do I search a list (or array) for certain values, then extract those values to another list? For instance, suppose I have the following in cells A1:B7=

No A
No B
Yes C
No D
No E
Yes F
No G

And I want to end up with:
In cell C1: C
In cell C2: F

All I want to do is extract the "Yes" values and put them in a row or column one after the other, without blank cells. I also need to do this with a formula as opposed to filtering or writing a macro. Any help would be appreciated.

Mike

 

[Dave Hawley]

Hi

I know you say as "apposed to filetring", but the advanced filter would do this nicely as would a very simple Pivot Table.

 

[Mark W.]

Select cells D1:D7 and enter the array formula...

{=SMALL(IF(ISNUMBER(MATCH(A1:A7,{"Yes"},0)),ROW(A1:A7)),ROW(A1:A7))}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Next, enter the formula, =IF(ISNUMBER(D1),INDEX(B:B,D1),""), into cell C1 and Fill Down to C7.

Hide column D if desired.
This message was edited by Mark W. on 2002-03-11 09:43