Formula to return supplier names that meet certain criteria

Morning all,

I'm trying to develop a tool for use by customers to ascertain which suppliers can provide services in their area(s) based on criteria they select. I'm using 'advanced filters' to extract the relevant data. From that I need to produce a list of suppliers that can fulfill all of the criteria (advanced filters return each criteria match, not as a package as a whole).

The attached example hopefully demonstrates what I'm looking to achieve.

Thanks in advance,

Snook

Hi Snook

in your file, wy does supplier 1 not match all criteria ?


cheers
Leo

Thanks all, I don't think I'm too far off now.

I've updated the attached (nflsales thanks for that very impressive formula!) and now I just need a formula for column O
to generate the supplier list (@ kev_ A a filtered list isn't a desirable solution. I need to produce something where the customer completes the 'selector' area and the
supplier list is generated based on that, i.e. dummy proof).

I don't think the customers will see the results area in the final product (because it contains info for suppliers
that don't meet all the criteria).

Cheers,

Snook

Originally Posted by LeoTaxi

Hi Snook

in your file, wy does supplier 1 not match all criteria ?


cheers
Leo

Hi Leo,

If you look at the 'data' tab on row 14 supplier 1 doesn't meet the 'Y/N' criteria.

Regards,

Snook

Hi avk,

Thanks for your solution but I'm afraid you've misunderstood my query. What I'm looking to achieve is to list all the suppliers in cell O4 and below that meet all the criteria listed in B5:E9. In the example I gave this would only apply to 'supplier 3'. I'm not looking to list all the data against a certain supplier which is what I think you have provided me with?

Regards,

Snook

Morning all,

Apologies for the confusion I clearly didn't explain my requirement sufficiently.

I think I've solved the issue around returning the supplier list, I managed to find a similar query and adapted it to my situation. I'd be grateful if someone
could sanity check it though (cells P4 to P9).

The final piece of the puzzle is to extract the lines for the supplier(s) that can fully undertake the requirement, i.e. meet all the criteria and place them into a
results table.

I've amended the attached spreadsheet to hopefully make it a bit clearer as to what I'm trying to achieve.

NB - The structure has changed so any cell references in previous posts will be slightly out of sync.

Thanks to everyone that has helped me so far, it's really appreciated!

Snook

With code after button

Please Login or Register  to view this content.

Kind regards
Leo

Hi nflsales,

Apologies I missed your post. I've just tried it and it doesn't appear to do what I need (your original post does though, #3). This is because you are using the 'Supplier(s)' column as the source of one of your inputs when the data in this column will be variable based on the data contained in the 'Results' table (effectively creating a circular reference). If you take a look at the latest version I have uploaded (v3) you will see how your original formula works (in the 'Matches' table) and the supplier column (P) based on it.

The data flow should work as follows:

1. The customer selects the various work (criteria) that it needs undertaking in the 'Selector' table.
2. The 'Matches' table then identifies all the instances in which those criteria are met by the various suppliers.
3. The 'Supplier(s)' column then identifies which suppliers can undertake the whole requirement, i.e. meets all the criteria.
4. The 'Results' table then uses the suppliers identified in the 'Supplier(s)' column to extract the relevant rows from the 'Matches' table. The 'Results' table should only show the prices of those suppliers that can undertake the whole requirement.

I hope this makes sense?

Thanks for your patience, all this is mashing my head!

Snook

Hi Leo,

Thanks for this it works perfectly. However, is it possible to achieve the same outcome without the use of VBA? The problem I've got is that this tool will be used by our external customers and I suspect I'll be bombarded with queries when they can't get it to work on their computer (I've been down that road before and it's not good times! )

Snook

Me not very good with formulas so this 1 with a few helper columns
starting from column Y
maybe 1 of the specialists can reduce the helper columns


Kind regards
Leo

Morning all,

Apologies for the delayed response I've been on leave.

For completeness I'm there now and have uploaded the final workbook.

LeoTaxi - Thanks for your solution. I knew there must've been a way to do it without the need for the helper cells so I've been reading various articles and I eventually managed to solve it.

Thanks for everyone's assistance, as always it's massively appreciated.

Snook