Forum Discussion

RichFrench's avatar
RichFrench
Copper Contributor
May 25, 2022

Converting 2 column tally into single column

Hello all. I'm very new to Excel. I appreciate any help in advance.    I have a data set like this... I want to convert it to this... How is this possible please? Rich
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
May 25, 2022

Hi RichFrench 

Another 365 LAMBDA option:

 

 

=XLOOKUP(
    SEQUENCE(SUM(Frequency)),
    SCAN(,Frequency, LAMBDA(acc,v, acc+v)),
    Subject,
    ,1
)

 

If Excel 2021 (LAMBDA not avail.):

=XLOOKUP(
    SEQUENCE(SUM(Frequency)),
    SUBTOTAL(9,OFFSET(Frequency,,,SEQUENCE(ROWS(Frequency)))),
    Subject,
    ,1
)

 

Resources