Automatically show multiple results for a table search

[Ana Wisbeck]

Hello!
I have a table in excel at which one column lists names while another column display YES or NO depending on whether a certain criteria is met.
Here's an example:

Column A Column B
2 HK YES
23 HK NO
2318 HK NO
2388 HK NO
2600 HK NO

I would like to display a results table that only lists the names in column A that display YES on column B. Here are the complications: the data will be updated daily and therefore the number of results will change and is unkown.
The goal is to automate the process so that sorting and copying or dragging down formulas and deleting N/A cells will not completely fulfill my task.

Please note that my actual table has about 400 names and the results can vary from zero to 90.

Any help is very much appreacited. I can use Excel formula or VBA, whichever will be more convenient.

Thanks in advance,
Ana Wisbeck

 

[WinteE]

Hi Ana,

Is it possible to switch column A and B, first YES/NO and then the names ?
If so then you can use the =ARLOOKUP() function from my www.excelguide.eu.
Excel, Lookup, Lookup several values.
This function will represent the values like "2 HK, 23 HK, ..." (example).

Success

 

[Ana Wisbeck]

Thanks for the quick reply!
What a beauty, very elegant solution and does what I need.

One final question if you allow me:
if I have 2 spreadsheets that need to have the same sorting done, can I save your code on the Personal.xslb folder to use it in all sheets or do I need to save it as a project under each worksheet?

Thank you very much!

Ana Wisbeck

 

[WinteE]

Hi Ana,

This depends on whether the spreadsheets will be used on other computers or not.

In my opinion it is most save to add the code to the spreadsheet itself. This allows you to use it on other computers and to send it to others without errors.

Erik

 

[Ana Wisbeck]

Thanks Eric, that's a very good point!
One extra step of adding the code to the other sheet might save me a lot of trouble in the future.

Thanks again, you saved me on this one. Question definitelly answered.

Ana