Avoid ranking duplicates in excel, checkbox to avoid ranking

[Nishantu309034]

Hi all, 

I want to rank the products based on their launch month and year. But in case, any 2-3 products are launching in the same month, I want to club them into a single rank. For e.g. if product a & b are launching on Mar'21, then both should be ranked same. 

In addition, if the checkbox is not selected then it should not be counted in the ranking at all. 

I have given a snapshot of the screen. Please suggest formula for range D18:D39.  

Regards

Nishant 

 

[#2]

@Nishantu309034 Created a small model, roughly based on the example in your picture. It demonstrates what you need to do. Used a few helper columns to keep the formulae easier to read and maintain. 

[#3]

@Nishantu309034 ,,,

I would like to suggest a Non VBA method, finds Rank, and skips, if Check box is not clicked.

How it works:

• To solve the issue two helper column are required.

1. Insert Check boxes as shown, and by using property link cells with them.
2. You find TRUE in column J if Check box is clicked, FALSE if not.

• Formula in cell M2, and fill it down.

=IF(J2=TRUE,L2,"")

• Formula in cell N2, fill it down.

=SMALL($M$2:$M$11,ROW()-ROW($A$1))

• Final formula in cell O2, fill it down.

=IFERROR(MATCH($M2,$N$2:$N$11,0),"")

N.B.

• Keep clicking check box, accordingly Excel will reshuffle the Rank in column O.
• For neatness later on you may hide helper columns.
• Adjust cell references in the formula as needed.

[#4]

Thanks a lot. Let me check and confirm if it works for me.