Avoid ranking duplicates in excel, checkbox to avoid ranking

%3CLINGO-SUB%20id%3D%22lingo-sub-1698254%22%20slang%3D%22en-US%22%3EAvoid%20ranking%20duplicates%20in%20excel%2C%20checkbox%20to%20avoid%20ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698254%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20rank%20the%20products%20based%20on%20their%20launch%20month%20and%20year.%20But%20in%20case%2C%20any%202-3%20products%20are%20launching%20in%20the%20same%20month%2C%20I%20want%20to%20club%20them%20into%20a%20single%20rank.%20For%20e.g.%20if%20product%20a%20%26amp%3B%20b%20are%20launching%20on%20Mar'21%2C%20then%20both%20should%20be%20ranked%20same.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIn%20addition%2C%20if%20the%20checkbox%20is%20not%20selected%20then%20it%20should%20not%20be%20counted%20in%20the%20ranking%20at%20all.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20given%20a%20snapshot%20of%20the%20screen.%20Please%20suggest%20formula%20for%20range%20D18%3AD39.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3ENishant%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20error%204.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220893i176B831AF6F1937B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Excel%20error%204.JPG%22%20alt%3D%22Excel%20error%204.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1698254%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1700604%22%20slang%3D%22en-US%22%3ERe%3A%20Avoid%20ranking%20duplicates%20in%20excel%2C%20checkbox%20to%20avoid%20ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1700604%22%20slang%3D%22en-US%22%3EThanks%20a%20lot.%20Let%20me%20check%20and%20confirm%20if%20it%20works%20for%20me.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698404%22%20slang%3D%22en-US%22%3ERe%3A%20Avoid%20ranking%20duplicates%20in%20excel%2C%20checkbox%20to%20avoid%20ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796836%22%20target%3D%22_blank%22%3E%40Nishantu309034%3C%2FA%3E%26nbsp%3B%2C%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI%20would%20like%20to%20suggest%20a%20Non%20VBA%26nbsp%3Bmethod%2C%20finds%20Rank%2C%20and%20skips%2C%20if%20Check%20%3CSPAN%3Eb%3C%2FSPAN%3Eox%20is%20not%20clicked.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1600795473764.png%22%20style%3D%22width%3A%20482px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F221164iEADB7E73884823C8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1600795473764.png%22%20alt%3D%22Rajesh-S_0-1600795473764.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CEM%3ETo%20solve%20the%20issue%20two%20helper%20column%20are%20required.%3C%2FEM%3E%3C%2FLI%3E%3C%2FUL%3E%3COL%3E%3CLI%3EInsert%20Check%20boxes%20as%20shown%2C%20and%20by%20using%20property%20link%20cells%20with%20them.%3C%2FLI%3E%3CLI%3EYou%20find%20TRUE%20in%20column%20J%20if%20Check%26nbsp%3Bbox%20is%20clicked%2C%20FALSE%20if%20not.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFormula%20in%20cell%20M2%2C%20and%20fill%20it%20down.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(J2%3DTRUE%2CL2%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFormula%20in%20cell%20N2%2C%20fill%20it%20down.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSMALL(%24M%242%3A%24M%2411%2CROW()-ROW(%24A%241))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EFinal%20formula%20in%20cell%20O2%2C%20fill%20it%20down.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(MATCH(%24M2%2C%24N%242%3A%24N%2411%2C0)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EKeep%20clicking%20check%20box%2C%20accordingly%20Excel%20will%20reshuffle%20the%20Rank%20in%20column%20O.%3C%2FLI%3E%3CLI%3EFor%20neatness%20later%20on%20you%20may%20hide%20helper%20columns.%3C%2FLI%3E%3CLI%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698387%22%20slang%3D%22en-US%22%3ERe%3A%20Avoid%20ranking%20duplicates%20in%20excel%2C%20checkbox%20to%20avoid%20ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698387%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796836%22%20target%3D%22_blank%22%3E%40Nishantu309034%3C%2FA%3E%26nbsp%3BCreated%20a%20small%20model%2C%20roughly%20based%20on%20the%20example%20in%20your%20picture.%20It%20demonstrates%20what%20you%20need%20to%20do.%20Used%20a%20few%20helper%20columns%20to%20keep%20the%20formulae%20easier%20to%20read%20and%20maintain.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 

Excel error 4.JPG

3 Replies

@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. 

@Nishantu309034 ,,,

 

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

 

 
 

Rajesh-S_0-1600795473764.png

 

 

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.
Thanks a lot. Let me check and confirm if it works for me.