SOLVED

match two criteria and return multiple records in a list

Copper Contributor

I have a problem set where I have a table of three columns. I need all the values in Column A that meet my selected criteria from both Column B and Column C to be returned in a format that I can put into Data Validation in list form. I've tried a variety of different way but this problem is just beyond me 

15 Replies

@amAnnunz 

 

If you have a spreadsheet created, even if it's not functional yet, it would help us visualize what you're trying to describe if we could see it. But you should expect that we might come back and ask for a more complete description of what you are actually trying to do.

 

So it would help if in addition to posting your spreadsheet as it is, if you could also give an example or two of what your sentence (in italics below) means in practice. That is, show what the values in A might be, both the selected and the unselected; are those values in column A in the form of words, numbers, codes, etc.? What are the entries in Col B and C that serve as criteria? How would the extracted values be used (together, in combination, or as individual entries in a data validation list?....

 

I need all the values in Column A that meet my selected criteria from both Column B and Column C to be returned in a format that I can put into Data Validation in list form.

@mathetes 

 

My table is $A$2:$C$25. I have 2 dropdowns in cell locations F2 and F3 that correspond to B2:B35 and C2:C35, respectively. What I want is a formula that takes both of those criteria from F2 and F3 into account and creates a new drop down list in cell F5 that pulls all the options in A2:A35 that meet both of those criteria.

 

I hope I'm being clear.

best response confirmed by amAnnunz (Copper Contributor)
Solution

@amAnnunz 

 

FILTER did it. Here's the result. The formula is:

=FILTER(A2:A35,(B2:B35=F2)*(C2:C35=F3),"No match")

@mathetes 

 

Thank you. That accomplished part of what I was saying but the formula doesn't work when copied in data validation to have a list just in cell F5. I didn't make that clear the first time and I apologize. Any thoughts on that?

@amAnnunz 

 

The list wouldn't be just in cell F5, it would be F5:F10 or whatever column length you need to accommodate the longest list. 

 

We could write some formulas to make the dimensions of a named range change according to what's there........ I'm sorry, but I can't devote more time to it until later tomorrow; maybe someone else can...but in the meantime you could just designate a range of cells sufficient to accommodate the longest possible list, as noted above.

@amAnnunz I believe the attached file ( _rev ) contains what you ask for. First create the filter as @mathetes described. Then link the data validation source to the cell that holds the filter formula and add the hash-sign (i.e. #).

Screenshot 2020-04-12 at 06.18.05.png

This will create a drop-down that follows whatever the FILTER function comes up with.

In the second version of the file ( _ver2 ), I took it one step further by making the drop-downs dependant on the Functions and Modes that are included in the table (changed it to a structured table though). That way, you don't need the named ranges anymore.

@Riny_van_Eekelen 

 

I had actually already done that but I didn't fully explain why it wouldn't work for me. The example I sent you is only one of many. I need the original selection (i.e. the results from the two criteria) to be in a single, contained cell. It's hard to explain exactly why but that's what i need. So even though the list is in a single, contained cell, using the FILTER function initially still requires more than one cell. 

 

Any other help would be much appreciated.

@Riny_van_Eekelen 

 

Thank you for finishing that off, Riny.

 

I had wondered whether the FILTER function could be put into the Data Validation dialog box directly. Just tried it and it didn't work.

 

That hastag after the cell reference in the way you resolved it is good to know about.

 

I keep learning and appreciating how much Excel has added over the last 18 years since I retired ..

@amAnnunz If it's hard to explain what you need, can you perhaps make an example without any formula or actual drow-down how it should look like. Whatever it is.

Or perhaps, upload you real workbook (stripped of any confidential information) so that we can understand what you are talking about.

@mathetes Not finished yet, I believe.

@mathetes @Riny_van_Eekelen 

 

It's not exactly what I was looking for but it's a really good option and I'm going to make it work, at least for now. Thank you both very much!

@amAnnunz 

 

well, I'm sincerely hoping you can explain why you wanted it this way, but in the attached I've taken all the work done earlier in this chain and created a way for your filtered result to all appear in one cell.

 

Essentially it involved three steps, and you'll see them in the highlighted cells in the attached.

TRANSPOSE the results of the FILTER, which leaves everything in adjacent cells

CONCATENATE the results of the transposition

Then use LEFT and LEN and FIND to trim off the trailing zeroes in the concatenate.

 

These would need to be adjusted for a set longer than what I provided for, but it works I think with all your combinations.

 

NOW Please tell us why you need it this way.

@mathetes 

 

The short answer to why I need it completely self contained in one drop down list is because I'm duplicating this process hundreds of times in hundreds of cells. Honestly, the answer you and the other gentleman came up with before is a really good option for me. It's going to take more legwork than I initially wanted but Data Validation just doesn't play nice with Table information.

@amAnnunz 

 

You wrote: Data Validation just doesn't play nice with Table information.

 

Which surprised me. My impression is that Data Validation DOES work well with tables (as lists). Then when you add the way we were able to come up with FILTER to create a  dynamic list based on two (and it could be even more) prior drop down (Data Validation) lists.... it seems to me this is a fantastic capability,

 

I've seen other ways to have Data Validation refer to secondary tables (e.g., having a formula refer to a named range), but this is a step ahead, because not only is it a secondary (or tertiary) table, but it's dynamic. You could extend the original or basic database and have it automatically accounted for.

 

Anyway, thanks for presenting the challenge. It's always fun to discover new (buried) capabilities within Excel.

@amAnnunz 

 

Wait, I just missed something you said here in your last message. You've been talking of having the results of the dual selection--that third set of exercises that meet both--as present in one cell, stretched out horizontally, but you've also referred, right from the beginning, to having them in a drop down as a third Data Validation item....and that would appear in one cell.

 

So when you wrote this:

"The short answer to why I need it completely self contained in one drop down list is because I'm duplicating this process hundreds of times in hundreds of cells"

something clicked with me. Although I can't imagine the application where you'd have this DataValidation process, especially with each of them two or three levels deep, running to hundreds of cells, that's your business and I can see that you know what you're doing. What I'm wondering, though, since the solution Riny and I were able to give you DOES give you that third level drop-down dynamic selection you wanted, is whether you're worrying about the screen getting cluttered with all the "real estate" needed to accomplish something like this.

 

If that's the case, then surely you realize you can put all the "sausage making" aspect of it backstage, on a separate, and even a separate AND hidden spreadsheet of its own. What the user sees could be very clean, and all those three drop-downs can be adjacent to one another on one row, or adjacent to one another in one column...or adjacent to one another in some other aesthetically appealing and functional layout.... Nobody needs to see the naked lists themselves.

 

And I do realize you may be fully aware of that....I'm writing partly for the sake of other people who may come across this (public) thread, facing similar situations (though hopefully not in the hundreds) who may not have thought of using separate/hidden sheets for the working of the magic.

 

 

1 best response

Accepted Solutions
best response confirmed by amAnnunz (Copper Contributor)
Solution

@amAnnunz 

 

FILTER did it. Here's the result. The formula is:

=FILTER(A2:A35,(B2:B35=F2)*(C2:C35=F3),"No match")

View solution in original post