Extracting sublist from list based on criteria

Hi,

I have a table such that column A has a list of names, column B shows either "Yes" or "No" depending on if that person's info needs updating. I am trying to find a way to a populate a list (prefereably in column C) that has only the names of people from column a that show a yes in column b.

I know that i could just do =if(B2 = "Yes", A2, "") and then drag the formula down, but that leaves blanks in between the cells. If column A has 10 names and only 3 are Yes then I only want the first 3 cells in column C to be populated with the corresponding names.

I think I need to use a combination of INDEX and MATCH functions but i dont know how to do that with a criteria that falls in another column.

Any help is greatly appreciated

thanks!

Thanks for the help so far. With the solution NBVC gave i only get the first instance of a name and a "yes" repeated over the whole range. (maybe there is a problem with my implementation but maybe not) here is what i get with the previously posted solution (sorry about the formatting issues)


Name --Needs Change -- Changes Required For:
Bob ----Yes--------------Bob
Frank --No ---------------Bob
John ---Yes--------------Bob
Jerry ---No-------------- Bob
Jim ---- Yes--------------Bob


and here is what i would like

Name --Needs Change -- Changes Required For:
Bob ----Yes--------------Bob
Frank --No ---------------John
John ---Yes--------------Jim
Jerry ---No-------------- ######<-- ideally from here on would be blank
Jim ----Yes--------------

thank you so much!

could you explain why the ctrl + shift + enter works?

what i was doing before was selecting the entire range and then pressing ctrl + shift + enter (similar to the transpose function) so that was why i was only getting the first entry repeated. but why does it work to confirm the function with just the first cell in the range and then drag it down?

Originally Posted by NBVC

=IFERROR(INDEX($A$2:$A$100,SMALL(IF($B$2:$B$100="Yes",ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:$A2))),"")

Confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.... First adjust ranges to suit.

NBVC, thanks for this formula. I could use your help on a related task. I'm tring to setup a budget spreadsheet that has a main page for all transactions and monthly pages that get automatically updated to reflect transactions from that month. I tried adjusting your formula to fit and work with my spreadsheet but I can't seem to get it to work.

The best I could do was getting the correct dates and values to copy over but only at the rows that the transactions were entered in on the main sheet (see attachment).

Could you give some help? Thanks!Budget.xls

Hello rossmon, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

I thought it best to point out to a new member that it wasn't simply advice but, rather, a forum rule.

Hi sorry I know this is old post but what if have 3rd column C and it has timestamp.

so if it is b is NO then extract all in column A
if column B is yes then only extract one which is Yes.

Please see this image.
9806704853_59698497c7_o.png