excelexcel-formulapowerquery

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

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
Solution

  • 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.

    enter image description here

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