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
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jul 12, 2023

dtbsmith 

=REDUCE({"Itemised Result"."Unique ID"."Concat Result"},SEQUENCE(ROWS(Tabelle1[Name])),
  LAMBDA(x,y,
LET(
z,DROP(TOCOL(TEXTSPLIT(REPT(INDEX(Tabelle1[Name],y)&"//",INDEX(Tabelle1[Quantity],y)),"//")),-1),
v,SEQUENCE(INDEX(Tabelle1[Quantity],y),,1),
VSTACK(x,HSTACK(z,v,z&"_"&v)))
  )
)

An alternative with Office 365 could be this formula.

 

  • dtbsmith's avatar
    dtbsmith
    Copper Contributor
    Jul 14, 2023
    Thanks for this. I think I'm going to have to look into functions within this formula as I've not come across them before. You've managed to ace it with just formulas though - very impressed!

Resources