Rank across Columns with no duplicates

kckliodna

New Member
Joined
Jul 30, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
This is just a small portion of my spreadsheet, it's fairly large, so I did a Snip-IT.
There are 23 cells that I want ranked 1-23, with any duplicate values ranking with different numbers, so there are no 2 #3s or "3.5" or 3 #6s as my snipit shows. For this example, cells EH14, EO14, EV14, FC14 - I need the Rank to value where it doesn't do duplicates. So EH14 should be 4, EO14 should be 5, EV14 should be 6, and FC14 should be 7. The cells to rank are the values under "TA" which are EN13, EU13, FB13, and FI13. If you need the other "3.5" cells, they are EA14 and EG13.
I have tried these formulas to no avail. For some reason, the beginning of the spreadsheet I did the first formula and it worked fine, but when I started adding more people, all the people at the end started showing the .5 or duplicate numbers. Can't figure that out either but if I can get the right formula, hopefully that won't matter.
Thanks in advance for any help/direction you can give me.
=RANK.AVG(EN13,(K$13,R$13,Y$13,AF$13,AM$13,AT$13,BA$13,BH$13,BO$13,BV$13,CC$13,CJ$13,CQ$13,CX$13,DE$13,DL$13,DS$13,DZ$13,EG$13,EN$13,EU$13,FB$13,FI$13))

=RANK.AVG(EN13,(K$13,R$13,Y$13,AF$13,AM$13,AT$13,BA$13,BH$13,BO$13,BV$13,CC$13,CJ$13,CQ$13,CX$13,DE$13,DL$13,DS$13,DZ$13,EG$13,EN$13,EU$13,FB$13,FI$13))+COUNTIF($$EN$13:EN13,EN13)-1

=RANK.EQ(EU13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIF($EU$13:EU13,EU13)-1
1691714974927.png
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try it like
Excel Formula:
=RANK.EQ(K13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($K$13:K13,K13,$K$12:K12,"TA")-1
 
Upvote 0
Try it like
Excel Formula:
=RANK.EQ(K13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($K$13:K13,K13,$K$12:K12,"TA")-1
Unfortunately that did not work either. Thanks for trying.
 
Upvote 0
OT 8-14 mrexcel.xlsx
EAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZFAFBFCFDFEFFFGFHFI
12RteLISTNS DAYTOTOTWTUTARteLISTNS DAYTOTOTWTUTARteLISTNS DAYTOTOTWTUTARteLISTNS DAYTOTOTWTUTARteLISTNS DAYTOTOTWTUTA
1395WABrown3370.170.0070.17T6-1NLYellow3370.170.0070.17T6-210Black3264.170.0064.17T6-3WAGreen3264.170.0064.17T6-4NLBlack3264.170.0064.17
143.00Geno3.00Bill8Janice8Relijah8Jackie
15SDOOT WUKeyODTTASDOOT WUKeyODTTASDOOT WUKeyODTTASDOOT WUKeyODTTASDOOT WUKeyODTTA
Quarter
Cell Formulas
RangeFormula
EB13EB13=IF(EA13=Schedule!B3,Schedule!D3,IF(EA13=Schedule!B4,Schedule!D4,IF(EA13=Schedule!B5,Schedule!D5,IF(EA13=Schedule!B6,Schedule!D6,IF(EA13=Schedule!B7,Schedule!D7,IF(EA13=Schedule!B8,Schedule!D8,IF(EA13=Schedule!B9,Schedule!D9,IF(EA13=Schedule!B10,Schedule!D10,IF(EA13=Schedule!B11,Schedule!D11,IF(EA13=Schedule!B12,Schedule!D12,IF(EA13=Schedule!B13,Schedule!D13,IF(EA13=Schedule!B14,Schedule!D14,IF(EA13=Schedule!B15,Schedule!D15,IF(EA13=Schedule!B16,Schedule!D16,IF(EA13=Schedule!B17,Schedule!D17,IF(EA13=Schedule!B18,Schedule!D18,IF(EA13=Schedule!B19,Schedule!D19,IF(EA13=Schedule!B20,Schedule!D20,IF(EA13=Schedule!B21,Schedule!D21,IF(EA13=Schedule!B22,Schedule!D22,IF(EA13=Schedule!B23,Schedule!D23,IF(EA13=Schedule!B24,Schedule!D24,IF(EA13=Schedule!B25,Schedule!D25,IF(EA13=Schedule!B26,Schedule!D26))))))))))))))))))))))))
EC13EC13=IF(EA13=Schedule!B3,Schedule!A3,IF(EA13=Schedule!B4,Schedule!A4,IF(EA13=Schedule!B5,Schedule!A5,IF(EA13=Schedule!B6,Schedule!A6,IF(EA13=Schedule!B7,Schedule!A7,IF(EA13=Schedule!B8,Schedule!A8,IF(EA13=Schedule!B9,Schedule!A9,IF(EA13=Schedule!B10,Schedule!A10,IF(EA13=Schedule!B11,Schedule!A11,IF(EA13=Schedule!B12,Schedule!A12,IF(EA13=Schedule!B13,Schedule!A13,IF(EA13=Schedule!B14,Schedule!A14,IF(EA13=Schedule!B15,Schedule!A15,IF(EA13=Schedule!B16,Schedule!A16,IF(EA13=Schedule!B17,Schedule!A17,IF(EA13=Schedule!B18,Schedule!A18,IF(EA13=Schedule!B19,Schedule!A19,IF(EA13=Schedule!B20,Schedule!A20,IF(EA13=Schedule!B21,Schedule!A21,IF(EA13=Schedule!B22,Schedule!A22,IF(EA13=Schedule!B23,Schedule!A23,IF(EA13=Schedule!B24,Schedule!A24,IF(EA13=Schedule!B25,Schedule!A25,IF(EA13=Schedule!B26,Schedule!A26))))))))))))))))))))))))
ED13,FF13,EY13,ER13,EK13ED13=SUMIF(EE16:EE113,"<>")
EE13,FG13,EZ13,ES13,EL13EE13=SUMIF(EA16:EB113,"<>")
EF13,FH13,FA13,ET13,EM13EF13=SUMIF(EC16:EC113,"<>")
EG13,FI13,FB13,EU13,EN13EG13=EG113
EI13EI13=IF(EH13=Schedule!B3,Schedule!D3,IF(EH13=Schedule!B4,Schedule!D4,IF(EH13=Schedule!B5,Schedule!D5,IF(EH13=Schedule!B6,Schedule!D6,IF(EH13=Schedule!B7,Schedule!D7,IF(EH13=Schedule!B8,Schedule!D8,IF(EH13=Schedule!B9,Schedule!D9,IF(EH13=Schedule!B10,Schedule!D10,IF(EH13=Schedule!B11,Schedule!D11,IF(EH13=Schedule!B12,Schedule!D12,IF(EH13=Schedule!B13,Schedule!D13,IF(EH13=Schedule!B14,Schedule!D14,IF(EH13=Schedule!B15,Schedule!D15,IF(EH13=Schedule!B16,Schedule!D16,IF(EH13=Schedule!B17,Schedule!D17,IF(EH13=Schedule!B18,Schedule!D18,IF(EH13=Schedule!B19,Schedule!D19,IF(EH13=Schedule!B20,Schedule!D20,IF(EH13=Schedule!B21,Schedule!D21,IF(EH13=Schedule!B22,Schedule!D22,IF(EH13=Schedule!B23,Schedule!D23,IF(EH13=Schedule!B24,Schedule!D24,IF(EH13=Schedule!B25,Schedule!D25,IF(EH13=Schedule!B26,Schedule!D26))))))))))))))))))))))))
EJ13EJ13=IF(EH13=Schedule!B3,Schedule!A3,IF(EH13=Schedule!B4,Schedule!A4,IF(EH13=Schedule!B5,Schedule!A5,IF(EH13=Schedule!B6,Schedule!A6,IF(EH13=Schedule!B7,Schedule!A7,IF(EH13=Schedule!B8,Schedule!A8,IF(EH13=Schedule!B9,Schedule!A9,IF(EH13=Schedule!B10,Schedule!A10,IF(EH13=Schedule!B11,Schedule!A11,IF(EH13=Schedule!B12,Schedule!A12,IF(EH13=Schedule!B13,Schedule!A13,IF(EH13=Schedule!B14,Schedule!A14,IF(EH13=Schedule!B15,Schedule!A15,IF(EH13=Schedule!B16,Schedule!A16,IF(EH13=Schedule!B17,Schedule!A17,IF(EH13=Schedule!B18,Schedule!A18,IF(EH13=Schedule!B19,Schedule!A19,IF(EH13=Schedule!B20,Schedule!A20,IF(EH13=Schedule!B21,Schedule!A21,IF(EH13=Schedule!B22,Schedule!A22,IF(EH13=Schedule!B23,Schedule!A23,IF(EH13=Schedule!B24,Schedule!A24,IF(EH13=Schedule!B25,Schedule!A25,IF(EH13=Schedule!B26,Schedule!A26))))))))))))))))))))))))
EP13EP13=IF(EO13=Schedule!B3,Schedule!D3,IF(EO13=Schedule!B4,Schedule!D4,IF(EO13=Schedule!B5,Schedule!D5,IF(EO13=Schedule!B6,Schedule!D6,IF(EO13=Schedule!B7,Schedule!D7,IF(EO13=Schedule!B8,Schedule!D8,IF(EO13=Schedule!B9,Schedule!D9,IF(EO13=Schedule!B10,Schedule!D10,IF(EO13=Schedule!B11,Schedule!D11,IF(EO13=Schedule!B12,Schedule!D12,IF(EO13=Schedule!B13,Schedule!D13,IF(EO13=Schedule!B14,Schedule!D14,IF(EO13=Schedule!B15,Schedule!D15,IF(EO13=Schedule!B16,Schedule!D16,IF(EO13=Schedule!B17,Schedule!D17,IF(EO13=Schedule!B18,Schedule!D18,IF(EO13=Schedule!B19,Schedule!D19,IF(EO13=Schedule!B20,Schedule!D20,IF(EO13=Schedule!B21,Schedule!D21,IF(EO13=Schedule!B22,Schedule!D22,IF(EO13=Schedule!B23,Schedule!D23,IF(EO13=Schedule!B24,Schedule!D24,IF(EO13=Schedule!B25,Schedule!D25,IF(EO13=Schedule!B26,Schedule!D26))))))))))))))))))))))))
EQ13EQ13=IF(EO13=Schedule!B3,Schedule!A3,IF(EO13=Schedule!B4,Schedule!A4,IF(EO13=Schedule!B5,Schedule!A5,IF(EO13=Schedule!B6,Schedule!A6,IF(EO13=Schedule!B7,Schedule!A7,IF(EO13=Schedule!B8,Schedule!A8,IF(EO13=Schedule!B9,Schedule!A9,IF(EO13=Schedule!B10,Schedule!A10,IF(EO13=Schedule!B11,Schedule!A11,IF(EO13=Schedule!B12,Schedule!A12,IF(EO13=Schedule!B13,Schedule!A13,IF(EO13=Schedule!B14,Schedule!A14,IF(EO13=Schedule!B15,Schedule!A15,IF(EO13=Schedule!B16,Schedule!A16,IF(EO13=Schedule!B17,Schedule!A17,IF(EO13=Schedule!B18,Schedule!A18,IF(EO13=Schedule!B19,Schedule!A19,IF(EO13=Schedule!B20,Schedule!A20,IF(EO13=Schedule!B21,Schedule!A21,IF(EO13=Schedule!B22,Schedule!A22,IF(EO13=Schedule!B23,Schedule!A23,IF(EO13=Schedule!B24,Schedule!A24,IF(EO13=Schedule!B25,Schedule!A25,IF(EO13=Schedule!B26,Schedule!A26))))))))))))))))))))))))
EW13EW13=IF(EV13=Schedule!B3,Schedule!D3,IF(EV13=Schedule!B4,Schedule!D4,IF(EV13=Schedule!B5,Schedule!D5,IF(EV13=Schedule!B6,Schedule!D6,IF(EV13=Schedule!B7,Schedule!D7,IF(EV13=Schedule!B8,Schedule!D8,IF(EV13=Schedule!B9,Schedule!D9,IF(EV13=Schedule!B10,Schedule!D10,IF(EV13=Schedule!B11,Schedule!D11,IF(EV13=Schedule!B12,Schedule!D12,IF(EV13=Schedule!B13,Schedule!D13,IF(EV13=Schedule!B14,Schedule!D14,IF(EV13=Schedule!B15,Schedule!D15,IF(EV13=Schedule!B16,Schedule!D16,IF(EV13=Schedule!B17,Schedule!D17,IF(EV13=Schedule!B18,Schedule!D18,IF(EV13=Schedule!B19,Schedule!D19,IF(EV13=Schedule!B20,Schedule!D20,IF(EV13=Schedule!B21,Schedule!D21,IF(EV13=Schedule!B22,Schedule!D22,IF(EV13=Schedule!B23,Schedule!D23,IF(EV13=Schedule!B24,Schedule!D24,IF(EV13=Schedule!B25,Schedule!D25,IF(EV13=Schedule!B26,Schedule!D26))))))))))))))))))))))))
EX13EX13=IF(EV13=Schedule!B3,Schedule!A3,IF(EV13=Schedule!B4,Schedule!A4,IF(EV13=Schedule!B5,Schedule!A5,IF(EV13=Schedule!B6,Schedule!A6,IF(EV13=Schedule!B7,Schedule!A7,IF(EV13=Schedule!B8,Schedule!A8,IF(EV13=Schedule!B9,Schedule!A9,IF(EV13=Schedule!B10,Schedule!A10,IF(EV13=Schedule!B11,Schedule!A11,IF(EV13=Schedule!B12,Schedule!A12,IF(EV13=Schedule!B13,Schedule!A13,IF(EV13=Schedule!B14,Schedule!A14,IF(EV13=Schedule!B15,Schedule!A15,IF(EV13=Schedule!B16,Schedule!A16,IF(EV13=Schedule!B17,Schedule!A17,IF(EV13=Schedule!B18,Schedule!A18,IF(EV13=Schedule!B19,Schedule!A19,IF(EV13=Schedule!B20,Schedule!A20,IF(EV13=Schedule!B21,Schedule!A21,IF(EV13=Schedule!B22,Schedule!A22,IF(EV13=Schedule!B23,Schedule!A23,IF(EV13=Schedule!B24,Schedule!A24,IF(EV13=Schedule!B25,Schedule!A25,IF(EV13=Schedule!B26,Schedule!A26))))))))))))))))))))))))
FD13FD13=IF(FC13=Schedule!B3,Schedule!D3,IF(FC13=Schedule!B4,Schedule!D4,IF(FC13=Schedule!B5,Schedule!D5,IF(FC13=Schedule!B6,Schedule!D6,IF(FC13=Schedule!B7,Schedule!D7,IF(FC13=Schedule!B8,Schedule!D8,IF(FC13=Schedule!B9,Schedule!D9,IF(FC13=Schedule!B10,Schedule!D10,IF(FC13=Schedule!B11,Schedule!D11,IF(FC13=Schedule!B12,Schedule!D12,IF(FC13=Schedule!B13,Schedule!D13,IF(FC13=Schedule!B14,Schedule!D14,IF(FC13=Schedule!B15,Schedule!D15,IF(FC13=Schedule!B16,Schedule!D16,IF(FC13=Schedule!B17,Schedule!D17,IF(FC13=Schedule!B18,Schedule!D18,IF(FC13=Schedule!B19,Schedule!D19,IF(FC13=Schedule!B20,Schedule!D20,IF(FC13=Schedule!B21,Schedule!D21,IF(FC13=Schedule!B22,Schedule!D22,IF(FC13=Schedule!B23,Schedule!D23,IF(FC13=Schedule!B24,Schedule!D24,IF(FC13=Schedule!B25,Schedule!D25,IF(FC13=Schedule!B26,Schedule!D26))))))))))))))))))))))))
FE13FE13=IF(FC13=Schedule!B3,Schedule!A3,IF(FC13=Schedule!B4,Schedule!A4,IF(FC13=Schedule!B5,Schedule!A5,IF(FC13=Schedule!B6,Schedule!A6,IF(FC13=Schedule!B7,Schedule!A7,IF(FC13=Schedule!B8,Schedule!A8,IF(FC13=Schedule!B9,Schedule!A9,IF(FC13=Schedule!B10,Schedule!A10,IF(FC13=Schedule!B11,Schedule!A11,IF(FC13=Schedule!B12,Schedule!A12,IF(FC13=Schedule!B13,Schedule!A13,IF(FC13=Schedule!B14,Schedule!A14,IF(FC13=Schedule!B15,Schedule!A15,IF(FC13=Schedule!B16,Schedule!A16,IF(FC13=Schedule!B17,Schedule!A17,IF(FC13=Schedule!B18,Schedule!A18,IF(FC13=Schedule!B19,Schedule!A19,IF(FC13=Schedule!B20,Schedule!A20,IF(FC13=Schedule!B21,Schedule!A21,IF(FC13=Schedule!B22,Schedule!A22,IF(FC13=Schedule!B23,Schedule!A23,IF(FC13=Schedule!B24,Schedule!A24,IF(FC13=Schedule!B25,Schedule!A25,IF(FC13=Schedule!B26,Schedule!A26))))))))))))))))))))))))
EA14EA14=RANK.EQ(EG13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($EG$13:EG13,EG13,$EG$12:EG12,"TA")-1
EB14EB14=IF(EA13=Schedule!B3,Schedule!C3,IF(EA13=Schedule!B4,Schedule!C4,IF(EA13=Schedule!B5,Schedule!C5,IF(EA13=Schedule!B6,Schedule!C6,IF(EA13=Schedule!B7,Schedule!C7,IF(EA13=Schedule!B8,Schedule!C8,IF(EA13=Schedule!B9,Schedule!C9,IF(EA13=Schedule!B10,Schedule!C10,IF(EA13=Schedule!B11,Schedule!C11,IF(EA13=Schedule!B12,Schedule!C12,IF(EA13=Schedule!B13,Schedule!C13,IF(EA13=Schedule!B14,Schedule!C14,IF(EA13=Schedule!B15,Schedule!C15,IF(EA13=Schedule!B16,Schedule!C16,IF(EA13=Schedule!B17,Schedule!C17,IF(EA13=Schedule!B18,Schedule!C18,IF(EA13=Schedule!B19,Schedule!C19,IF(EA13=Schedule!B20,Schedule!C20,IF(EA13=Schedule!B21,Schedule!C21,IF(EA13=Schedule!B22,Schedule!C22,IF(EA13=Schedule!B23,Schedule!C23,IF(EA13=Schedule!B24,Schedule!C24,IF(EA13=Schedule!B25,Schedule!C25,IF(EA13=Schedule!B26,Schedule!C26))))))))))))))))))))))))
EH14EH14=RANK.EQ(EN13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($EN$13:EN13,EN13,$EN$12:EN12,"TA")-1
EI14EI14=IF(EH13=Schedule!B3,Schedule!C3,IF(EH13=Schedule!B4,Schedule!C4,IF(EH13=Schedule!B5,Schedule!C5,IF(EH13=Schedule!B6,Schedule!C6,IF(EH13=Schedule!B7,Schedule!C7,IF(EH13=Schedule!B8,Schedule!C8,IF(EH13=Schedule!B9,Schedule!C9,IF(EH13=Schedule!B10,Schedule!C10,IF(EH13=Schedule!B11,Schedule!C11,IF(EH13=Schedule!B12,Schedule!C12,IF(EH13=Schedule!B13,Schedule!C13,IF(EH13=Schedule!B14,Schedule!C14,IF(EH13=Schedule!B15,Schedule!C15,IF(EH13=Schedule!B16,Schedule!C16,IF(EH13=Schedule!B17,Schedule!C17,IF(EH13=Schedule!B18,Schedule!C18,IF(EH13=Schedule!B19,Schedule!C19,IF(EH13=Schedule!B20,Schedule!C20,IF(EH13=Schedule!B21,Schedule!C21,IF(EH13=Schedule!B22,Schedule!C22,IF(EH13=Schedule!B23,Schedule!C23,IF(EH13=Schedule!B24,Schedule!C24,IF(EH13=Schedule!B25,Schedule!C25,IF(CEH3=Schedule!B26,Schedule!C26))))))))))))))))))))))))
EO14EO14=RANK.EQ(EU13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($EU$13:EU13,EU13,$EU$12:EU12,"TA")-1
EP14EP14=IF(EO13=Schedule!B3,Schedule!C3,IF(EO13=Schedule!B4,Schedule!C4,IF(EO13=Schedule!B5,Schedule!C5,IF(EO13=Schedule!B6,Schedule!C6,IF(EO13=Schedule!B7,Schedule!C7,IF(EO13=Schedule!B8,Schedule!C8,IF(EO13=Schedule!B9,Schedule!C9,IF(EO13=Schedule!B10,Schedule!C10,IF(EO13=Schedule!B11,Schedule!C11,IF(EO13=Schedule!B12,Schedule!C12,IF(EO13=Schedule!B13,Schedule!C13,IF(EO13=Schedule!B14,Schedule!C14,IF(EO13=Schedule!B15,Schedule!C15,IF(EO13=Schedule!B16,Schedule!C16,IF(EO13=Schedule!B17,Schedule!C17,IF(EO13=Schedule!B18,Schedule!C18,IF(EO13=Schedule!B19,Schedule!C19,IF(EO13=Schedule!B20,Schedule!C20,IF(EO13=Schedule!B21,Schedule!C21,IF(EO13=Schedule!B22,Schedule!C22,IF(EO13=Schedule!B23,Schedule!C23,IF(EO13=Schedule!B24,Schedule!C24,IF(EO13=Schedule!B25,Schedule!C25,IF(EO13=Schedule!B26,Schedule!C26))))))))))))))))))))))))
EV14EV14=RANK.EQ(FB13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($FB$13:FB13,FB13,$FB$12:FB12,"TA")-1
EW14EW14=IF(EV13=Schedule!B3,Schedule!C3,IF(EV13=Schedule!B4,Schedule!C4,IF(EV13=Schedule!B5,Schedule!C5,IF(EV13=Schedule!B6,Schedule!C6,IF(EV13=Schedule!B7,Schedule!C7,IF(EV13=Schedule!B8,Schedule!C8,IF(EV13=Schedule!B9,Schedule!C9,IF(EV13=Schedule!B10,Schedule!C10,IF(EV13=Schedule!B11,Schedule!C11,IF(EV13=Schedule!B12,Schedule!C12,IF(EV13=Schedule!B13,Schedule!C13,IF(EV13=Schedule!B14,Schedule!C14,IF(EV13=Schedule!B15,Schedule!C15,IF(EV13=Schedule!B16,Schedule!C16,IF(EV13=Schedule!B17,Schedule!C17,IF(EV13=Schedule!B18,Schedule!C18,IF(EV13=Schedule!B19,Schedule!C19,IF(EV13=Schedule!B20,Schedule!C20,IF(EV13=Schedule!B21,Schedule!C21,IF(EV13=Schedule!B22,Schedule!C22,IF(EV13=Schedule!B23,Schedule!C23,IF(EV13=Schedule!B24,Schedule!C24,IF(EV13=Schedule!B25,Schedule!C25,IF(EV13=Schedule!B26,Schedule!C26))))))))))))))))))))))))
FC14FC14=RANK.EQ(FI13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($FI$13:FI13,FI13,$FI$12:FI12,"TA")-1
FD14FD14=IF(FC13=Schedule!B3,Schedule!C3,IF(FC13=Schedule!B4,Schedule!C4,IF(FC13=Schedule!B5,Schedule!C5,IF(FC13=Schedule!B6,Schedule!C6,IF(C13=Schedule!B7,Schedule!C7,IF(FC13=Schedule!B8,Schedule!C8,IF(FC13=Schedule!B9,Schedule!C9,IF(FC13=Schedule!B10,Schedule!C10,IF(FC13=Schedule!B11,Schedule!C11,IF(FC13=Schedule!B12,Schedule!C12,IF(FC13=Schedule!B13,Schedule!C13,IF(FC13=Schedule!B14,Schedule!C14,IF(FC13=Schedule!B15,Schedule!C15,IF(FC13=Schedule!B16,Schedule!C16,IF(FC13=Schedule!B17,Schedule!C17,IF(FC13=Schedule!B18,Schedule!C18,IF(FC13=Schedule!B19,Schedule!C19,IF(FC13=Schedule!B20,Schedule!C20,IF(FC13=Schedule!B21,Schedule!C21,IF(FC13=Schedule!B22,Schedule!C22,IF(FC13=Schedule!B23,Schedule!C23,IF(FC13=Schedule!B24,Schedule!C24,IF(FC13=Schedule!B25,Schedule!C25,IF(FC13=Schedule!B26,Schedule!C26))))))))))))))))))))))))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
FC14:FD14Expression=$FE$13="N/A"textNO
EW14:FB14Expression=$EX$13="N/A"textNO
EO14:EP14Expression=$EQ$13="N/A"textNO
FE13Cell Valuecontains "N/A"textNO
FC14:FD14Expression=$FE$13="Red"textNO
FC14:FD14Expression=$FE$13="Green"textNO
FC14:FD14Expression=$FE$13="Yellow"textNO
FC14:FD14Expression=$FE$13="Brown"textNO
FC14:FD14Expression=$FE$13="Blue"textNO
FC14:FD14Expression=$FE$13="Black"textNO
FE13Cell Valuecontains "GREEN"textYES
FE13Cell Valuecontains "BLUE"textYES
FE13Cell Valuecontains "YELLOW"textYES
FE13Cell Valuecontains "BLACK"textYES
FE13Cell Valuecontains "RED"textYES
FE13Cell Valuecontains "BROWN"textYES
FD13:FE13Cell Valuecontains "GREEN"textNO
FD13:FE13Cell Valuecontains "BLUE"textNO
FD13:FE13Cell Valuecontains "YELLOW"textNO
FD13:FE13Cell Valuecontains "BLACK"textNO
FD13:FE13Cell Valuecontains "RED"textNO
FD13:FE13Cell Valuecontains "BROWN"textNO
EX13Cell Valuecontains "N/A"textNO
EV14:EW14Expression=$EX$13="Red"textNO
EV14:EW14Expression=$EX$13="Green"textNO
EV14:EW14Expression=$EX$13="Yellow"textNO
EV14:EW14Expression=$EX$13="Brown"textNO
EV14:EW14Expression=$EX$13="Blue"textNO
EV14:EW14Expression=$EX$13="Black"textNO
EX13Cell Valuecontains "GREEN"textYES
EX13Cell Valuecontains "BLUE"textYES
EX13Cell Valuecontains "YELLOW"textYES
EX13Cell Valuecontains "BLACK"textYES
EX13Cell Valuecontains "RED"textYES
EX13Cell Valuecontains "BROWN"textYES
EW13:EX13Cell Valuecontains "GREEN"textNO
EW13:EX13Cell Valuecontains "BLUE"textNO
EW13:EX13Cell Valuecontains "YELLOW"textNO
EW13:EX13Cell Valuecontains "BLACK"textNO
EW13:EX13Cell Valuecontains "RED"textNO
EW13:EX13Cell Valuecontains "BROWN"textNO
EQ13Cell Valuecontains "N/A"textNO
EO14:EP14Expression=$EQ$13="Red"textNO
EO14:EP14Expression=$EQ$13="Green"textNO
EO14:EP14Expression=$EQ$13="Yellow"textNO
EO14:EP14Expression=$EQ$13="Brown"textNO
EO14:EP14Expression=$EQ$13="Blue"textNO
EO14:EP14Expression=$EQ$13="Black"textNO
EQ13Cell Valuecontains "GREEN"textYES
EQ13Cell Valuecontains "BLUE"textYES
EQ13Cell Valuecontains "YELLOW"textYES
EQ13Cell Valuecontains "BLACK"textYES
EQ13Cell Valuecontains "RED"textYES
EQ13Cell Valuecontains "BROWN"textYES
EP13:EQ13Cell Valuecontains "GREEN"textNO
EP13:EQ13Cell Valuecontains "BLUE"textNO
EP13:EQ13Cell Valuecontains "YELLOW"textNO
EP13:EQ13Cell Valuecontains "BLACK"textNO
EP13:EQ13Cell Valuecontains "RED"textNO
EP13:EQ13Cell Valuecontains "BROWN"textNO
EJ13Cell Valuecontains "N/A"textNO
EH14:EI14Expression=$EJ$13="Red"textNO
EH14:EI14Expression=$EJ$13="Green"textNO
EH14:EI14Expression=$EJ$13="Yellow"textNO
EH14:EI14Expression=$EJ$13="Brown"textNO
EH14:EI14Expression=$EJ$13="Blue"textNO
EH14:EI14Expression=$EJ$13="Black"textNO
EJ13Cell Valuecontains "GREEN"textYES
EJ13Cell Valuecontains "BLUE"textYES
EJ13Cell Valuecontains "YELLOW"textYES
EJ13Cell Valuecontains "BLACK"textYES
EJ13Cell Valuecontains "RED"textYES
EJ13Cell Valuecontains "BROWN"textYES
EI13:EJ13Cell Valuecontains "GREEN"textNO
EI13:EJ13Cell Valuecontains "BLUE"textNO
EI13:EJ13Cell Valuecontains "YELLOW"textNO
EI13:EJ13Cell Valuecontains "BLACK"textNO
EI13:EJ13Cell Valuecontains "RED"textNO
EI13:EJ13Cell Valuecontains "BROWN"textNO
EC13Cell Valuecontains "N/A"textNO
EA14:EB14Expression=$EC$13="RED"textNO
EA14:EB14Expression=$EC$13="GREEN"textNO
EA14:EB14Expression=$EC$13="YELLOW"textNO
EA14:EB14Expression=$EC$13="BROWN"textNO
EA14:EB14Expression=$EC$13="BLUE"textNO
EA14:EB14Expression=$EC13="BLACK"textNO
EC13Cell Valuecontains "GREEN"textYES
EC13Cell Valuecontains "BLUE"textYES
EC13Cell Valuecontains "YELLOW"textYES
EC13Cell Valuecontains "BLACK"textYES
EC13Cell Valuecontains "RED"textYES
EC13Cell Valuecontains "BROWN"textYES
EB13:EC13Cell Valuecontains "GREEN"textNO
EB13:EC13Cell Valuecontains "BLUE"textNO
EB13:EC13Cell Valuecontains "YELLOW"textNO
EB13:EC13Cell Valuecontains "BLACK"textNO
EB13:EC13Cell Valuecontains "RED"textNO
EB13:EC13Cell Valuecontains "BROWN"textNO
Cells with Data Validation
CellAllowCriteria
EA13ListN/A,34,38,40,41,45,46,47,48,49,50,51,52,59,60,64,66,67,68,95,T6-1,T6-2,T6-3,T6-4
EH13ListN/A,34,38,40,41,45,46,47,48,49,50,51,52,59,60,64,66,67,68,95,T6-1,T6-2,T6-3,T6-4
EO13ListN/A,34,38,40,41,45,46,47,48,49,50,51,52,59,60,64,66,67,68,95,T6-1,T6-2,T6-3,T6-4
EV13ListN/A,34,38,40,41,45,46,47,48,49,50,51,52,59,60,64,66,67,68,95,T6-1,T6-2,T6-3,T6-4
FC13ListN/A,34,38,40,41,45,46,47,48,49,50,51,52,59,60,64,66,67,68,95,T6-1,T6-2,T6-3,T6-4
 
Upvote 0
The formula for EA14 should be
Excel Formula:
=RANK.EQ(EG13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($K$13:EG13,EG13,$K$12:EG12,"TA")-1
and for EH14
Excel Formula:
=RANK.EQ(EN13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($K$13:EN13,EN13,$K$12:EN12,"TA")-1
 
Upvote 1
Solution
The formula for EA14 should be
Excel Formula:
=RANK.EQ(EG13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($K$13:EG13,EG13,$K$12:EG12,"TA")-1
and for EH14
Excel Formula:
=RANK.EQ(EN13,($K$13,$R$13,$Y$13,$AF$13,$AM$13,$AT$13,$BA$13,$BH$13,$BO$13,$BV$13,$CC$13,$CJ$13,$CQ$13,$CX$13,$DE$13,$DL$13,$DS$13,$DZ$13,$EG$13,$EN$13,$EU$13,$FB$13,$FI$13))+COUNTIFS($K$13:EN13,EN13,$K$12:EN12,"TA")-1
Ah, I see what we did. We replaced all the 'K's in the COUNTIFS to the corresponding cell, thinking that's what it should be. We went back through and made the corrections and now it works perfectly.

THANK YOU!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 1

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

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