0

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?

Improve this question
1

1 Answer 1

Reset to default
0

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

Improve this answer
5
  • So easy (well, not for me), many thanks, works great. Now I just have to figure out how it works.....
    – Carl-Johan Larsson
    Nov 1, 2021 at 21:43
  • See my slightly simplified formula and explanation above.
    – Erik Tyler
    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?
    – Tedinoz
    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."
    – Erik Tyler
    Nov 1, 2021 at 22:16
  • in Italian, "a caval donato non si guarda in bocca"
    – Daniele
    Nov 2, 2021 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.