0

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?

migrated from superuser.com Jun 16 at 20:00

This question came from our site for computer enthusiasts and power users.

0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy