"Alan Beban" wrote...
And here's an alternative set of formulas:

A1: =VLOOKUP(B1,B$1:D1,3,0)

C1: =SUM(1/COUNTIF(B$1:B1,B$1:B1)) array entered

Or without array entry,

=SUMPRODUCT(1/COUNTIF(B$1:B1,B$1:B1))

D1: 1

D2:
=IF(B$2=B$1,IF(COUNTIF(B$1:B2,B2)=1,MAX(C$1:C1)+1 ,
MATCH(B2,B$2:B2,0)),IF(COUNTIF(B$1:B2,B2)=1,MAX(C $1:C1)+1,
MATCH(B2,B$1:B2,0)))

Starting with row 2, the function call counts on each row are

1 VLOOKUP O(K)
1 COUNTIF(r,r) O(K^2)
1 SUM[PRODUCT] O(K)
2 COUNTIF(r,x) O(K) both identical
2 MAX O(K) both identical
2 MATCH O(K)
3 IF O(1)
12 in total

where K is the row number. Copied down through N rows, the COUNTIF calls in
col C dominate, making the overall approach O(N^2 log(N)).

Compare the foregoing to the following single cell formula.

A3:
=IF(ISERROR(MATCH(B3,B$1:B2,0)),MAX(A$1:A2)+1,
INDEX(A$1:A2,MATCH(B3,B$1:B2,0)))

Starting in row 3, the function call counts on each row are

2 MATCH O(K) both identical
1 MAX O(K)
1 INDEX O(1)
1 ISERROR O(1)
1 IF O(1)
6 in total

N rows each containing such formulas, so overall O(N log(N)).

What's the benefit of your formulas? It's clearly not efficiency, either in
terms of recalc speed, disk storage or RAM usage.

Then there's the 3 cell/result formulas. Fixing the A3 and Y4 formulas,

A3:
=IF(ISERROR(X3),Y3,INDEX(A$1:A2,X3))

X3:
=MATCH(B3,B$1:B2,0)

Y3:
=MAX(A1:A2)

Y4:
=Y3+ISERROR(X4)

The Y3 formula is O(1), constant time. Starting with row 4,

1 INDEX O(1)
2 ISERROR O(1)
1 IF O(1)
1 MATCH O(K)
5 in total

Over N rows, O(N log(N)) again, but with a constant factor reduction since
only one O(K) function call on each row.

Again, what's the benefit of your formulas?