Can I put a If statement before I execute my RANK?

[RudeBoy]

I have a RANK Formula like this: =RANK(J12,J$9:J$728)
I am doing a Sort so if I have a Duplicate item in Column A of that same row of the Rank I do not want it to execute the RANK

So Like this if A12 is equal = to A11 Then "DUP"

How can I combine a test into the Rank Formula & do not execute it if previous line has a DUPE in Column A?

Thanks for your help

 

[RudeBoy]

I have a RANK Formula like this: =RANK(J12,J$9:J$728)
I am doing a Sort so if I have a Duplicate item in Column A of that same row of the Rank I do not want it to execute the RANK

So Like this if A12 is equal = to A11 Then "DUP"

How can I combine a test into the Rank Formula & do not execute it if previous line has a DUPE in Column A?

Thanks for your help

I got the Formula working but it did not re-rank after it got rid of DUP it did this - It skipped #4 that is where the DUP was
I have some Visial Basic running on that tab to auto sort rank like this for that tab:
'
rowcount = ActiveSheet.UsedRange.Rows.Count
Range(Cells(9, 1), Cells(rowcount, 22)).Select

Selection.Sort Key1:=Range("K9"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Activate
End Sub

$ POS $
RANK
01
02
03
05

 

[RudeBoy]

I got the Formula working but it did not re-rank after it got rid of DUP it did this - It skipped #4 that is where the DUP was
I have some Visial Basic running on that tab to auto sort rank like this for that tab:
'
rowcount = ActiveSheet.UsedRange.Rows.Count
Range(Cells(9, 1), Cells(rowcount, 22)).Select

Selection.Sort Key1:=Range("K9"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Activate
End Sub

$ POS $
RANK
01
02
03
05

Here is the Formula I got working but just not re-ranking after DUP gone
=IF($B12=$B11,"DUP",RANK(J12,J$9:J$728))

 

[bebo021999]

Is it are you looking for?

Book1
	B								J	K
11	A								1	6
12	B								2	5
13	C								3	4
14	C								4	DUP
15	A								5	DUP
16	D								6	3
17	E								7	2
18	F								8	1
Sheet1

Cell Formulas
Range	Formula
K11:K18	K11	=IF(COUNTIF($B$11:B11,B11)>1,"DUP",SUMPRODUCT(--($J$11:$J$18*(MATCH($B$11:$B$18,$B$11:$B$18,0)=ROW($B$11:$B$18)-MIN(ROW($B$11:$B$18))+1)>=J11)))