2nd most common entry if

[Fazila]

Hi

Any ideas how I can get the second most commonly occurring text value based on an if. The data I have is as follows:

A	B	C
Class	Teacher	Subject
10x/Ar1	AOW	Art
11x/Ar1	AOW	Art
11z/Ar1	AOW	Art
7L10/Ar	AOW	Art
7L3/Ar	EBE	Art
7L8/Ar	EBE	Art
10y/Ci1	RAM	Citizenship
11x/Ci1	RAM	Citizenship
10w/Ci1	SAH	Citizenship

<tbody>
</tbody>

I have named the ranges so column A is Cls, Column B is Teach and Column C is Sub. The formula needs to go into another sheet?

To get the most common entry I have used the formula: {=INDEX(Sheet6!$B$2:$B$431,MODE(IF(Sheet6!$C$2:$C$431=Sheet4!$A3,MATCH(Sheet6!$B$2:$B$431,Sheet6!$B$2:$B$431,0))))}

I now need the second most common then the third etc.

I need to know the most occurring value based on subject so for Art my first common value would be AOW then EBE and for Citizenship the most common value would be RAM then SAH.

Any help would be really appreciated.

Thanks

 

[James006]

Hello,

Barry has offered an excellent solution ... .wink:

https://www.mrexcel.com/forum/excel-questions/1032393-second-most-common-text-value.html#post4955384

Hope this will help

 

[Fazila]

Hello,

Barry has offered an excellent solution ... .wink:

https://www.mrexcel.com/forum/excel-questions/1032393-second-most-common-text-value.html#post4955384

Hope this will help

Thanks James006 but don't think this will work as this formula calculates the most common text provided it is not Y whereas I need the second most common entry. The criteria relates to another column i.e. the subject.

Does this make sense?

Thanks

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​
1​	Class​	Teacher​	Subject​		Art​	Citizenship​
2​	10x/Ar1​	AOW​	Art​		AOW​	RAM​
3​	11x/Ar1​	AOW​	Art​		EBE​	SAH​
4​	11z/Ar1​	AOW​	Art​
5​	7L10/Ar​	AOW​	Art​
6​	7L3/Ar​	EBE​	Art​
7​	7L8/Ar​	EBE​	Art​
8​	10y/Ci1​	RAM​	Citizenship​
9​	11x/Ci1​	RAM​	Citizenship​
10​	10w/Ci1​	SAH​	Citizenship​
11​

Array formula in E2 copied across and down
=IFERROR(INDEX($B$2:$B$10,MODE(IF($C$2:$C$10=E$1,IF(ISNA(MATCH($B$2:$B$10,E$1:E1,0)),MATCH($B$2:$B$10,$B$2:$B$10,0)+{0,0})))),"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[Fazila]

Thanks Marcelo and sorry to be so awkward but is there a way for the formula to work if the table is as below:

A	B	C	D	E	F	G	H	I	J
Subject	Class 1	Hw	ATL	DTL	Class 2	Hw	ATL	DTL	Class 3
Art	{=INDEX(Teach,MODE(IF(Sub=Sheet4!$A3,MATCH(Teach,Teach,0))))}				Second teacher with second most classes				Third teacher with third most classes
Citizenship	{=INDEX(Teach,MODE(IF(Sub=Sheet4!$A4,MATCH(Teach,Teach,0))))}				As above				As above

<tbody>
</tbody>

Thanks I really appreciate the help with this.

 

[Marcelo Branco]

This layout complicates things a lot!
I suggest that you first create the list, as I suggested above, in an empty area of the worksheet.
Then you can create formulas that referred to this list to bring the results to the appropriate cells in the other worksheet.

M.

 

[Fazila]

This layout complicates things a lot!
I suggest that you first create the list, as I suggested above, in an empty area of the worksheet.
Then you can create formulas that referred to this list to bring the results to the appropriate cells in the other worksheet.

M.

That seems to be the best bet thanks Marcelo.

 

[Marcelo Branco]

You're welcome. Glad to help.

M.