Google sheets: How to add a helper row to fill gaps in a list of customer ID row

Question

I'm trying to figure out a way how to fill blank cells in a row with previous existing data. I have an array of data with a customer ID-key I'd need to copy to some lines that don't automatically have the key ID due to gaps in source data.

Here's the situation. Gaps in data in Columns A:

Column A	Column B
Cell 1	Cell 2
	Cell 4
	Cell 6
Cell 7	Cell 8
	Cell 10
	Cell 12

This question comes in two parts. See the wished solution I tried to create below.

player0 · Accepted Answer · 2022-12-11T00:55:07.027

0

use:

=ARRAYFORMULA(IF(B2:B="",, VLOOKUP(ROW(A2:A), IF(A2:A<>"", {ROW(A2:A), A2:A}), 2, 1)))

update:

=ARRAYFORMULA(IF(B2:B="";; VLOOKUP(ROW(A2:A); IF(A2:A<>""; {ROW(A2:A)\ A2:A}); 2; 1)))

or:

=SCAN(; A2:INDEX(A:A; MAX(ROW(B:B)*(B:B<>""))); LAMBDA(a; b; IF(b=""; a; b)))

see: stackoverflow.com/questions/73767719

edited Dec 11 '22 at 00:55

answered Dec 10 '22 at 19:46

player0

124,011
12
67
124

That's it! This seems like working solution, yet I don't seem to get it working. I thought to add that extra column on the left side of the original A column, but I guess it's not relevant where the helper column is located. I try to figure this syntax out. Due to scandinavian input settings my google sheets syntax uses semicolon instead of commas. – Coach_Arto Dec 10 '22 at 20:11
Yeah, there's a bug in the translation of the locale settings. You should change {ROW(A2:A), A2:A} with {ROW(A2:A)\A2:A} – Martín Dec 10 '22 at 23:18
@Coach_Arto answer updated. use 2nd formula – player0 Dec 11 '22 at 00:48
1

Thanks a ton! This solved it. Now it's working and I could modify the formula to my needs. Boggles my mind though, how fast you came up with the solution. Thanks for helping. – Coach_Arto Dec 11 '22 at 08:24

score 0 · Answer 2 · answered Dec 10 '22 at 20:11

Use SCAN, like this

=SCAN(,A2:A7,LAMBDA(a,c,IF(c="",a,c)))

Since this is a very common question, I had also created a named function _FILL that solves this problem. You can import it from here.

And use it like this:

_FILL(vector, fill_mode) 

// fill with previous non-empty -> fill_mode = 1
// fill with next non-empty -> fill_mode = -1

_FILL(A2:A7,1)

Google sheets: How to add a helper row to fill gaps in a list of customer ID row

2 Answers2

update: