I have a sheet in Excel 365 with the columns A and B as shown below and I want to get columns C and D with some formula (not VBA!). That is, I want to repeat every Title for Count times and add a running number to it.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Title | Count | Running Title | Running Number |
| 2 | Anna | 3 | Anna | 1 |
| 3 | Ben | 2 | Anna | 2 |
| 4 | Anna | 3 | ||
| 5 | Ben | 1 | ||
| 6 | Ben | 2 |
For the Running Title, I found a 
Using TEXTSPLIT() & TEXTJOIN() with REPT()
=TEXTSPLIT(TEXTJOIN("-",,REPT(A2:A3&"|",B2:B3)),"-","|",1)
Or,
Create Custom Formula With A Friendly Name Using LAMBDA()
• Formula used in cell C2
=REPEAT.NTIMES(A2:A3,B2:B3)
The Excel LAMBDA() function gives us a way create custom functions that can be reused throughout a workbook, without using VBA, with a friendly name.
The formula used in Name Manager as shown below with testing syntax
=LAMBDA(values,num_repeat,
XLOOKUP(SEQUENCE(SUM(num_repeat)),
VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a b))),
VSTACK(values,""),,-1))(A2:A3,B2:B3)
Copy the above formula, not including the testing parameters at the end, press CTRL F3, this opens the Name Manager, click New.
In the New Name dialog, enter the name REPEAT.NTIMES, leave the scope set to workbook, and paste the formula you copied into the "Refers to" input area, press OK.
Now that the LAMBDA() formula has a name, it can be used in the workbook like any other function.
Lastly to get the Running Number we can use a COUNTIF() Function,
• Formula used in cell D2
=COUNTIF(C$2:C2,C2)
CodePudding user response：
You could try:
Formula in D1:
=REDUCE("Running "&{"Title","Number"},A2:B3,LAMBDA(a,b,IF(COLUMN(b)=1,LET(x,OFFSET(b,,1,1),VSTACK(a,HSTACK(INDEX(b,SEQUENCE(x,,,0)),SEQUENCE(x)))),a)))
Or:
=REDUCE("Running "&{"Title","Number"},A2:A3&"|"&B2:B3,LAMBDA(a,b,LET(x,TEXTBEFORE(b,"|"),y,--TEXTAFTER(b,"|"),VSTACK(a,HSTACK(INDEX(x,SEQUENCE(y,,,0)),SEQUENCE(y))))))
Or:
=HSTACK(REDUCE("Running Title",REPT(A2:A3&"|",B2:B3),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,"|",1)))),REDUCE("Running Number",B2:B3,LAMBDA(a,b,VSTACK(a,SEQUENCE(b)))))