Forum Discussion

Nishantu309034's avatar
Nishantu309034
Copper Contributor
Sep 22, 2020

Avoid ranking duplicates in excel, checkbox to avoid ranking

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 ...
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Sep 22, 2020

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.

Resources