Help require to modify rank formula for subject groups

Hi friends,
There are groups of subjects in column ‘I’. I want to give rank as per the subject group in column ‘n’.

The problem is that there are two groups for English ‘english1 and english2’ and for Hindi ‘hindi1 and hindi2’. I want to treat these groups as one and rank according to it.

English1 and English2 one subject group
Hindi1 and Hindi2 one subject group

Please see the attachment and suggest me amendment in the rank formula in column ‘N’.

Any help will be appreciated.

Thanking you.

In N4 try

=IF(OR(I4="",M4=""),"",1+SUMPRODUCT((LEFT($I$4:$I$20,LEN($I$4:$I$20)-1)=LEFT(I4,LEN(I4)-1))*($M$4:$M$20>M4)))

Your ranges should be restricted to non-blank cells only

Hi Ace_XL,
Thank you. It's working fine for restricted ranges only. The ranges are not fixed. It varies time to time as per the divisions in the standard and subjects. So I don't want any restriction in ranges. Whether it is blank of nonblank the formula should work.

Please do the amendment to remove the restriction of ranges.

Thank you.

is it ok to insert one more column for subject

Subject Subject type
english1 english
english1 english
english1 english
english2 english
english2 english
english2 english
hindi1 hindi
Hindi1 Hindi
hindi2 hindi
Hindi2 Hindi
marathi marathi
marathi marathi
marathi marathi
maths maths
maths maths
maths maths
maths maths

No, The data is retrieve from several result sheets automatically by a programme so it's not possible to insert a helping column.

I want amendment in this situation only to avoide any further complications in my main programme.

Thanking you.

Try

=IF(OR(I4="",M4=""),"",1+SUMPRODUCT((LEFT($I$4:INDEX($I$4:$I$100,COUNTA($I$4:$I$100)),LEN($I$4:INDEX($I$4:$I$100,COUNTA($I$4:$I$100)))-1)=LEFT(I4,LEN(I4)-1))*($M$4:INDEX($M$4:$M$100,COUNTA($I$4:$I$100))>M4)))

Originally Posted by Ace_XL

Try

=IF(OR(I4="",M4=""),"",1+SUMPRODUCT((LEFT($I$4:INDEX($I$4:$I$100,COUNTA($I$4:$I$100)),LEN($I$4:INDEX($I$4:$I$100,COUNTA($I$4:$I$100)))-1)=LEFT(I4,LEN(I4)-1))*($M$4:INDEX($M$4:$M$100,COUNTA($I$4:$I$100))>M4)))

@ Ace_XL
If subject entered like
maths1
maths1
maths
maths
then it will be problem

Hi ase_xl,
Ok! It's working fine but if there is a word instead of number 1, 2 then it's giving error. I have subjects like

subject
English general
English general
English general
English special
English special
English special

in my original workbook. In this case it's not working. As Siva stated it's also not working if there are more than 2 subjects having 1, 2 after subject name.

Mainly I have a problem of word after main subject like mentioned above. Please suggest me a correction to work the formula if there is a word after main subject.

Sorry for trouble.

Thank you.

Hi excel expert friends,

Bump!

Due to this problem my work is stopped so I humbly request you to look into this problem and suggest me a concrete solution to achieve the target of proper ranking as per my requirement. I'm quite optimistic to receive a solution today from you excel experts positively.

Waiting to receive a solution.

Thanking you.

Hi Lewis,
Excellent job done. Thank you. It's working fine but if there is any word after main subject then it gives wrong ranking. Please see the output column 'o'.

I have inserted a small code with a formula to achieve it suggested by Jbealcare which gives a correct output in rank.

Everything else is perfect.

I appreciate you for the efforts taken to create a such challenging macro to achieve the target of ranking.

Thanking you,

Hi Lewis,
Excellent! Excellent! Excellent!

What a dynamic creation for a challenging dynamic ranking. I salute your knowledge of vba.

I appreciate you for the excellent creation to achieve the target.

Thank you.