Creating a list based on yes/no column of another list

[infopull]

I have a list a people, in alphabetical order by last name, I sent an invitation to. As RSVPs come in I mark in a separate column that they are attending. On a separate sheet I would like to search the "attending" column and only include in that list the people that are attending.

I was tried; Index, If, Vlookup, etc. They work...kind of...I don't want the blank cells associated with the people not attending, inbetween the cells with information I need.

I'm sure I need to nest multiple functions in one, but I can't figure out what, or the order needed.

Another way of saying it...Search a column, find a true value and input the related information. If false, keep searching till the next true value is found.

 

[Aladin Akyurek]

I have a list a people, in alphabetical order by last name, I sent an invitation to. As RSVPs come in I mark in a separate column that they are attending. On a separate sheet I would like to search the "attending" column and only include in that list the people that are attending.

I was tried; Index, If, Vlookup, etc. They work...kind of...I don't want the blank cells associated with the people not attending, inbetween the cells with information I need.

I'm sure I need to nest multiple functions in one, but I can't figure out what, or the order needed.

Another way of saying it...Search a column, find a true value and input the related information. If false, keep searching till the next true value is found.

Did you consider using AutoFilter or Advanced Filter?

If you want to do with formulas, How did you mark attending: With Yes/No? By the way, which ranges are involved?

 

[T. Valko]

I have a list a people, in alphabetical order by last name, I sent an invitation to. As RSVPs come in I mark in a separate column that they are attending. On a separate sheet I would like to search the "attending" column and only include in that list the people that are attending.

I was tried; Index, If, Vlookup, etc. They work...kind of...I don't want the blank cells associated with the people not attending, inbetween the cells with information I need.

I'm sure I need to nest multiple functions in one, but I can't figure out what, or the order needed.

Another way of saying it...Search a column, find a true value and input the related information. If false, keep searching till the next true value is found.

So, names are in column A and column B contains either Yes, No or blank and you want to get the list of names that corresponds to Yes?

 

[T. Valko]

So, names are in column A and column B contains either Yes, No or blank and you want to get the list of names that corresponds to Yes?

Try this...

Book1
	A	B
1	Name	Status
2	Name1	No
3	Name2	Yes
4	Name3	Yes
5	Name4	Yes
6	Name5	Yes
7	Name6	No
8	Name7	_
9	Name8	_
10	Name9	Yes
Sheet1

Book1
	A	B
1	Count	Names
2	5	Name2
3	_	Name3
4	_	Name4
5	_	Name5
6	_	Name9
7	_	_
Sheet2

In the formulas I use the following defined named ranges:

• Names
• Refers to: =Sheet1!$A:$A
• Status
• Refers to: =Sheet1!$B$2:$B$10

Enter this formula on Sheet2 in cell A2. This will return the count of records that meet the condition.

=COUNTIF(Status,"yes")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)>A$2,"",INDEX(Names,SMALL(IF(Status="yes",ROW(Status)),ROWS(B$2:B2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.