i create formula dynamically sort columns "ai" "aj" new scores inputted user. want "total" / "ag" column driver of sort. have used "small" function in excel, not handle duplicate scores.
what best way dynamically sort "total" column displaying name , score in ascending order in columns ai , aj?
i guess you're using formula sorted scores
=small(ag$11:ag$23,rows(ag10:ag$10))
if have unsorted scores in ag, letters in ah , sorted scores in ai, try in aj
=index(ah$11:ah$23,small(if(ag$11:ag$23=ai11,row(ag$11:ag$23)-row(ag$10)),1+countif(ai$10:ai10,ai11)))
this assuming scores start in row 11 , finish in row 23, need adjusting if (as appears screenshot) scores in every third row.
has entered array formula using ctrlshiftenter
here modified formula ai assuming scores every third row
=if(mod(rows(ag$8:ag10),3),"",small(ag$11:ag$47,rows(ag$8:ag10)/3))
and modified formula aj
=if(ai11="","",index(ah$11:ah$47,small(if(ag$11:ag$47=ai11,row(ag$11:ag$47)-row(ag$10)),1+countif(ai$10:ai10,ai11))))
Comments
Post a Comment