Forum Discussion

dtbsmith's avatar
dtbsmith
Copper Contributor
Jul 11, 2023
Solved

List each occurrence with a unique identifier based on type and frequency table

Hi All,    Got a bit of a tough one here - I'm trying to work out a formula which is able to identify each 'instance/occurance' or a type based on the frequency value within the table (essentially ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 11, 2023

dtbsmith 

As variant

=LET(
    Itemised, REDUCE(
        "Itemised",
        SEQUENCE(ROWS(Table1)),
        LAMBDA(a,v,
            VSTACK(a, IF(SEQUENCE(INDEX(Table1, v, 2)), INDEX(Table1, v, 1)))
        )
    ),
    id, REDUCE("Id", Table1[Quantity], LAMBDA(a,v, VSTACK(a, SEQUENCE(v)))),
    HSTACK(Itemised, id, Itemised & "_" & id)
)

 

  • dtbsmith's avatar
    dtbsmith
    Copper Contributor
    Jul 17, 2023
    Thanks Sergei - I need to look into Lambda as it appears that's how everyone has done it via formulas!

Resources