Dynamic Array for Count

[Stephen_IV]

I am looking for a dynamic array for this count, any help would be appreciated! Thanks in advance!!!

VBA Top 50.xlsm A B 1 Dynamic Array 2 1 274395 3 2 245000 4 3 175000 5 4 172910 6 4 172910 7 5 169480 8 6 167833 9 6 167833 10 6 167833 11 7 164762 12 7 164762 13 8 161762 14 8 161762 15 8 161762 16 8 161762 17 8 161762 18 8 161762 19 9 159168 20 9 159168 21 9 159168 22 9 159168 23 10 156399 24 10 156399 25 10 156399 26 11 155804 27 11 155804 28 11 155804 29 11 155804 30 11 155804 31 11 155804 32 11 155804 33 11 155804 34 11 155804 35 11 155804 36 11 155804 37 11 155804 38 11 155804 39 11 155804 40 11 155804 41 11 155804 42 11 155804 43 11 155804 44 11 155804 45 11 155804 46 12 153038 47 12 153038 48 12 153038 49 12 153038 50 13 149803 51 14 149725 52 14 149725 53 14 149725 54 14 149725 55 14 149725 56 14 149725 57 14 149725 Sheet2 Cell Formulas Range Formula A2:A57 A2 =SUM(IF(B2<$B$2:$B$57,1/COUNTIF($B$2:$B$57,$B$2:$B$57)))+1

 

[lrobbo314]

How about this?

SIV
	A	B	C	D
1	Original		Dynamic Array	Lambda
2	1	274395	1	1
3	2	245000	2	2
4	3	175000	3	3
5	4	172910	4	4
6	4	172910	4	4
7	5	169480	5	5
8	6	167833	6	6
9	6	167833	6	6
10	6	167833	6	6
11	7	164762	7	7
12	7	164762	7	7
13	8	161762	8	8
14	8	161762	8	8
15	8	161762	8	8
16	8	161762	8	8
17	8	161762	8	8
18	8	161762	8	8
19	9	159168	9	9
20	9	159168	9	9
21	9	159168	9	9
22	9	159168	9	9
23	10	156399	10	10
24	10	156399	10	10
25	10	156399	10	10
26	11	155804	11	11
27	11	155804	11	11
28	11	155804	11	11
29	11	155804	11	11
30	11	155804	11	11
31	11	155804	11	11
32	11	155804	11	11
33	11	155804	11	11
34	11	155804	11	11
35	11	155804	11	11
36	11	155804	11	11
37	11	155804	11	11
38	11	155804	11	11
39	11	155804	11	11
40	11	155804	11	11
41	11	155804	11	11
42	11	155804	11	11
43	11	155804	11	11
44	11	155804	11	11
45	11	155804	11	11
46	12	153038	12	12
47	12	153038	12	12
48	12	153038	12	12
49	12	153038	12	12
50	13	149803	13	13
51	14	149725	14	14
52	14	149725	14	14
53	14	149725	14	14
54	14	149725	14	14
55	14	149725	14	14
56	14	149725	14	14
57	14	149725	14	14
Sheet2

Cell Formulas
Range	Formula
C2:C57	C2	=SCAN(0,SEQUENCE(ROWS(B2:B57)),LAMBDA(s,c,IF(c=1,1,IF(INDEX(B2:B57,c-1)<>INDEX(B2:B57,c),s+1,s))))
D2:D57	D2	=SIV(B2:B57)
A2:A57	A2	=SUM(IF(B2<$B$2:$B$57,1/COUNTIF($B$2:$B$57,$B$2:$B$57)))+1
Dynamic array formulas.

 

[Fluff]

What exactly is SIV?

 

[lrobbo314]

It was the dynamic array formula wrapped in a lambda

 

[Stephen_IV]

Thank you so much!!! Worked like a charm!!!

 

[Fluff]

It was the dynamic array formula wrapped in a lambda

It would have been helpful if you had explained that.