Forum Discussion

Copper Contributor

Dec 30, 2022

Please help me trying to figure out the correct formulas

So I am trying to create this table where I have got a load of data, there is alot of data to go through so to do this manually would be a never ending task. At the moment column A is all I have. How...

excel

Formulas and Functions

mtarler

Silver Contributor

Dec 30, 2022

alexjw94 alternatively using dynamic array

=LET( in, A2:INDEX(A:A,COUNTA(A:A)),
           counts,DROP(REDUCE(1,SEQUENCE(MAX(in)),LAMBDA(p,q, VSTACK(p,SEQUENCE(SUM(--(in=q)))))),1),
           HSTACK(counts, in&"."&counts))

note that the "in" parameter on line 1 could be entered as a2:a100 instead of auto sizing