List of dates, repeating

I have an issue that I cant solve, hopefully someone can give some insights.

So, I have a list that contains dates and the number of times something should be done, like this:

Date Number of times
2021-10-04 2
2021-10-05 3
2021-10-06 4
2021-10-07 2

I would like to have a special output, a list with the "Date" column repeated for as many times as it is stated in the "Number of times" column.

So this output:

Date
2021-10-04
2021-10-04
2021-10-05
2021-10-05
2021-10-05
2021-10-06
2021-10-06
2021-10-06
2021-10-06
2021-10-07
2021-10-07

Is this possible to achieve without scripting? With somekind of formula?

Because the nature of the returned list will differ from the input chart, I recommend that you place the following formula in a new sheet. The formula assumes that your test data (with headers as shown in your post) is in Sheet1!A:B. Place the following formula in cell A1 of your new sheet:

`=ArrayFormula({Sheet1!A1; QUERY(FLATTEN(SPLIT(REPT(Sheet1!A2:A&"|",Sheet1!B2:B),"|")),"Select * WHERE Col1 Is Not Null")})`

This formula will produce the header and all results.

The initial return data will look like numbers in the 40000-50000 range. These are raw dates. Just select the column and format in the date format of your choice.

How It Works:

A virtual array containing the header (pulled from the original Col-A header cell) and all results is formed between curly brackets { }. The semicolon means "Place the content before on top of the content after" (i.e., place the header over the results).

A pipe symbol is appended to the end of every cell value in A2:A.

`REPT` forms a one-column array of strings made up of `A2:A&"|"` `B2:B` times in a row each.

`SPLIT` then splits those concatenated strings at the pipe symbol, pushing the iterations into virtual cells moving right (i.e., forming a 2D grid).

`FLATTEN` forms one column from the 2D grid results.

`QUERY` cleans out any null data (introduced by the blank rows in your original sheet and the uneven number of times each date is requested in B2:B after the `SPLIT` is performed).

• So easy (well, not for me), many thanks, works great. Now I just have to figure out how it works..... Nov 1, 2021 at 21:43
• See my slightly simplified formula and explanation above. Nov 1, 2021 at 21:56
• Eric, do you think that this was a question that needed to be answered, or would there have been a multitude of precedents and duplicates? Nov 1, 2021 at 22:09
• @Tedinoz, as I assessed the question—realizing full well this has been asked before—I myself couldn't quickly think of what a regular user would enter for search terms. So I felt answering was warranted. However, I've never understood the apparent need for didactic, pejorative or critical comments of one contributor to another on these free forums. I made the assessment. I chose to answer on my own donated time. As the old saying goes, "Never look a gift horse in the mouth." Nov 1, 2021 at 22:16
• in Italian, "a caval donato non si guarda in bocca" Nov 2, 2021 at 6:30