Thanks:  0
Likes:  0

# Thread: Help with if formula based on values

1. ## Help with if formula based on values

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

Image:

https://imgur.com/a/WPaTs

2. ## Re: Help with if formula based on values

 98 1 98 1 90 2 90 2 #### 60 3 60 3 55 4 55 4 55 5 55 4 46 6 46 6 33 7 33 7 20 8 20 8 using large function, then looking for duplications and if found use the rank of the first one #### =IF(G2=G1,F1,F2)

3. ## Re: Help with if formula based on values

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

4. ## Re: Help with if formula based on values

current formula:

https://imgur.com/a/lB1rA

5. ## Re: Help with if formula based on values

Thank you for reply I need to show instead of 6 as ranked as 5th after 4, 4

6. ## Re: Help with if formula based on values

Originally Posted by oldbrewer
 98 1 98 1 90 2 90 2 #### 60 3 60 3 55 4 55 4 55 5 55 4 46 6 46 6 33 7 33 7 20 8 20 8 using large function, then looking for duplications and if found use the rank of the first one #### =IF(G2=G1,F1,F2)
Thank you for reply I need to show instead of 6 as ranked as 5th after 4, 4

7. ## Re: Help with if formula based on values

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

8. ## Re: Help with if formula based on values

Originally Posted by wideboydixon
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

9. ## Re: Help with if formula based on values

Originally Posted by kypok999
its just crashed my exel
Boom! Try the alternative formula then

WBD

10. ## Re: Help with if formula based on values

Originally Posted by wideboydixon
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