Multiply (copy) the text in a cell down a column, by the number below the cell?

Question

I'm trying to create a data set to upload into tableau, and am essentially trying to have the numbers and the words in the top left here-all multiplied (copied) into one column. So essentially Tan is 10, so I need it listed out on ten rows for Column L. Same thing for 'Grey' and so on. I've been trying different functions but things don't seem to be working. I've tried the repeat function but it only lists things horizontally and fully concatenated instead of one by one vertically and ALL within the same column.

Would anyone know a way I could this in excel so I can essentially automate this without having to go and copy things ONE by one?

I tried to use the repeat function and it wouldn't work since it produced things horizontally and jumbled up instead of one by one. I can do this all manually but I essentially have to do this about 50 times, so any type of formula or automation would help and I've been scouring the internet and ChatGPT to configure something but no such luck yet.

Answer 1

Repeat in Column From Row

• It looks similar to Mayukh Bhattacharya's solution in the comments but it is many, many times faster on a large data set. I tried it with over 1M records using the full rows 1:1 and 2:2 with the formula =RANDBETWEEN(1,125) (in row 2; don't forget to copy/paste values) and it finished in a bit more than one second.

=LET(d,A1:D1,r,A2:D2,
TOCOL(INDEX(d,,MATCH(SEQUENCE(,SUM(r)),
    HSTACK(1,SCAN(1,r,LAMBDA(a,b,a+b))),1))))