Find Minimum Value, Then Write Out All Corresponding Information

[Daninja]

Hello,

I have a list of cities with distances and travel times between them. Postal Code is only for further identification of the city or city part. The table looks like this (with a total of 60000 rows):


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Postal Code 1[/TD]
[TD]City 1[/TD]
[TD]Postal Code 2[/TD]
[TD]City 2[/TD]
[TD]Distance(km)[/TD]
[TD]Travel time(minutes)[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]Prague[/TD]
[TD]61700[/TD]
[TD]Brno[/TD]
[TD]202[/TD]
[TD]117[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]Prague[/TD]
[TD]70030[/TD]
[TD]Ostrava[/TD]
[TD]364[/TD]
[TD]202[/TD]
[/TR]
[TR]
[TD]77900[/TD]
[TD]Olomouc[/TD]
[TD]70030[/TD]
[TD]Ostrava[/TD]
[TD]90[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

In the first 2 columns I have a set of only 18 big cities with which I want to compare the rest of the cities (about 3300) in the next 2 columns.

What I want to achieve is when I enter a Postal Code, I would like Excel to check column "Postal Code 2" for all instances of this number and then find the corresponding smallest travel time (Minimum Value in column F). Once I find the minimum value (basically it says what is the shortest travel time from the chosen city to one of the 18 big cities), I would like to also get the Postal Code 1 and City 1 and the Distance, so I know to which city pair the minimum travel time belongs and also what is the distance between them.

So, for example in this simplified 3-row table, if I enter Postal Code for Ostrava = "70030", I would like the result to be:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]77900[/TD]
[TD]Olomouc[/TD]
[TD]90[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

...because for this Postal Code the shortest travel time is 60 minutes, takes 90 kilometers and goes to the city of Olomouc with Postal Code 77900.


For my full big list with 60k rows, I also want to have the same output for the 2nd and 3rd shortest travel time, so the output table would have 4 columns and 3 rows (4x3).

---

So far I have tried the "MINIFS" function, with which I can find the shortest travel time given the criteria. To get 2nd and 3rd shortest travel time, I could use a combination of functions LARGE and IF.

What I am stuck on is how to also get the additional and identifying information (Postal Code 1, City 1, Distance) which corresponds to the shortest travel time and not just the single value, basically achieving the 4x3 table mentioned above. Please help

If something is unclear or you need more info to be able to help solve this, please ask and I will try to clear it up.

 

[DRSteele]

Welcome to the forum.

With such a small amount of sample data, it is difficult to make sure this is correct. I suspect that it will fail if there are duplicates, such as two trips from 77900 to 70030 that have different times but identical distances. So try this and let us know if it works on broader data.

Note that my formulas are reporting errors because there is only one trip with 77900 as the criteria.

Book1
	A	B	C	D	E	F
1	Postal Code 1	City 1	Postal Code 2	City 2	Distance(km)	Travel time(minutes)
2	10000	Prague	61700	Brno	202	117
3	10000	Prague	70030	Ostrava	364	202
4	77900	Olomouc	70030	Ostrava	90	60
5
6
7	Ordinal	Postal Code 1	City 1	Distance(km)	Travel time(minutes)
8	1	77900	Olomouc	60	90
9	2	77900	Olomouc	#NUM!	#NUM!
10	3	77900	Olomouc	#NUM!	#NUM!
Sheet52

Cell Formulas
Range	Formula
C8	=INDEX($B$2:$B$4,MATCH(B8,$A$2:$A$4,0))
D8	=AGGREGATE(15,6,$F$2:$F$4/($A$2:$A$4=B8),A8)
E8	=SUMPRODUCT((D8=$F$2:$F$4)*(B8=$A$2:$A$4)*($E$2:$E$4))

 

[Daninja]

Hello DRSteele,

thanks for the reply. As you say I am indeed new to the forums - I see a "button" called "

Sheet52"​

- does that work as a download button for your sample spreadsheet? It doesnt do anything when I click it. I do see your formulas though, thanks.

As to your Q, there are NO DUPLICATES. A trip from one place to another always has only 1 value. There are even trips from one place to the very same place with both distance and time being 0, in that case no travel is needed and the person is already in his destination, but this shouldnt cause any problems with the formulas.

Yes I realize the sample info I provided is lacking, but I dont know how to simply add more data, I just used the table function of the text editor and added all info manually.. is it possible to upload a sample excel sheet?

I will promptly try your formulas and get back.

 

[DRSteele]

You can post more sample data together with expected outcome and a brief but clear description by downloading and installing the MrExcel HTML Maker or other add-in, found here: https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

Also, you've exceeded your Private Message allotment, so I can't reply to your verbose message. In any event, kindly keep the discussion in the public thread. And I never download files.