# Finding the max value of driving distance between two sites

#### RCU61

##### New Member
I would like your assistance in finding the max value of the time required to drive from site A to site Z via multiple paths (including the exception when you can travel the same number of hours via two different paths).
What are the formulas to be used to extract just one set of records (site A- Site Z and Time) given the structure below? Anticipated thanks.

 Site A Site Z Time (hrs) A B 3 A B 2.5 A B 1.8 A C 65 A C 34 A D 44 A D 44 A E 55 B K 88 B K 72 B L 56 C M 34.5 C N 20 C P 14

<tbody>
</tbody>

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### AlphaFrog

##### MrExcel MVP
If you have Excel 2016, there is a MAXIFS function

Otherwise, you can use this array formula...
MAX IF Formula
=MAX(IF((A1:A100="A")*(B1:B100="B"),C1:C100))
Confirm with Ctrl+Shift+Enter

Last edited:

#### RCU61

##### New Member
Thank you. In my Excel 2010 version I obtained, using the suggested formula, A to B 88 hours instead of 3 hours.

I probably confused many of this post's viewers by being too laconic. Here is a more detailed explanation of the problem.

Data Source Table Results Table
 Site A Site Z Hours Site 1 Site 2 3 Site 1 Site 2 2.5 Site 1 Site 2 1.8 Site 1 Site 3 65 Site 1 Site 3 34 Site 1 Site 4 44 Site 1 Site 4 44 Site 1 Site 5 55 Site 2 Site 6 88 Site 2 Site 7 72 Site 2 Site 8 56 Site 3 Site 9 34.5 Site 3 Site 9 20 Site 3 Site 10 14

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>
<strike></strike>
 Site A Site Z Hours Site 1 Site 2 3 Site 1 Site 3 65 Site 1 Site 4 44 Site 1 Site 5 55 Site 2 Site 6 88 Site 2 Site 7 72 Site 2 Site 8 56 Site 3 Site 9 34.5 Site 3 Site 10 14

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>
<strike></strike>​

<tbody>
</tbody>

#### AlphaFrog

##### MrExcel MVP

ABCDEFG
1Site ASite ZHours
Site A
Site Z
Hours
2Site 1Site 2
3​
Site 1​
Site 2​
3​
3Site 1Site 2
2.5​
Site 1​
Site 3​
65​
4Site 1Site 2
1.8​
Site 1​
Site 4​
44​
5Site 1Site 3
65​
Site 1​
Site 5​
55​
6Site 1Site 3
34​
Site 2​
Site 6​
88​
7Site 1Site 4
44​
Site 2​
Site 7​
72​
8Site 1Site 4
44​
Site 2​
Site 8​
56​
9Site 1Site 5
55​
Site 3​
Site 9​
34.5​
10Site 2Site 6
88​
Site 3​
Site 10​
14​
11Site 2Site 7
72​
12Site 2Site 8
56​
13Site 3Site 9
34.5​
14Site 3Site 9
20​
15Site 3Site 10
14​

Array Formulas
CellFormula
G2{=MAX(IF((\$A\$2:\$A\$15=E2)*(\$B\$2:\$B\$15=F2),\$C\$2:\$C\$15))}
G3{=MAX(IF((\$A\$2:\$A\$15=E3)*(\$B\$2:\$B\$15=F3),\$C\$2:\$C\$15))}
G4{=MAX(IF((\$A\$2:\$A\$15=E4)*(\$B\$2:\$B\$15=F4),\$C\$2:\$C\$15))}
G5{=MAX(IF((\$A\$2:\$A\$15=E5)*(\$B\$2:\$B\$15=F5),\$C\$2:\$C\$15))}
G6{=MAX(IF((\$A\$2:\$A\$15=E6)*(\$B\$2:\$B\$15=F6),\$C\$2:\$C\$15))}
G7{=MAX(IF((\$A\$2:\$A\$15=E7)*(\$B\$2:\$B\$15=F7),\$C\$2:\$C\$15))}
G8{=MAX(IF((\$A\$2:\$A\$15=E8)*(\$B\$2:\$B\$15=F8),\$C\$2:\$C\$15))}
G9{=MAX(IF((\$A\$2:\$A\$15=E9)*(\$B\$2:\$B\$15=F9),\$C\$2:\$C\$15))}
G10{=MAX(IF((\$A\$2:\$A\$15=E10)*(\$B\$2:\$B\$15=F10),\$C\$2:\$C\$15))}
Entered with Ctrl+Shift+Enter.
Excel will automatically surround the {formula} with curly braces.
Do not manually add the braces to the formula.

##### MrExcel MVP
 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ 1​ Site A Site Z Hours concat 9​ 2​ Site 1 Site 2 3 Site 1|Site 2 Site A​ Site Z​ Hours​ 3​ Site 1 Site 2 2.5 Site 1|Site 2 Site 1​ Site 2​ 3​ 4​ Site 1 Site 2 1.8 Site 1|Site 2 Site 1​ Site 3​ 65​ 5​ Site 1 Site 3 65 Site 1|Site 3 Site 1​ Site 4​ 44​ 6​ Site 1 Site 3 34 Site 1|Site 3 Site 1​ Site 5​ 55​ 7​ Site 1 Site 4 44 Site 1|Site 4 Site 2​ Site 6​ 88​ 8​ Site 1 Site 4 44 Site 1|Site 4 Site 2​ Site 7​ 72​ 9​ Site 1 Site 5 55 Site 1|Site 5 Site 2​ Site 8​ 56​ 10​ Site 2 Site 6 88 Site 2|Site 6 Site 3​ Site 9​ 34.5​ 11​ Site 2 Site 7 72 Site 2|Site 7 Site 3​ Site 10​ 14​ 12​ Site 2 Site 8 56 Site 2|Site 8 ​ ​ ​ 13​ Site 3 Site 9 34.5 Site 3|Site 9 ​ ​ 14​ Site 3 Site 9 20 Site 3|Site 9 ​ ​ 15​ Site 3 Site 10 14 Site 3|Site 10 ​ ​

In D2 just enter and copy down:

=A2&"|"&B2

In F1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(\$D\$2:\$D\$15=""),MATCH(\$D\$2:\$D\$15,\$D\$2:\$D\$15,0)),ROW(\$D\$2:\$D\$15)-ROW(\$D\$2)+1),1))

In F3 control+shift+enter, copy to G3, and down:

=IF(ROWS(\$F\$3:F3)>\$F\$1,"",INDEX(A\$2:A\$15,SMALL(IF(FREQUENCY(IF(1-(\$D\$2:\$D\$15=""),MATCH(\$D\$2:\$D\$15,\$D\$2:\$D\$15,0)),ROW(\$D\$2:\$D\$15)-ROW(\$D\$2)+1),ROW(\$D\$2:\$D\$15)-ROW(\$D\$2)+1),ROWS(\$F\$3:F3))))

In H3 control+shift+enter and copy down:

=IF(\$F3="","",MAX(IF(\$A\$2:\$A\$15=\$F3,IF(\$B\$2:\$B\$15=\$G3,\$C\$2:\$C\$15))))

#### RCU61

##### New Member
 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ 1​ Site A Site Z Hours concat 9​ 2​ Site 1 Site 2 3 Site 1|Site 2 Site A​ Site Z​ Hours​ 3​ Site 1 Site 2 2.5 Site 1|Site 2 Site 1​ Site 2​ 3​ 4​ Site 1 Site 2 1.8 Site 1|Site 2 Site 1​ Site 3​ 65​ 5​ Site 1 Site 3 65 Site 1|Site 3 Site 1​ Site 4​ 44​ 6​ Site 1 Site 3 34 Site 1|Site 3 Site 1​ Site 5​ 55​ 7​ Site 1 Site 4 44 Site 1|Site 4 Site 2​ Site 6​ 88​ 8​ Site 1 Site 4 44 Site 1|Site 4 Site 2​ Site 7​ 72​ 9​ Site 1 Site 5 55 Site 1|Site 5 Site 2​ Site 8​ 56​ 10​ Site 2 Site 6 88 Site 2|Site 6 Site 3​ Site 9​ 34.5​ 11​ Site 2 Site 7 72 Site 2|Site 7 Site 3​ Site 10​ 14​ 12​ Site 2 Site 8 56 Site 2|Site 8 13​ Site 3 Site 9 34.5 Site 3|Site 9 14​ Site 3 Site 9 20 Site 3|Site 9 15​ Site 3 Site 10 14 Site 3|Site 10

<tbody>
</tbody>

In D2 just enter and copy down:

=A2&"|"&B2

In F1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(\$D\$2:\$D\$15=""),MATCH(\$D\$2:\$D\$15,\$D\$2:\$D\$15,0)),ROW(\$D\$2:\$D\$15)-ROW(\$D\$2)+1),1))

In F3 control+shift+enter, copy to G3, and down:

=IF(ROWS(\$F\$3:F3)>\$F\$1,"",INDEX(A\$2:A\$15,SMALL(IF(FREQUENCY(IF(1-(\$D\$2:\$D\$15=""),MATCH(\$D\$2:\$D\$15,\$D\$2:\$D\$15,0)),ROW(\$D\$2:\$D\$15)-ROW(\$D\$2)+1),ROW(\$D\$2:\$D\$15)-ROW(\$D\$2)+1),ROWS(\$F\$3:F3))))

In H3 control+shift+enter and copy down:

=IF(\$F3="","",MAX(IF(\$A\$2:\$A\$15=\$F3,IF(\$B\$2:\$B\$15=\$G3,\$C\$2:\$C\$15))))

Thank you Aladin, you understood the overall issue and the formulas provided helped me solved my matrix. I appreciate your assistance.

##### MrExcel MVP
Thank you Aladin, you understood the overall issue and the formulas provided helped me solved my matrix. I appreciate your assistance.

Glad to help. (I think Alpha simply missed the unique list bit.)

Replies
17
Views
217
Replies
2
Views
285
Replies
0
Views
318
Replies
4
Views
575
Replies
4
Views
335

1,191,040
Messages
5,984,295
Members
439,882
Latest member
gerdc

### 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.

### Which adblocker are you using?

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

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