+ Reply to Thread
Results 1 to 19 of 19

Formula to evaluate criteria across two matrices

  1. #1
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,078

    Re: Formula to evaluate criteria across two matrices

    Could there be more than 1 service and/or region selected?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool 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
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,982

    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.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,643

    Re: Formula to evaluate criteria across two matrices

    misread the post
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to evaluate criteria across two matrices

    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

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,643

    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. #11
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    Re: Formula to evaluate criteria across two matrices

    Looking good JohnTopley

    Thanks all!

  12. #12
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    Re: Formula to evaluate criteria across two matrices

    Cheers nflsales that works as well.

  13. #13
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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.
    Last edited by The_Snook; 06-06-2018 at 06:44 AM.

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,982

    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.
    Last edited by kvsrinivasamurthy; 06-08-2018 at 08:48 AM.

  15. #15
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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.

    Thanks in advance,

    Snook
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,982

    Re: Formula to evaluate criteria across two matrices

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

  17. #17
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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. #18
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,982

    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.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 06-28-2018 at 03:42 AM.

  19. #19
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2016 H: 365
    Posts
    737

    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
    Last edited by The_Snook; 06-28-2018 at 10:00 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 03-28-2018, 03:21 PM
  2. [SOLVED] Evaluate and return value per given criteria
    By dcourtney in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-23-2016, 07:00 PM
  3. [SOLVED] Evaluate multiple criteria with multiple results (IF / OR / AND formula)
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2016, 11:27 AM
  4. IF statement in need of improvement to evaluate more criteria
    By waternut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2016, 03:38 PM
  5. [SOLVED] Sum if range criteria is met then evaluate results and return specified value
    By jbeets in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 02:24 PM
  6. [SOLVED] Evaluate 4 criteria in a spreadsheet
    By Gorbs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-10-2015, 06:44 AM
  7. Excel: Using DSUM, I need to evaluate 2 criteria that are not toge
    By Know enough to get myself in too deep in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2006, 05:25 PM

Bookmarks

Posting Permissions

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