Returning highest 5 numbers in range with conditions

[onthegreen03]

Hi. I posted earlier about this issue, thought I had it licked but alas did not. I pasted a picture below to help explain the issue I am having. I have a table with the top 5 and bottom 5 locations for a certain account. For the top 5 I am using a SMALL formula which looks at the specified range (using RANK formula) and returns 1 through 5, so no issues with that chart. The problem is the bottom 5 chart. When the parent account has more than 10 locations a LARGE formula will and does work. But when the total locations are between 6 and 9 some of the numbers repeat. In the example below I need to write a formula that looks at the range of numbers (in this case 1-8) and only return 6-8. If more information is needed to solve this let me know and I will include. This has stumped me all day. Thank you!

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H
1	City	State	Zip	Rating		Top 5 Locations:
2	Austin	TX	78758	3		City	State	Zip
3	Houston	TX	77056	1		Houston	TX	77056
4	San Antonio	TX	78229	2		San Antonio	TX	78229
5	Lafayette	LA	70508	8		Austin	TX	78758
6	Pflugerville	TX	78660	5		New Orleans	LA	70115
7	New Orleans	LA	70115	4		Pflugerville	TX	78660
8	Austin	TX	78732	6
9	Austin	TX	78738	7		Bottom 5 Locations:
10						City	State	Zip
11						Lafayette	LA	70508
12						Austin	TX	78738
13						Austin	TX	78732
14
Sheet6

Cell Formulas
Range	Formula
F3:H7	F3	=INDEX(SORTBY(A2:C10,D2:D10),SEQUENCE(5),{1,2,3})
F11:H13	F11	=INDEX(SORTBY(A2:C10,D2:D10,-1),SEQUENCE(MIN(5,COUNTA(A2:A10)-5)),{1,2,3})
Dynamic array formulas.

 

[onthegreen03]

Hi Eric. Thank you for the suggestion, this is exactly what I needed. What if I wanted to return just the resulting RANK numbers (In your example the top 5 in cells F3:F7 and bottom 5 in cells F11:F15)? In your example I would want to return numbers 1-5 in cells F3:F7 and 6,7, and 8 in cells F11:F13, while cells F14:F15 would be blank. Below is the actual table that I am using. The RANK formula is sitting in cells A8:A65. Depending on the number of locations that range will obviously change (in this example there are 8 locations). What I am looking for is a formula that returns the top 5 numbers (1 through 5 in this example), and the bottom 5 (6-8 in this example). There are times when the account only has 3 locations, so in that case I'd want to return 1, 2 and 3, while the bottom 5 would be blank. In other cases the account may have 25 locations, so I'd want to return 1-5 (top) and 21-25 (bottom). Let me know if you have any suggestions to accomplish this. Thanks again!

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1								Top 5 Locations:
2								Rank	City	State	Zip
3								1	Houston	TX	77056
4								2	San Antonio	TX	78229
5								3	Austin	TX	78758
6								4	New Orleans	LA	70115
7	Rating			Zip	City	State		5	Pflugerville	TX	78660
8	3	Yes		78758	Austin	TX
9	1	Yes		77056	Houston	TX		Bottom 5 Locations:
10	2	Yes		78229	San Antonio	TX		Rank	City	State	Zip
11	8	Yes		70508	Lafayette	LA		8	Lafayette	LA	70508
12	5	Yes		78660	Pflugerville	TX		7	Austin	TX	78738
13	4	Yes		70115	New Orleans	LA		6	Austin	TX	78732
14	6	Yes		78732	Austin	TX
15	7	Yes		78738	Austin	TX
16		#N/A			#N/A	#N/A
17		#N/A			#N/A	#N/A
18		#N/A			#N/A	#N/A
19		#N/A			#N/A	#N/A
20		#N/A			#N/A	#N/A
Sheet6

Cell Formulas
Range	Formula
H3:K7	H3	=INDEX(SORT(FILTER(A8:F20,IFERROR(B8:B20="Yes",0))),SEQUENCE(MIN(5,COUNTIF(B8:B20,"Yes"))),{1,5,6,4})
H11:K13	H11	=IFERROR(INDEX(SORT(FILTER(A8:F20,IFERROR(B8:B20="Yes",0)),,-1),SEQUENCE(MIN(5,COUNTIF(B8:B20,"Yes")-5)),{1,5,6,4}),"")
Dynamic array formulas.

 

[onthegreen03]

Amazing! Of course it worked and thank you. The only issue is this. In the results table I have the City name merged into 3 columns so the longer names fit correctly. Is there a way to edit the formulas so that the city populates columns I-J-K and the State in column L and zip in column M (based on your example above)? I had to unmerge those 3 columns to get the formula to work. If not, no big deal. I can just go back through my model and unmerge those City name cells and just readjust the column widths. Not ideal but I can do that if needed. So grateful for your help on this one Eric ... Thanks!!

 

[Eric W]

Merged cells usually pose a real problem with formulas. I can usually figure out a way to read from a merged cell, but as far as I know, these SPILL formulas won't SPILL into merged cells. You'd probably need to create separate formulas for each column. If you'd want to leave the source table alone, just make sure the ranges in the formula include the entire columns, and use the first column with the city as the output column (in the {1,5,6,4} array). But the output table pretty much has to be a single column. Just widen it so that the whole city name shows. I'd actually recommend unmerging your source table too, but that's your call.

 

[onthegreen03]

Will do, taking your advice as we speak. Thank you again ... the formula's work great!