if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
How do I fill down a column, copying the cell above, unless the adjacent cell has a value, then copy that value?
I'm trying to clean up a data table, and I want to fill column B with values, based on the last non-blank value in column A.
This formula works, if copied down in column B, but breaks whenever I insert a blank row, which I will need to do. I'm hoping for an arrayformula that will do the trick, but haven't found it yet.
My sample sheet is here. https://docs.google.com/spreadsheets/d/1q73uLmSCFwt1XnWEDOizf9i9uYDfFqXyr_VZ74DOU3c/edit?usp=sharing
I've tried variations of the following formulae:
=ArrayFormula(if(A3:A12="",indirect("B"&row()-1),A3:A12))
=ArrayFormula(if(A3:A12="",offset(B3:B12,-1,0,1,1),A3:A12))
=ArrayFormula(if(A2:A12="",offset(B2:B12,-1,0,1,1),A2:A12))
Many thanks for any guidance.
Solution
use:
=ARRAYFORMULA(VLOOKUP(ROW(A2:A12), IF(A2:A12<>"", {ROW(A2:A12), A2:A12}), 2, 1))
- switch case in XSL
- Get first non-empty cell in row
- An "and" operator for an "if" statement in Bash
- Checking for character in a string
- VB6 and VB.net has different outcomes with the same IF statements
- Returning Values to Razor Page - Confused Javascript or .cs?
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- comma operator in if condition
- How to assign values to a df$column from another df using conditions in R
- Else if statement to create a new column
- How can I run code If any one of if else statements is true?
- zsh condition on number results in bad pattern
- Cobol - checking for remainder in IF clause
- Can you store a variable inside a if-clause?
- How to use the @if angular with async pipe and alias for else block?
- Condition in python lists comprehensions
- nested ifelse in R with "condition has length > 1" warning
- Best practices for integrating optional arguments in shell command inside a snakemake pipeline
- Using joins and if statements
- Using Conditional Statements in Dymola
- Declaring a Variable in if-Statement
- Oracle sql return true if exists question
- How do I replace words in an HTML page using JS?
- How to automate a series of similar IF statements
- How to check a pattern in a string in matlab?
- If statement isn't showing console.log when condition passed and continue work as unexpected
- Order of Conditions in `if ... else if ... else ...` in C++
- How can I assign NA?
- How to return in an Async Function in Angular?
- Why does python use 'else' after for and while loops?