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.
CodePudding user response：
use:
=ARRAYFORMULA(IF(B2:B="",, VLOOKUP(ROW(A2:A), IF(A2:A<>"", {ROW(A2:A), A2:A}), 2, 1)))
CodePudding user response：
Here's a proposed solution: Adding Column X (helper column) and building an arrayfromula -function that would fill cells in Column X with the previous existing data from Column A until the function recognizes a new data entry. Then again it goes down the list checking each cell in Column A, filling missing information on Column X with a new data entry and repeats the procedure until reaching the end of the list (circa 2000 rows)
Column X Column A Column B Data from Cell 1 Cell 1 Cell 2 Data from Cell 1 Cell 4 Data from Cell 1 Cell 6 Data from Cell 7 Cell 7 Cell 8 Data from Cell 7 Cell 10 Data from Cell 7 Cell 12 ... and so on
I have tried to find a solution by using OFFSET -function inside an IFS-function and arrayformula -function but haven't been able to find a solution.
Here's the closest I have got. =ArrayFormula(IFS(B6<>"";B6;B6="";OFFSET(B6;-1;0);B6=""&OFFSET(B6;-1;0)="";OFFSET(B6;-2;0))) or =ArrayFormula(IFS(B3<>"";B3;B3="";OFFSET(B3;-1;0);B4="";OFFSET(B3;-1;0);B5="";OFFSET(B3;-1;0);B6="";OFFSET(B3;-1;0);B7="";OFFSET(B3;-1;0);B8="";OFFSET(B3;-1;0);B9="";OFFSET(B3;-1;0);B10="";OFFSET(B3;-1;0);B11="";OFFSET(B3;-1;0);B12="";OFFSET(B3;-1;0)))
CodePudding user response：
I tried to build such a solution: Adding Column X (helper column) and building an arrayfromula -function that would fill cells in Column X with the previous existing data from Column A until the function recognizes a new data entry. Then again it goes down the list checking each cell in Column A, filling missing information on Column X with a new data entry and repeats the procedure until reaching the end of the list (circa 2000 rows)
| Column X | Column A | Column B |
|---|---|---|
| Data from Cell 1 | Cell 1 | Cell 2 |
| Data from Cell 1 | Cell 4 | |
| Data from Cell 1 | Cell 6 | |
| Data from Cell 7 | Cell 7 | Cell 8 |
| Data from Cell 7 | Cell 10 | |
| Data from Cell 7 | Cell 12 |
... and so on
I have tried to find a solution by using OFFSET -function inside an IFS-function and arrayformula -function but haven't been able to find a solution.
Here's the closest I have got. =ArrayFormula(IFS(B6<>"";B6;B6="";OFFSET(B6;-1;0);B6=""&OFFSET(B6;-1;0)="";OFFSET(B6;-2;0))) or =ArrayFormula(IFS(B3<>"";B3;B3="";OFFSET(B3;-1;0);B4="";OFFSET(B3;-1;0);B5="";OFFSET(B3;-1;0);B6="";OFFSET(B3;-1;0);B7="";OFFSET(B3;-1;0);B8="";OFFSET(B3;-1;0);B9="";OFFSET(B3;-1;0);B10="";OFFSET(B3;-1;0);B11="";OFFSET(B3;-1;0);B12="";OFFSET(B3;-1;0)))
CodePudding user response：
Use 
Since this is a very common question, I had also created a named function _FILL that solves this problem. You can import it from 