Excel: Return Multiple Rows with No Row Gaps (combo box)

Hello,

Not sure if this is possible.

I have the following dataset on two separate rows. The first column is a combination box.

[yes/no]      Sentence 1
[yes/no]      Sentence 2
[yes/no]      Sentence 3
[yes/no]      Sentence 4

I have a further 4 rows where I would like to display only the sentences with [yes] next to them. I would like there to be no row gaps between the sentences. E.g:

Sentence 1
Sentence 3

(above example is if [yes] = sentence 1 & 3 - note there is no 'blank row' between the sentences, they appear one row after the other)

Hopefully I explained this ok.

What's the best way of getting this to work?

Thanks.
LVL 1
dabug80Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Use the following array-entered formula, entered in cell F10 in this example:
=IFERROR(INDEX(B$1:B$4,SMALL(IF(A$1:A$4="Yes",ROW(A$1:A$4)-ROW(A$1)+1,""),ROWS(F$10:F10))),"")

If you put the formula in a different cell, then change the reference to F$10:F10 accordingly. As you copy the formula down ROWS(F$10:F10) will return the series 1, 2, 3, etc.

To array-enter a formula:
1.  Click in the formula bar
2.  Hold the Control and Shift keys down
3.  Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding your formula. If you don't see them (or if the formula returns an empty string (looks like a blank), then repeat steps 1 to 3.

You may copy the array-entered formula down. When the list of Yes answers is exhausted, the formula returns an empty string.


If you want to use the results in a data validation dropdown, use a dynamic named range like:
=$F$10:INDEX($F$10:$F$100,COUNTIF($F$10:$F$100,"?*"))
This formula will count the sentences, but ignore the empty strings.
0
 
dabug80Author Commented:
Wow! That is an excellent solution. You solved it. And to think I thought it couldn't be done.

Thanks so much for your solution
0
 
dabug80Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.