Repeat each value a different number of times. Excel

There are 2 columns of data:

a 2
b 3
c 1

Result:

X01a
X02a
X01b
X02b
X03b
X01c

In Google Sheets I use the formula:

=tocol(map(A2:A;B2:B;lambda(a;b;if(counta({a\b})<>2;;index("X"&text(sequence(1;b);"00")&a&"|"&b))));1)

In Excel this formula gives an error: #CALC! error (Nested Array)

How to do this in Excel?

Found answer in Repeat each value a different number of times, in Google Sheets

I don’t understand how to adapt this formula for my purposes.

=LET(Data,A2:A5,Repeats,B2:B5,
    Both,HSTACK(Data,Repeats),Filtered,FILTER(Both,Repeats>0),
    dData,TAKE(Filtered,,1),dStacked,VSTACK(dData,""),
    rData,TAKE(Filtered,,-1),rSequence,SEQUENCE(SUM(rData)),
    rStacked,VSTACK(0,rData),rScanned,SCAN(1,rStacked,LAMBDA(a,b,a+b)),
    rIndexes,MATCH(rSequence,rScanned),
Result,INDEX(dStacked,rIndexes),Result)

The formula will return over 250k results. I wish it didn't hang up. :)

Found the answer using an extra column. Is there a non-VBA Excel spilling formula to create and process arrays of arrays?

How to get the result without an extra column? All suggested methods return:

a
a
b
b
b
c

I need a result with running numbers.

1a
2a
1b
2b
3b
1c

Solution

Try the following formula using MAKEARRAY()

• Formula used in cell D1

=LET(
     α, A1:A3,
     φ, B1:B3,
     TOCOL(MAKEARRAY(ROWS(α),MAX(φ),LAMBDA(δ,ε,
     IF(ε<=INDEX(φ,δ),TEXT(ε,"X00")&INDEX(α,δ),p))),3))

Alternative approach, but note due to the use of ARRAYTOTEXT() there are character limitations:

• Formula used in cell D1

=TEXTSPLIT(ARRAYTOTEXT(MAP(A1:A3,B1:B3,LAMBDA(α,φ,ARRAYTOTEXT(TEXT(SEQUENCE(,φ),"X00")&α)))),,", ")

Also in the given link there was indeed a solution using helper columns but it could have been accomplished with some fine tunning of the formula proposed by JvdV Sir.

• Formula used in cell D1

=DROP(REDUCE("",A1:A3&"|"&B1:B3,LAMBDA(a,b,
  LET(x,TEXTBEFORE(b,"|"),y,--TEXTAFTER(b,"|"),
  VSTACK(a,TEXT(SEQUENCE(y),"X00")&INDEX(x,SEQUENCE(y,,,0)))))),1)

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

This can be accomplished quickly and easily using POWER QUERY. To achieve this using the said procedure, follow this simple steps using POWER QUERY window UI:

First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1

Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Create_Sequence = Table.AddColumn(Source, "Custom", each {1..[Column2]}),
    Expand_To_NewRows = Table.ExpandListColumn(Create_Sequence, "Custom"),
    Padding = Table.AddColumn(Expand_To_NewRows, "Output", each "X" & Text.PadStart(Text.From([Custom]),2,"0") & [Column1]),
    Remove_Unwanted_Cols = Table.SelectColumns(Padding,{"Output"}),
    Removed_Empty = Table.SelectRows(Remove_Unwanted_Cols, each [Output] <> null and [Output] <> "")
in
    Removed_Empty

Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.