Extracting Data From A List

mfronczak · Mar 11th, 2002, 09:47 AM

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 · Mar 11th, 2002, 09:56 AM

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. · Mar 11th, 2002, 10:37 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 09:47 AM	#1
mfronczak New Member Join Date: Mar 2002 Posts: 9	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

Mar 11th, 2002, 09:56 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi I know you say as "apposed to filetring", but the advanced filter would do this nicely as would a very simple Pivot Table.

Mar 11th, 2002, 10:37 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]