Top 10 Values- Skip Duplicate

[emerdmann]

I am measuring "population" for states/cities. I would like to get a list of top 3 states/cities in critical status based on population. The dataset I am using has metrics for years 1910-2017. Short example with made up data:

	A	B	C	D	E
1	State	City	Year	Status	Population
2	Pennsylvania	Philadelphia	1910	Critical	554,000
3	Pennsylvania	Philadelphia	1911	Critical	650,987
4	Pennsylvania	Philadelphia	1912	Critical	982,545
5	Delaware	Wilmington	1910	Critical	450,240
6	New Jersey	Ocean City	1912	Critical	245,568
7	New Jersey	Ocean City	1913	Safe	982,245
8	Texas	Austin	1910	Semi-Critical	654,244

To do this, I used the following formula:

=LARGE(IF(D2:D8=H2,E2:E8,""),ROW(A1:A3))

(Cell H3 to the side of this table has the text "critical")

When it returned my data I got the following result:

982,545

650,987

554,000

When I matched the population data to the state/city I just had a list of Pennsylvania-Philadelphia. What I really was looking for is:

982,545 (Pennsylvania-Philadelphia)

450,240 (Delaware, Wilmington)

245,568 (New Jersey, Ocean City)

I understand that Pennsylvania-Philadelphia has the 3 largest population sizes, but I only want it listed once and I want the highest value. I want the formula to skip duplicates of Pennsylvania-Philadelphia again. I've researched using the UNIQUE function in my formula, but I only know how to apply that to population size and not unique listing of State/City.

Any help?

 

[Fluff]

Do you have functions such as TAKE & HSTACK?
If so, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	State	City	Year	Status	Population
2	Pennsylvania	Philadelphia	1910	Critical	554,000			Critical	Pennsylvania	Philadelphia	982545
3	Pennsylvania	Philadelphia	1911	Critical	650,987				Delaware	Wilmington	450240
4	Pennsylvania	Philadelphia	1912	Critical	982,545				New Jersey	Ocean City	245568
5	Delaware	Wilmington	1910	Critical	450,240
6	New Jersey	Ocean City	1912	Critical	245,568
7	New Jersey	Ocean City	1913	Safe	982,245
8	Texas	Austin	1910	Semi-Critical	654,244
9
Report

Cell Formulas
Range	Formula
I2:K4	I2	=LET(u,UNIQUE(FILTER(A2:B1000,(A2:A1000<>"")*(D2:D1000=H2))),TAKE(SORT(HSTACK(u,MAXIFS(E:E,A:A,INDEX(u,,1),D:D,H2)),3,-1),10))
Dynamic array formulas.

 

[emerdmann]

Do you have functions such as TAKE & HSTACK?
If so, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	State	City	Year	Status	Population
2	Pennsylvania	Philadelphia	1910	Critical	554,000			Critical	Pennsylvania	Philadelphia	982545
3	Pennsylvania	Philadelphia	1911	Critical	650,987				Delaware	Wilmington	450240
4	Pennsylvania	Philadelphia	1912	Critical	982,545				New Jersey	Ocean City	245568
5	Delaware	Wilmington	1910	Critical	450,240
6	New Jersey	Ocean City	1912	Critical	245,568
7	New Jersey	Ocean City	1913	Safe	982,245
8	Texas	Austin	1910	Semi-Critical	654,244
9
Report

Cell Formulas
Range	Formula
I2:K4	I2	=LET(u,UNIQUE(FILTER(A2:B1000,(A2:A1000<>"")*(D2:D1000=H2))),TAKE(SORT(HSTACK(u,MAXIFS(E:E,A:A,INDEX(u,,1),D:D,H2)),3,-1),10))
Dynamic array formulas.

Hey,

Thanks for the help here. Unfortunately, I do not think my version of Excel has that capability. I just tried it out and I do not think it recognized the "u" or "HSTACK" functions. I appreciate the response, though!

 

[Fluff]

Do you have the LET function?