grouping - how to assign same id for a same name (string) like dense rank to a character field in Excel

Question

I want a dense_rank kind of formula which will return a same id for a same name in first field. Now the problem is that rank or rank.eq function in excel can be applied to value fields only but here I want a dense rank for each name.

I leave an example...

thanks for your help

Answer 1

If you just want to assign unique ID to each name use this formula in G1 (assuming your data is in F1:F16 like the screenshot below

Approach-1 use this formula in G1 onwards(down)

=COUNTIF($F$1:$F$16, "<="&F1)

Still if you want these numbers to start from 1 and keep on increasing so on use this formula in H1

=SUMPRODUCT((G1>=$G$1:$G$16)/COUNTIF($G$1:$G$16,$G$1:$G$16))

APPROACH-2 Without the need for creating intermediate field

=SUMPRODUCT((F1>=$F$1:$F$16)/COUNTIF($F$1:$F$16,$F$1:$F$16))

You can get results like this