Increment & Repeat a range based on cell values in Google sheets USING FORMULA

user_17579532 0
Viewed 239

I have a range (B1:B7) containing Dates as listed below

1-Jan 1-Jan 1-Jan 2-Jan 2-Jan 2-Jan 3-Jan

I want this range to repeat N times based on a cell value and also increment each value of the range by 7 when the range repeats each time.

Eg : If the value in cell A1 = 3 , I want the date range to repeat 3 times starting from cell A2 and increment the values by 7 each time they repeat.

Expected Result : 1-Jan 1-Jan 1-Jan 2-Jan 2-Jan 2-Jan 3-Jan 8-Jan 8-Jan 8-Jan 9-Jan 9-Jan 9-Jan 10-Jan 15-Jan 15-Jan 15-Jan 16-Jan 16-Jan 16-Jan 17-Jan

Below is the formula I am using to repeat the range. But I couldn't find a way to increment the values by 7 when the range repeats each time.

=query(flatten(transpose(ARRAYFORMULA(split(rept(B1:B7&" ",A1)," ")))),"where Col1 is not null")

Result : 1-Jan 1-Jan 1-Jan 2-Jan 2-Jan 2-Jan 3-Jan 1-Jan 1-Jan 1-Jan 2-Jan 2-Jan 2-Jan 3-Jan 1-Jan 1-Jan 1-Jan 2-Jan 2-Jan 2-Jan 3-Jan

google-sheets-formula google-sheets
1 Answers

Given the exact ranges and specs in your post, this formula should work for you in A2:

=ArrayFormula(FLATTEN(TRANSPOSE(FILTER(B:B,B:B<>"")+SEQUENCE(1,A1,0,7))))

FILTER creates a virtual array of only non-null rows from B1:B.

To those dates will be added a SEQUENCE of numbers 1 row by [number in A1] columns, starting with 0 and skipping by 7. In the posted example where A1 = 3, then, this SEQUENCE would be as follows: 0 7 14 (each in a separate virtual column).

Those numbers will be added to every date in the FILTER to form a grid with as many rows as dates and as many columns as the number in A1.

This grid is TRANSPOSEd before the next step, to keep the dates in the order of your original vertical listing instead of deferring to the horizontal additions to each.

The grid is then FLATTENed into one column.

The formula is wrapped in ArrayFormula( ) because it is processing arrays rather than single cells.

Keep in mind that you may need to format the results range (i.e., A2:A) in the date format of your choice. Otherwise, you may see raw dates, which will be numbers in the 44000-45000 range.

user_139032830
Related