Google-sheets – Repeat formula n number of times in Google Sheets

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has a variable number of rows. Each cell has a date associated with it in another column, but sometimes that date is not filled in, I need to correct that like so:

Demo

+---+---+    +---+---+
| 1 | a |    | 1 | a |
+---+---+    +---+---+
|   | b |    | 1 | b |
+---+---+    +---+---+
| 2 | c |    | 2 | c |
+---+---+ -> +---+---+
|   | d |    | 2 | d |
+---+---+    +---+---+
|   | e |    | 2 | e |
+---+---+    +---+---+
| 3 | f |    | 3 | f |
+---+---+    +---+---+

I'm using arrayformula elsewhere, which works really well to repeat formulas, but I can't get this one to work. The number of repeats is stored in a cell and referenced when needed. Currently, my workaround method is to copy my formula in thousands of rows, but I'd rather have the formula in one cell and the rest be automatic

My formula:

=INDEX(C:C, MAX(FILTER(ROW(OFFSET(C$1, 0, 0, ROW())), 
 ISNUMBER(OFFSET(C$1, 0, 0, ROW())))), 1)

The formula is the exact same for each cell, so I tried wrapping it in an if that runs the correct number of times, but the formula is only evaluated once.

=ARRAYFORMULA(IF(OFFSET(C2, 0, 0, A2) + 1, INDEX(C:C, 
 MAX(FILTER(ROW(OFFSET(C$1, 0, 0, ROW())), 
 ISNUMBER(OFFSET(C$1, 0, 0, ROW())))), 1), "error"))

Is there any way to do this with a single formula per column?

Best Answer

=ARRAYFORMULA(QUERY({VLOOKUP(ROW(A:A), 
 {IF(A:A<>"", ROW(A:A)), A:A}, 2), B:B}, 
 "select Col1,Col2 where Col2 !=''"))

0