Result based on criteria fro 2 lists

marcidee

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,312
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
153
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,312
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
153
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,312
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
153
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
153
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
7,852
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
153
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)
 

Forum statistics

Threads
1,078,240
Messages
5,339,031
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top