loopsgoogle-sheetslambdavlookupoffset
Google sheets: How to add a helper row to fill gaps in a list of customer ID row
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.
Solution
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
- Zipping for loop in fish shell (loop over multiple lists)
- How to apply a function in an iterable list
- Why doesn't the condition of my while loop 'apply' to my print() statement?
- Simplifying If Statements Into Loop
- OCaml for-loop seems to be re-executing more than expected
- loop through excel raw data file and input them to other excel formula sheet and perform iteration calculation
- Generate list of lead values
- Updating the output of a function that takes a changing list as an input
- Time Looping Python
- Counting the number of times a value appears in an array
- Add multiple unknowns to a string in Python
- Looping through array and removing items, without breaking for loop
- Print each value of a delimited string as an HTML list item
- more modern way of looping through C++ arrays
- Why do some kernel programmers use goto instead of simple while loops?
- How to make a JS foreach loop inside of a template literal block of HTML?
- How to hide properties of an object?
- JS - How does this loop crash the browser?
- Shopify - Add Number (1,2,3) before Product in Collection
- array_push() inside of nested loops is not populating the correct array structure
- Read csv value in loop controller Jmeter
- Loop (for each) over an array in JavaScript
- Python json.dumps() outputs all my data into one line but I want to have a new line for each entry
- Loop only stores the last row while trying to build a 2d array while looping a query result set
- How to loop each JSON files on bash script to know if content of the file in empty or not empty?
- Stop pyzmq receiver by KeyboardInterrupt
- How to build a 2d array with static and dynamic data while looping?
- Python: Make class iterable
- Assembly: calculation of string index
- Action Chain in loop works only once(Selenium/Python)