Is there a non-VBA Excel spilling formula to create and process arrays of arrays?

Question

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 somewhat arcane formula that looks somewhat unstable (relying on string processing). It basically uses the following formula to create an array that is {2,2,2,3,3} and XLOOKUP on that to get the titles.

=XLOOKUP(
   IFERROR(
      FILTERXML(
        "<a><b>"&
        SUBSTITUTE(TRIM(TEXTJOIN(" ",TRUE,REPT(ROW($A$2:$A$100)&" ",$B$2:$B$100)))," ","</b><b>")&
        "</b></a>",
        "//b"
      ),
   ""),
   ROW($A$2:$A$100),
   A2:A100
)

This works, but it would be great to see some less patchy formula (i.e. not relying on specifics on how Excel treats strings) for the purpose.

For the Running Number, I could use a formula like =IF($A2<>"",IF($C1=$C2,$D1+1,1),"") in cell D2 and extend it downwards by dragging the lower right corner downwards. This works, but I would love to see a spilling formula that only has to be defined in D2 and nowhere else.

In summary, is there a way to create columns C and D in the example above without having to define the size of the output (like I do by manually expanding the formula area) and without resorting to functions that depend on the internal workings of strings in Excel?

Perhaps not an improvement to what you're already doing, but how to repeat cell value would get you the running title by using 2 helper columns, then a simple COUNTIF to get the running number. — Isolated
– Isolated, Commented Oct 24, 2022 at 14:23

JvdV · Accepted Answer · 2022-10-24 17:47:41Z

4

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

edited Oct 24, 2022 at 17:47

answered Oct 24, 2022 at 16:31

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mayukh Bhattacharya · Accepted Answer · 2024-03-28 13:53:21Z

You can use either of the formulas:

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)

Edit: 3/28/2024

• Without using any LAMBDA() helper functions:

• Formula used in cell G2

=LET(
     α, SEQUENCE(,MAX(F2:F3)),
     δ, TOCOL(IFS(α<=F2:F3,E2:E3&"|"&α),2),
     HSTACK(TEXTBEFORE(δ,"|"),TEXTAFTER(δ,"|")))

Collectives™ on Stack Overflow

Is there a non-VBA Excel spilling formula to create and process arrays of arrays?

2 Answers 2

Comments

Using `TEXTSPLIT()` & `TEXTJOIN()` with `REPT()`

Create Custom Formula With A Friendly Name Using `LAMBDA()`

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Using TEXTSPLIT() & TEXTJOIN() with REPT()

Create Custom Formula With A Friendly Name Using LAMBDA()

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Using `TEXTSPLIT()` & `TEXTJOIN()` with `REPT()`

Create Custom Formula With A Friendly Name Using `LAMBDA()`