Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Jul 27, 2022
Solved

Need a formula to write the rank of students as a text.

I need a formula to write the rank of students as a text. such that:

-  If the student rank is (1), (cell-E2), then the rank will be FIRST (cell D2).

-  If the student rank is (1), (cell-E4), then the rank will be THird  (cell D4) and will be Third-Repeated in cell (D5) for the next student. while the rank of the student in cell (E6) will be the fifth as shown here.

is it possible to do that?

Thanks

  • ajl_ahmed 

    Use a helping list of words equivalent of numbers. Then use below formula.

    =IF(COUNTIF($E$2:$E2,E2)>1,XLOOKUP(E2,$I$2:$I$100,$J$2:$J$100,"")&"-repeated",XLOOKUP(E2,$I$2:$I$100,$J$2:$J$100,""))

     

3 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    ajl_ahmed 

    Use a helping list of words equivalent of numbers. Then use below formula.

    =IF(COUNTIF($E$2:$E2,E2)>1,XLOOKUP(E2,$I$2:$I$100,$J$2:$J$100,"")&"-repeated",XLOOKUP(E2,$I$2:$I$100,$J$2:$J$100,""))

     

  • mtarler's avatar
    mtarler
    Silver Contributor

    ajl_ahmed here is an option but you need to supply the word conversions:

    =LET(in,E2:E14,
         r,RANK.EQ(in,in,1),
         SCAN(0,r,LAMBDA(prior,curr,
           LET(
             new,INDEX(counting[#Data],curr),
             IF(LEFT(new,LEN(prior))=prior,
                new&"-repeated",
                new)))))

    this version and a version for older Excel are in the attached.

     

Resources