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

L

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
256
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]
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Rank Function
Replies
4
Views
326
vmjan02
V
A
  • Question Question
VBA to Categorise based on columns
Replies
3
Views
144
Peter_SSs
Peter_SSs
D
  • Solved
Using COUNTIFS to Rank Within Nested IF, THEN
Replies
4
Views
546
diderooy
D
D
  • Question Question
Formula to create a list based
Replies
2
Views
91
myall_blues
myall_blues
S
  • Solved
Ranking Array need to move as per the data
2
Replies
16
Views
493
sksanjeev786
S

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

Share this page

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