Ask Your Question

Revision history [back]

You may create a non-sequential, but still unique numbering, using this formula in your B column (given your example is in A1:A8; the formula is put into B1 and drag-copied to B2:B8):

=MATCH(A1;$A$1:$A$8;0)

This gives you:

AAA 1
AAA 1
BBB 3
AAA 1
AAA 1
AAA 1
CCC 7
BBB 3

You may create a non-sequential, but still unique numbering, using this formula in your B column (given your example is in A1:A8; the formula is put into B1 and drag-copied to B2:B8):

=MATCH(A1;$A$1:$A$8;0)

This gives you:

AAA 1
AAA 1
BBB 3
AAA 1
AAA 1
AAA 1
CCC 7
BBB 3

Further, if you require sequential numbering, you may add two more columns: C and D, with formula in C1: =SUM(1/COUNTIF($B$1:$B$8;B1)), and in D1: =SUMIF($B$1:$B$8;"<="&B1;$C$1:$C$8).

This will give you:

AAA 1   0,2 1
AAA 1   0,2 1
BBB 3   0,5 2
AAA 1   0,2 1
AAA 1   0,2 1
AAA 1   0,2 1
CCC 7   1   3
BBB 3   0,5 2

The last column is the required numbering; columns B and C may be hidden.