Use Excel to populate Text answers values from a specific column based on specific criteria in other columns

Question

I have a data set with 1000s of rows worth of data

I am looking to create an excel formula that gives me the Column 'A' text values based on the following :

• Column 'D' value Must be 'Winter'
• Column 'E' value Must be 'New'
• Column 'F' value Must be 'West'
• Column 'G' Value must be '2020'

__________

Column 'C' has values ranging from 501 to 550.  I want to separate the answers above based on the Column 'C' values - 501-550

I want the results to show up on a new tab in the excel workbook

There are most likely multiple Column 'A' answers for this criteria.  I want all of the column 'A' answers to populated on different cells within the same row or column.

Also, is it possible to eliminate duplicate answers?  For example, if 'John Smith' is listed twice in column 'A' where the column 'C' value is 503, I only want John Smith to be listed once.

THis is a screenshot of the data tab

This is a screenshot of what I want the results tab to look like:  The cell in B5 and below would automatically populate based on the dropdown selection in B3.  

Many thanks!

Answer 1

Hi

I choose to create a macro using "the advanced filter" to be called by a button

Click the link below to access the copy of your file with the answer

https://www.dropbox.com/s/9pg8h3crbi7qd9m/excel%20example%20answer.xlsm?dl=0

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hi

POWER QUERY is the best and more dynamic solution to your request.

Kindly request posting a link to a copy of your workbook so the experts in this forum could access it and help you.

Regards

Jeovany

Answer 3

Excel Example.xlsx

Answer 4

Hi,

I am not sure of what you want but try this formula in cell B5 of the Analysis worksheet

=UNIQUE(FILTER(Data!A4:A323,((Data!C4:C323=Analysis!B3)*(Data!D4:D323="Fall"))))

Hope this helps.