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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Class[/TD]
[TD]Teacher[/TD]
[TD]Subject[/TD]
[/TR]
[TR]
[TD]10x/Ar1[/TD]
[TD]AOW[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]11x/Ar1[/TD]
[TD]AOW[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]11z/Ar1[/TD]
[TD]AOW[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]7L10/Ar[/TD]
[TD]AOW[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]7L3/Ar[/TD]
[TD]EBE[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]7L8/Ar[/TD]
[TD]EBE[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]10y/Ci1[/TD]
[TD]RAM[/TD]
[TD]Citizenship[/TD]
[/TR]
[TR]
[TD]11x/Ci1[/TD]
[TD]RAM[/TD]
[TD]Citizenship[/TD]
[/TR]
[TR]
[TD]10w/Ci1[/TD]
[TD]SAH[/TD]
[TD]Citizenship[/TD]
[/TR]
</tbody>[/TABLE]

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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Class​

[/td][td]

Teacher​

[/td][td]

Subject​

[/td][td][/td][td]

Art​

[/td][td]

Citizenship​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

10x/Ar1​

[/td][td]

AOW​

[/td][td]

Art​

[/td][td][/td][td]

AOW​

[/td][td]

RAM​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

11x/Ar1​

[/td][td]

AOW​

[/td][td]

Art​

[/td][td][/td][td]

EBE​

[/td][td]

SAH​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

11z/Ar1​

[/td][td]

AOW​

[/td][td]

Art​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

7L10/Ar​

[/td][td]

AOW​

[/td][td]

Art​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

7L3/Ar​

[/td][td]

EBE​

[/td][td]

Art​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

7L8/Ar​

[/td][td]

EBE​

[/td][td]

Art​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

10y/Ci1​

[/td][td]

RAM​

[/td][td]

Citizenship​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

11x/Ci1​

[/td][td]

RAM​

[/td][td]

Citizenship​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

10w/Ci1​

[/td][td]

SAH​

[/td][td]

Citizenship​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Subject[/TD]
[TD]Class 1[/TD]
[TD]Hw[/TD]
[TD]ATL[/TD]
[TD]DTL[/TD]
[TD]Class 2[/TD]
[TD]Hw[/TD]
[TD]ATL[/TD]
[TD]DTL[/TD]
[TD]Class 3[/TD]
[/TR]
[TR]
[TD]Art[/TD]
[TD]{=INDEX(Teach,MODE(IF(Sub=Sheet4!$A3,MATCH(Teach,Teach,0))))}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Second teacher with second most classes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Third teacher with third most classes[/TD]
[/TR]
[TR]
[TD]Citizenship[/TD]
[TD]{=INDEX(Teach,MODE(IF(Sub=Sheet4!$A4,MATCH(Teach,Teach,0))))}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]As above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]As above[/TD]
[/TR]
</tbody>[/TABLE]

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.