Result based on criteria fro 2 lists

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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:

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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.
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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(...))?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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:

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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?
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
Also if I drag the drop downs and formula down on sheet 1 - if only works on row 1
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,104
Office Version
2007
Platform
Windows
Try the following formulas
You can use sumproduct or sumifs

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:100.75px;" /><col style="width:102.65px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Client From</td><td >Client To</td><td >Mileage</td><td >Mileage</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >B</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >C</td><td style="text-align:right; ">7</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >C</td><td >A</td><td style="text-align:right; ">7</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >B</td><td >A</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >B</td><td >C</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=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))</td></tr><tr><td >D2</td><td >=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)</td></tr></table></td></tr></table>

Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:95.05px;" /><col style="width:89.35px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Client From</td><td >Client To</td><td >Miles</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >B</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >C</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >B</td><td >C</td><td style="text-align:right; ">10</td></tr></table>
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
159
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,997
Messages
5,484,100
Members
407,430
Latest member
sgoldman

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top