Add Same Text To Multiple Cells In One Column using Excel formula

Question

I have an excel spreadsheet with two columns with example data

Name	Quantity
Dell v1	12
HP v1	4

I've created a new sheet, and wanted to know which formula allows me to add the text value 'Dell v1' to 12 cells in a single column? I understand I can copy & paste, drag down etc....

Name
Dell v1
Dell v1
Dell v1
Dell v1
Dell v1
Dell v1
Dell v1
Dell v1
Dell v1
Dell v1

Answer 1

Unless I have misunderstood the objective, with Office 365, you can do:

=LET( series, A2:A3,
      by, B2:B3,
       elements, SUM( by ),  eSeq, SEQUENCE( elements,,0 ),
       bySeq, SEQUENCE( ROWS( by ) ),
       byPos, MMULT( --(bySeq >= TRANSPOSE( bySeq )), by ),
       INDEX( series, IFERROR(MATCH( eSeq, byPos, 1 )+1,1), 0 ) )

where the Name is in series and the Quantities are in by.

LAMBDA Helper

I just realized it could be shorter with a LAMBDA helper:

=LET( series, A2:A3,
      by, B2:B3,
       eSeq, SEQUENCE( SUM( by ),,0 ),
       byPos, SCAN(0,by,LAMBDA(a,b,a+b)),
       INDEX( series, IFERROR(MATCH( eSeq, byPos, 1 )+1,1), 0 ) )

Answer 2

As per my comment, this seems to be a nice task for PowerQuery. Load your data from the table, and assuming the the name of the table to be 'table1', you can change the M-code in the advanced editor to the following:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Quantity", Int64.Type}}),
    Repeated = Table.TransformColumns(Typed, {"Quantity", each List.Numbers(_,_,0), type list}),
    Expanded = Table.ExpandListColumn(Repeated, "Quantity"),
    Finished = Table.RemoveColumns(Expanded,{"Quantity"})
in
    Finished

Then close PowerQuery and it will load the data back into Excel.