Return Nth instance of text string

[Partjob]

I have the following formula that returns the most frequently occuring string in a range. what I would like to do is return the 2nd, 3rd 4th and 5th most common occourance as well. I am not sure if I can do this by adjusting this formula or whether that would be a completely diffent formula or worst case senario it is not possible at all.

=INDEX(B2:B1537,MATCH(MAX(COUNTIF(B2:B1537,B2:B1537)),COUNTIF(B2:B1537,B2:B1537),0))

obviously this is an array.

Partjob

 

[Andrew Poulsom]

Use the LARGE function instead of the MAX function. It takes a position argument.

 

[xld]

Much more complex I think.

This solution uses a helper column.

In C2 enter

=IF(COUNTIF(B2:$B$1537,B2)=1,COUNTIF($B$2:$B$1537,B2)+ROW()/100000,"")

and copy down to C1537

Then in D2, enter this array formula

=IF(ISERROR(LARGE($C$2:$C$1537,ROW(A1))),"",INDEX($B$2:$B$1537,MIN(IF($C$2:$C$1537=LARGE($C$2:$C$1537,ROW(A1)),ROW($C$2:$C$1537)))-ROW($C$2:$C$1537)+1))

and copy down until you get blanks

 

[Richard Schollar]

Given values in A1:A15 this is an alternative 'all in 1':

=INDEX($A$1:$A$15,MATCH(TRUE,COUNTIF($A$1:$A$15,$A$1:$A$15)=LARGE(FREQUENCY(MATCH($A$1:$A$15,$A$1:$A$15,0),MATCH($A$1:$A$15,$A$1:$A$15,0)),2),0))

Confirmed with Ctrl+Shift+Enter. Change the red 2 to whatever position you want ie 2nd most common, 3rd etc ect

 

[Partjob]

Andrew
I now have the folowing formula but it is not changing the result when I change the k value.
Formula looks like this now I feel I should get the second highest occurence.
=INDEX(B2:B1537,MATCH(LARGE(COUNTIF(B2:B1537,B2:B1537),2),COUNTIF(B2:B1537,B2:B1537),0))

Thanks for taking the time to help me

Partjob

 

[xld]

Andrew
I now have the folowing formula but it is not changing the result when I change the k value.
Formula looks like this now I feel I should get the second highest occurence.
=INDEX(B2:B1537,MATCH(LARGE(COUNTIF(B2:B1537,B2:B1537),2),COUNTIF(B2:B1537,B2:B1537),0))

Thanks for taking the time to help me

Partjob

Isn't it just finding the secind instance of the highest occurring?

 

[Andrew Poulsom]

Isn't it just finding the secind instance of the highest occurring?

Yes, my fault for not thinking clearly.

 

[Partjob]

Thank you all for your help.
For the record xld's solution worked for me.
andrew's didn't give me the required result for the reason stated in the thread.
Richard's returned N/A not really sure why?
It would be nice if it was all in one formula but what I have works so what more can I ask.
Thanks again
Partjob

 

[Richard Schollar]

You have to confirm with Ctrl+Shift+Enter (as it is an array formula). Your ranges will obviously need to be different to mine too.

 

[Peter_SSs]

Partjob

Could you use a PivotTable?

One of the options in a PivotTable is to Sort. Here (columns D:E) is a small one, sorted descending on the 'Total' field.

If you are only interested in the top several values (maybe 5 in your case), you can also have the table be restricted to the top x values. Columns G:H shows a PivotTable, still sorted descending on the 'Total' field, but also restricted to the top 2 values.

If the source data is changed and the PivotTable refreshed, the table gets re-sorted and/or re-restricted to the top x values.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Heading		Count of Heading			Count of Heading
2		a		Heading	Total		Heading	Total
3		a		a	7		a	7
4		a		c	5		c	5
5		a		b	4		Grand Total	12
6		a		Grand Total	16
7		a
8		b
9		c
10		c
11		c
12		b
13		b
14		b
15		c
16		a
17		c
18
19
PivotTable