# Formula to evaluate criteria across two matrices

1. ## 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.

Snook

2. ## Re: Formula to evaluate criteria across two matrices

Could there be more than 1 service and/or region selected?

3. ## Re: Formula to evaluate criteria across two matrices

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

4. ## Re: Formula to evaluate criteria across two matrices

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

And any objections to helper columns and/or VBA ?

5. ## Re: Formula to evaluate criteria across two matrices

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

6. ## Re: Formula to evaluate criteria across two matrices

ARRAY formula is used in M column. Formula should be confirmed With Ctrl+Shift+Enter Keys together
Column D to H are helper columns(Depending upon total suppliers)
In D3 then drag across
``Please Login or Register  to view this content.``
ARRAY formula in M3 then drag down
``Please Login or Register  to view this content.``

7. ## Re: Formula to evaluate criteria across two matrices

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

9. ## Re: Formula to evaluate criteria across two matrices

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

10. ## Re: Formula to evaluate criteria across two matrices

Try below array formula in H3 and copy towards down
``Please Login or Register  to view this content.``
...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.

11. ## Re: Formula to evaluate criteria across two matrices

Looking good JohnTopley

Thanks all!

12. ## Re: Formula to evaluate criteria across two matrices

Cheers nflsales that works as well.

13. ## Re: Formula to evaluate criteria across two matrices

Hmmm it won't let me leave you any rep points nflsales, apparently I need to share it round first.

I'll pop back and try again later......

Rep sorted.

14. ## Re: Formula to evaluate criteria across two matrices

This ARRAY formula does not require helper columns
In J3 then drag down
``Please Login or Register  to view this content.``

15. ## Array formula to evaluate criteria across two matrices

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.

Snook

16. ## Re: Formula to evaluate criteria across two matrices

I did not find where the ARRAY formula applied. Pl clarify in detail.

17. ## Re: Formula to evaluate criteria across two matrices

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

18. ## Re: Formula to evaluate criteria across two matrices

Formula is modified to suit to Data. Ranges are directly entered.Change them by range names suitably.
ARRAY formula in N4 then drag down
``Please Login or Register  to view this content.``

19. ## Re: Formula to evaluate criteria across two matrices

Massive thanks kvsrinivasamurthy, I'll give it a try now and will let you know how I get on.

Update - It works a treat!

Regards,

Snook

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1