# 2nd most common entry if

#### Fazila

##### Board Regular
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

Last edited:

#### Marcelo Branco

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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.

M.

1,082,373
Messages
5,365,070
Members
400,822
Latest member
Aldebaran13

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...