Result based on criteria fro 2 lists
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Result based on criteria fro 2 lists

  1. #1
    Board Regular
    Join Date
    May 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Result based on criteria fro 2 lists

    Please can someone advise me the best way to go around this scenario. To help ease calculations for a mileage report I want to put together.

    I am thinking I will have 2 lists that generate a result (you maybe able to advise a better way?) – a list of clients (going from) and a list of clients (going to) – someone will have worked out what that mileage is and enter that in ‘result’ (behind the scenes)

    Drop down lists - select:
    Client a Client b result = 5 miles
    Client a client c result = 7 miles

    The user will then have two drop downs 1) client from and 2) client to - then the result will appear.

    Thank you

    Marc

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,065
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    So 3 clients labelled A, B and C

    Create a table consisting of 3 columns
    Client From, Client To, and Distance

    Then just do a VLOOKUP()/INDEX(MATCH(...)) based on the From and To
    So

    Code:
    A   B   5miles
    A   C   7miles
    B   C   10miles
    If you place the Client From/ To in alphabetical order (irrespective of if the from/to) you can do away with duplicate rows in the table since the distance from A to B would be the same as B. to A. For example A B C is 6 combinations AtoB AtoC BtoC BtoA BtoC CtoA. but the last 3 of these are the same as the first 3 for distance so you could do away with the last 3 rows of the table and just place the Clients in alpahabetical order, ie for BtoA just change it to AtoB and lookup AtoB.
    Last edited by Special-K99; Aug 16th, 2019 at 08:41 AM.

  3. #3
    Board Regular
    Join Date
    May 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    Thank you for your reply - based on you advice above - can Client A and Client B be indifferent columns because if the list is long it will be mush easier for them to choose

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,065
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    Yes. Client A and B in different columns with a mileage, hence the 3 columns
    Im just saying why have this

    A to B 5 miles
    A to C 7 miles
    B to C 10 miles
    B to A 5 miles (we know this already from above)
    C to A 7 miles (we know this already from above)
    C to B 10 miles (we know this already from above)

    when you just need this

    A to B 5 miles
    A to C 7 miles
    B to C 10 miles

    You can just set a formula to say if its "B to A" in the formula just check if "From" is greater than "To" just swap them around and you only need to lookup 3 rows instead of 6, which if your saying the list of clients is long you can halve the length of the list as displayed int he tables above. No need to duplicate distances that have already been entered.

  5. #5
    Board Regular
    Join Date
    May 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    thank you that's great - if the data is in table 1 on sheet 2 columns A,B, C from row - any chance you could give me the exact formula =VLOOKUP()/INDEX(MATCH(...))?

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,065
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    I might have overdone this but try

    in Sheet2!A1:C3 put

    Code:
    A B 5
    A C 7
    B C 10
    in Sheet1!C1 put
    =INDEX(Sheet2!C$1:$C$3,AGGREGATE(15,6,ROW($A$1:$C$3)/(((IF(B1 < A1,B1,A1)=Sheet2!A$1:A$3)*(IF(A1 < B1,B1,A1)=Sheet2!B$1:B$3))),ROWS(A$1:A1))-(1-1),1)

    Put A in Sheet1!A1
    Put B in Sheet1!B1
    Result is 5

    Now Swap A1 and B1 around, result is still 5 so saves you having to duplicate distances in the table, so you only need to put AtoB, not BtoA as well.
    The highlighted part of the formula swaps the Client names round if necessary.
    Last edited by Special-K99; Aug 16th, 2019 at 09:39 PM.

  7. #7
    Board Regular
    Join Date
    May 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    Thank you for this - This works really well - however it only appears to work on the first 2 rows of the table in sheet 2?

  8. #8
    Board Regular
    Join Date
    May 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    Also if I drag the drop downs and formula down on sheet 1 - if only works on row 1

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    Try the following formulas
    You can use sumproduct or sumifs

    Sheet1
     ABCD
    1Client FromClient ToMileageMileage
    2AB55
    3AC77
    4CA77
    5BA55
    6BC1010

    CellFormula
    C2=SUMPRODUCT(((Sheet2!$A$2:$A$4=A2)*(Sheet2!$B$2:$B$4=B2)+(Sheet2!$A$2:$A$4=B2)*(Sheet2!$B$2:$B$4=A2))*(Sheet2!$C$2:$C$4) )
    D2=SUMIFS(Sheet2!$C$2:$C$4,Sheet2!$A$2:$A$4,A2,Sheet2!$B$2:$B$4,B2)+SUMIFS(Sheet2!$C$2:$C$4,Sheet2!$A$2:$A$4,B2,Sheet2!$B$ 2:$B$4,A2)


    Sheet2
     ABC
    1Client FromClient ToMiles
    2AB5
    3AC7
    4BC10
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    May 2016
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Result based on criteria fro 2 lists

    Thank you for this - SUMProduct works but not SUMIF - however it does not pick up Row 5 or after of the table in sheet2 - (so it works for the first 3 rows of information)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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