Finding the max value of driving distance between two sites

[RCU61]

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>

 

[AlphaFrog]

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

 

[RCU61]

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]

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

Array Formulas

Cell Formula 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.

 

[Aladin Akyurek]

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]

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.

 

[Aladin Akyurek]

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