Formula to evaluate criteria across two matrices

Evening all,

I've been given two matrices, one defining which services a supplier can undertake and the other which regions they operate in. I need to create a document that can be used to identify which suppliers are capable of undertaking a requirement based on the criteria selected (services and regions).

Attached is an example of the data I'm working with along with a tab showing the output required.

If you require any further info just give me a shout.

Thanks in advance,

Snook

Hi FDibbins,

Yes each requirement will contain multiple services across a number of locations.

The aim of the document is to quickly identify which supplier(s) can fully undertake a defined requirement.

Regards,

Snook

What are the likely maximum for Services. Regions and Suppliers.

And any objections to helper columns and/or VBA ?

is this correct result for selected Service & Region?

	F	G	H
10	Service	Region	Supplier
11	Service 10	Region 10	Supplier 1,Supplier 2,Supplier 3,Supplier 4,Supplier 5

Thanks kvsrinivasamurthy, unfortunately it doesn't appear to be returning the result I would expect. Based on the criteria you selected (Service 2 in Region 5 & Service 5 in Region 7) I would only expect Supplier 1 to be able to undertake that requirement as Supplier 4 don't operate in Region 5 and Supplier 5 don't operate in Region 7.

@JohnTopley

- I have to create a couple of these but the largest data set I'm working with appears to be 36 suppliers, 60 services and 74 regions.
- No objections to helper columns or VBA although I would probably prefer a formula based solution if possible? Mainly because I would like to be able to see in real time the impact of additional selections. For example if a wide range of suppliers can undertake a requirement but then you add a remote location that severely restricts the list I'd like to be able to see the impact of that. We may then choose to strip out that line and undertake it as a separate individual requirement.

Thinking on I would also like the supplier list to be blank whilst the criteria is incomplete, i.e. don't show the potential supplier list until both criteria have been selected.

@sandy666

- Yes I would expect all suppliers to be able to undertake a requirement that just related to Service 10 in Region 10. NB - I would like the output to be in a listed form in separate cells though.



Regards,

Snook

Based on kvsrinivasamurthy's reply

in M3

=IFERROR(INDEX($D$2:$H$2,SMALL(IF($D$31:$H$31=COUNTIF($B$3:$B$30,"?*"),COLUMN($D$2:$H$2)-COLUMN($D$2)+1,""),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

Cheers nflsales that works as well.

Looking good JohnTopley

Thanks all!

Afternoon all,

I'm struggling to adapt the solutions provided by nflsales and kvsrinivasamurthy to the document that I'm working on. I'd be grateful if someone could review what I've produced and identify where I'm going wrong.

- nflsales' solution doesn't appear to be returning any results.
- kvsrinivasamurthy's solution is returning some supplier names but they aren't the correct ones. They appear to be out of sync with the expected results and I'm not sure which part of the formula I need to amend to correct it. Also, when I add a second criteria it finds no matches.



NB 1: The requirement has changed in some respects compared to my original request.

1. Rather than stating the criteria as 'this service in this location' it is now a list of 'these services' in 'these locations'. Therefore the two lists will usually be differing lengths (I'm not sure that this change affects the logic of the formula though?).
2. There are 3 different supplier bases we use dependent on the value of the requirement to be undertaken. I am trying to dynamically alternate between the datasets
using the INDIRECT function.

NB 2: If it makes it any easier I've created a number of named ranges to structure it better (located on the 'Lookup' tab).

NB 3: I wasn't sure if the issue was being caused by the datasets being different sizes so I built in some 'UNUSED' data to make them the same. Ideally I'd like to remove these rows/columns from the final solution (it's not a problem if they have to stay though).



I've attached a copy of the actual document that I'm working on.

If you have any queries just give me a shout.

Thanks in advance,

Snook

Hi kvsrinivasamurthy,

The array formula you provided me with is in cell N4 (and copied down) on the 'Selector Tool' tab.

For info - The array formula provided by nflsales is in cell L4 (and copied down) on the same tab.

Regards,

Snook