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

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

Make it relatively easy by using helper column,

• In A3 put the number 1

• In A4 put formula =A3 C3 and copy down

• In E3 put formula

=VLOOKUP(ROW(A1),$A$3:$B$5,2,TRUE)

and drag down as far as it needs to go.