Help with if formula based on values

kypok999

New Member
Joined
Oct 20, 2017
Messages
41
Hi Guys need help with below formula as per image current formula is looking into ranking based on A2 which is fine but if I get duplicate value in column AM like line 5 and 6 I want it to give the same ranking in this case it would be 4 and 4

Thank you
WPaTs


Image:

WPaTs
https://imgur.com/a/WPaTs
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
981981
902902####
603603
554554
555554
466466
337337
208208
using large function, then looking for duplications
and if found use the rank of the first one
####
=IF(G2=G1,F1,F2)

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
You're not showing what formula is there at the moment but perhaps for AM2:

Code:
=SUMPRODUCT(($A:$A=$A2)*($AL:$AL>$AL2))+1

It would be better to restrict $A:$A and $AL:$AL to include only the populated data (e.g. $A$2:$A$1000) as these will be slow when they're scanning the whole column.

WBD
 
Upvote 0
981981
902902####
603603
554554
555554
466466
337337
208208
using large function, then looking for duplications
and if found use the rank of the first one
####
=IF(G2=G1,F1,F2)

<tbody>
</tbody>

Thank you for reply I need to show instead of 6 as ranked as 5th after 4, 4
 
Upvote 0
Ah. So they're currently sorted anyway. In which case in AM2:

Code:
=IF(A1=A2,IF(AL1=AL2,AM1,AM1+1),1)

That will give you 1,2,2,3,3,4,4,5,6,7,8 in your data as shown. Is that what you want? I you want 1,2,2,4,4,6,6,8,9,10,11 then it's a little more tricky.

WBD
 
Upvote 0
You're not showing what formula is there at the moment but perhaps for AM2:

Code:
=SUMPRODUCT(($A:$A=$A2)*($AL:$AL>$AL2))+1

It would be better to restrict $A:$A and $AL:$AL to include only the populated data (e.g. $A$2:$A$1000) as these will be slow when they're scanning the whole column.

WBD

its just crashed my exel
 
Upvote 0
Ah. So they're currently sorted anyway. In which case in AM2:

Code:
=IF(A1=A2,IF(AL1=AL2,AM1,AM1+1),1)

That will give you 1,2,2,3,3,4,4,5,6,7,8 in your data as shown. Is that what you want? I you want 1,2,2,4,4,6,6,8,9,10,11 then it's a little more tricky.

WBD

Hi WBD I need following as currently its 1,2,2,4,4,6,6,8,9,10,11 but I want 1,2,2,3,3,4,4,5,6,7,8

https://imgur.com/a/RAJ5p
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top