Result based on criteria fro 2 lists

marcidee

Board Regular
Joined
May 23, 2016
Messages
184
Office Version
  1. 2019
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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(...))?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Also if I drag the drop downs and formula down on sheet 1 - if only works on row 1
 
Upvote 0
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>
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top