1, 2, 3.. IF formula and Rank If Uniques

[Lewzerrrr]

Hi,

I'd have 2 separate data sources, 1 is a list of values that's automatically sorted highest to lowest. I have a formula that puts a Y next to a value if it meets a criteria. (This is in column B). What I'd like in column A is to place a 1, 2, 3.. but skip if the value in B is a Y. I only need to go up to 20 so it can be as complex as need be.

The 2nd is a list of values that isn't sorted at all, it's a list of serial #s with their sales next to. Again in column B is Y's if they meet criteria. I want to rank only the NON Y serials whilst also ignoring duplicates. Again I only really need to go up to rank 20 so can be a complex formula.


Here's 2 tables and the results I'd like to see.
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]95[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]11[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]22[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]95[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]46[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]92[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]83[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]73[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]73[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]95[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]69[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]64[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]64[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]95[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]50[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]46[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]46[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]92[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]46[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]69[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]37[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]31[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]19[/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]83[/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]19[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]14[/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]12[/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]37[/TD]
[/TR]
[TR]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]31[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Mahoney]

I assume that row 1 is headers and Cell A2 is the first Rank value. In cell A2 put:
=IF(AND(B2<>"Y",C2<>""),MAX($A$1:A1)+1,"")

Jeff