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 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!

8 Replies
best response confirmed by dtbsmith (Copper Contributor)
Solution

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

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

See attached.

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

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

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

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

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

``````=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. # Re: List each occurrence with a unique identifier based on type and frequency table

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

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

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!

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

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

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

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