Forum Discussion
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 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
3 Replies
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.
- Insert Check boxes as shown, and by using property link cells with them.
- 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.
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.
- Thanks a lot. Let me check and confirm if it works for me.
