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

[dtbsmith]

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 the inverse of the FREQUENCY excel function). Simply put if I have a table of multiple 'Types' e.g, names (Bob, Chris, John) and the frequency of that 'Type' (e.g., 1,5,2) I'm trying to figure out the formula that would be required to itemise each instance (e.g., Bob_01, Chris_01, Chris_02, Chris_03, Chris_04, Chris_05, John_01, John_02). However the closest I've got is to just get the itemised type through a bit of a botched formula (see below):

(Unique column has been manually input to achieve example of desired output)

If anyone is able to shed any light on how I could achieve what I'm after I'd be hugely appreciative!

[#2 · Accepted solution]

@dtbsmith I'd use Power Query for that. You familiar with PQ?

See attached.

[#3]

@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)
)

[#4]

@dtbsmith 

A formula solution:

=REDUCE(
    {"Itemised Result", "Unique ID", "Concat Result"},
    Table1[Name],
    LAMBDA(a, v,
        LET(
            reps, XLOOKUP(v, Table1[Name], Table1[Quantity]),
            seq, SEQUENCE(reps),
            names, EXPAND(v, reps, , v),
            VSTACK(a, HSTACK(names, seq, names & "_" & seq))
        )
    )
)

[#5]

@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.

[#6]

Hi - This is brilliant and does it in such a simple and clever way!

[#7]

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!

[#8]

Thanks Patrick - very nifty formula - however think we're going to go with the PQ option.

[#9]

Thanks Sergei - I need to look into Lambda as it appears that's how everyone has done it via formulas!