Result based on criteria fro 2 lists

[marcidee]

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]

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

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.

 

[marcidee]

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]

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]

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]

I might have overdone this but try

in Sheet2!A1:C3 put

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.

 

[marcidee]

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]

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

 

[DanteAmor]

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]

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)