Repeat Values Variable Times on Each Row – Google Sheets

google sheets

I'd like to automatically populate a dynamic list where values are repeated on each row separately. The number of repetitions is in column A and the value to repeat is in column B. The formula should go into column C, like this:

Best Answer

You can repeat values with the sequence() function like this:

=sequence(1, A2, B2, 0)

...where A2 contains the number of repetitions and B2 contains the value to repeat.

See sequence().

Related Solutions

Google Sheets – How to Convert Jagged Array to 2D Array

For the OP example try the following formula:

=ArrayFormula(
IFERROR(
REGEXEXTRACT(","&
TRANSPOSE(TRIM(SPLIT(QUERY(TRANSPOSE(QUERY(
IFERROR(A4:A6&","&
REGEXEXTRACT(","&
SUBSTITUTE(B4:B6,", ",","),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")&";")
,,1000))
,,1000),";"))),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,2))-1)&",+([^,]+)"
)
)
)

For the 30,000 rows:

Change the references accordingly.
Replace 1000 by 30000 or a larger number.
- The 1000 is the headers argument of QUERY(data,query,[headers]). We want use a number big enough to include all the rows of the source data.
If the second column of the source data has rows with more than 6 items, replace the 6 in OFFSET(A1,,,1,6) by the corresponding number of items.

See the reference for further details.

References

JOINing code-points after CHAR conversion in Google Spreadsheets

Google-sheets – Grab data values from Row with max value

You have not mentioned what you would like to so with the three values mentioned, but maybe try:

 =index(B:BB,match(max(A1:A10),A:A,0))

or perhaps:

=index(B:B,match(max(A1:A10),A:A,0))&index(C:C,match(max(A1:A10),A:A,0))&index(E:E,match(max(A1:A10),A:A,0))