Return unique values based on another column's value

Hi,

Please see attached.

In column F I have values YES or NO, and in column A, I have a list of other values - from row 1 to 20.

I want to see the 4 values in column H because the value in column F is yes. There will always be 6 rows (in this test data) and I'd like if possible the values in column H to update automatically if the value in column F changes (ie : from yes to no)

Is this possible?

Thanks in advance.

Sure can!

Put this in cell H1:

Formula:

Please Login or Register  to view this content.

And then copy it downwards.

Originally Posted by daffodil11

Sure can!

Put this in cell H1:

Formula:

Please Login or Register  to view this content.

And then copy it downwards.

Thanks for your suggestion, but that just copies yes values to the right. I'm looking for a condensed list, like in my example.

Yes, with rank, match, and index.

We assign a value to yes and no, then we rank them, somewhere else we can count the number of Yes cells, and count backwards from that number and looking up just those cells that correspond.rank-match-index-autosort.xls

Originally Posted by daffodil11

Yes, with rank, match, and index.

We assign a value to yes and no, then we rank them, somewhere else we can count the number of Yes cells, and count backwards from that number and looking up just those cells that correspond.Attachment 252922

That's pretty close -thank you. However, in the results column O, there is the value dog, where I would expect to see mouse. Any ideas why this may be?

It's ordering them of no particular value. I'm not sure how I would preserve the original order using rank.

I will think upon it.

Originally Posted by daffodil11

It's ordering them of no particular value. I'm not sure how I would preserve the original order using rank.

I will think upon it.

Excellent, I'll keep an eye on the thread.

It's a shame Excel doesn't have a Filter function similar to Google Docs.

I'll create a macro filter for you. I use them on our Excel based knowledge bases. Give me 15 minutes.

Enter a word to search A1:F6. Click Filter.

pets-filtermacro.xlsm

Originally Posted by daffodil11

Enter a word to search A1:F6. Click Filter.

Attachment 252954

Thanks for your help.