Top 5 States With Values

[ZSchoonover]

So I've been researching all day on how to do this, and I'm a bit confused on how to proceed. I need to create multiple top 5 list of States from a data series. I've included a screenshot to help better understand what it I'm trying to achieve. In short, I need excel to auto calculate the top 5 states for students who have applied for spring and fall semesters and return the state name next to the number itself. This info will be populated into 2 separate areas of the sheet C11:D15 and C35:D38. There will be duplicates included with this.

[/URL][/IMG]

The range L2:L51 is labeled "States", M2:M51 is "Spring18", and N2:N51 is "Fall18". This list is dynamic and will be changing quite often so the formulas that calculate the top 5 need to be dynamic as well. I've not had to go this far in depth with excel in years and as such this is the first time I've dealt with arrays and lengthy functions, so I apologize in advance if I'm missing any pertinent information. I'm also using excel 2016/excel 365.

Thanks,
Zach

 

[Chrisdontm]

Try these;

in cell C11:
=IF($D11=$D10,INDEX(OFFSET($L$1,MATCH($C10,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D11,OFFSET($M$1,MATCH($C10,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D11,$M$2:$M$51,0)))
Then copy it down through cell C15

in cell D11:
=LARGE($M$2:$M$51,ROWS($A$1:A1))
Then copy it down through cell D15

in cell C35:
=IF($D35=$D34,INDEX(OFFSET($L$1,MATCH($C34,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D35,OFFSET($N$1,MATCH($C34,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D35,$N$2:$N$51,0)))
Then copy it down through cell C39

in cell D35:
=LARGE($N$2:$N$51,ROWS($A$1:A1))
Then copy it down through cell D39

 

[Aladin Akyurek]

Try to post an Excel readable sample directly here along with the output that goes with that sample.

 

[ZSchoonover]

Try these;

in cell C11:
=IF($D11=$D10,INDEX(OFFSET($L$1,MATCH($C10,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D11,OFFSET($M$1,MATCH($C10,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D11,$M$2:$M$51,0)))
Then copy it down through cell C15

in cell D11:
=LARGE($M$2:$M$51,ROWS($A$1:A1))
Then copy it down through cell D15

in cell C35:
=IF($D35=$D34,INDEX(OFFSET($L$1,MATCH($C34,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D35,OFFSET($N$1,MATCH($C34,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D35,$N$2:$N$51,0)))
Then copy it down through cell C39

in cell D35:
=LARGE($N$2:$N$51,ROWS($A$1:A1))
Then copy it down through cell D39

Wow, that works perfectly, thank you so much! You all make it seem so easy!

 

[ZSchoonover]

Try these;

in cell C11:
=IF($D11=$D10,INDEX(OFFSET($L$1,MATCH($C10,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D11,OFFSET($M$1,MATCH($C10,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D11,$M$2:$M$51,0)))
Then copy it down through cell C15

in cell D11:
=LARGE($M$2:$M$51,ROWS($A$1:A1))
Then copy it down through cell D15

in cell C35:
=IF($D35=$D34,INDEX(OFFSET($L$1,MATCH($C34,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D35,OFFSET($N$1,MATCH($C34,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D35,$N$2:$N$51,0)))
Then copy it down through cell C39

in cell D35:
=LARGE($N$2:$N$51,ROWS($A$1:A1))
Then copy it down through cell D39

I posted too soon. The first 2 formulas seems to working perfectly for the 1st output table, however formula 3 for the second output table is giving me an #N/A error for the 4th rank value. The current calculation for the output are VA-7, PA-3, FL-2, #N/A-2, IN-1. This is all correct except the #N/A error should be reading "NJ"

I originally gave you the wrong output range for this table (C35:D39) the correct range is (C34:D38). I did correct the formula accordingly to reflect that change, but maybe I'm missing something? The corrected formula is:

in cell C34
=IF($D34=$D33,INDEX(OFFSET($L$1,MATCH($C33,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),MATCH($D34,OFFSET($N$1,MATCH($C33,$L$1:$L$50,0),,51-MATCH($C10,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D34,$N$2:$N$51,0)))
Copied down to cell C38

 

[Aladin Akyurek]

How about posting a sample?

 

[ZSchoonover]

I think I did this correctly? I saved a copy of the file as a "Formatted Text (Space Delimited)" .txt file, the original file was an .xlsx file. The notepad is copied below and the delimiter for importing this into excel is "Tab" Hope this is what you were asking for.

201820 State 201820 201840
AC/AK 7 AK 0 0
HE 8 AL 0 0
WD 4 AR 0 0
RJ 0 AZ 0 0
Total Apps. 15 CA 0 0
Av. UG GPA 3.08 CO 0 0
CT 0 0
Top 5 States DE 0 0
State Apps FL 1 2
1 GA 0 0
2 HI 0 0
3 IA 0 0
4 ID 0 0
5 IL 0 0
***Check for repating app IN 0 1
numbers for 5th rank*** KS 0 0
KY 0 0
LA 0 1
MA 0 0
MD 2 1
ME 0 0
MI 1 0
201840 MN 0 0
AC/AK 5 MO 0 0
HE 15 MS 0 0
WD 3 MT 0 0
RJ 0 NC 0 1
Total Apps. 20 ND 0 0
Av. UG GPA 3.08 NE 0 0
NH 0 0
Top 5 States NJ 0 2
State Apps NM 0 0
1 NV 0 0
2 NY 1 0
3 OH 0 0
4 OK 0 0
5 OR 0 0
***Check for repating app PA 2 3
numbers for 5th rank*** RI 0 0
SC 0 1
SD 0 0
TN 0 0
TX 0 1
UT 0 0
VA 8 7
VT 0 0
WA 0 0
WI 0 0
WV 0 0
WY 0 0

 

[ZSchoonover]

That didn't seem to post with the correct spacing. Could you provide input on how to post the file via what is acceptable by the mrexcel forum?

 

[Aladin Akyurek]

That didn't seem to post with the correct spacing. Could you provide input on how to post the file via what is acceptable by the mrexcel forum?

You can try one of the possible methods for posting useable samples listed in Attachments.

 

[ZSchoonover]

	201820								State	201820	201840
		AC/AK	7								AK	0	0
		HE	8								AL	0	0
		WD	4								AR	0	0
		RJ	0								AZ	0	0
		Total Apps.	15								CA	0	0
		Av. UG GPA	3.08								CO	0	0
											CT	0	0
	Top 5 States								DE	0	0
		State	Apps								FL	1	2
	1										GA	0	0
	2										HI	0	0
	3										IA	0	0
	4										ID	0	0
	5										IL	0	0
	***Check for repating app								IN	0	1
	numbers for 5th rank***								KS	0	0
											KY	0	0
											LA	0	1
											MA	0	0
											MD	2	1
											ME	0	0
											MI	1	0
	201840								MN	0	0
		AC/AK	5								MO	0	0
		HE	15								MS	0	0
		WD	3								MT	0	0
		RJ	0								NC	0	1
		Total Apps.	20								ND	0	0
		Av. UG GPA	3.08								NE	0	0
											NH	0	0
	Top 5 States								NJ	0	2
		State	Apps								NM	0	0
	1										NV	0	0
	2										NY	1	0
	3										OH	0	0
	4										OK	0	0
	5										OR	0	0
	***Check for repating app								PA	2	3
	numbers for 5th rank***								RI	0	0
											SC	0	1
											SD	0	0
											TN	0	0
											TX	0	1
											UT	0	0
											VA	8	7
											VT	0	0
											WA	0	0
											WI	0	0
											WV	0	0
											WY	0	0

<tbody>
</tbody>

Thank you, I think I got it now. The range shown here is A1:N51.