[SOLVED] Copy value from a cell to the rest of a colum

Copy value from a cell to the rest of a colum

Can anybody help me to break down this long formula and ,by examining the independent elements, understand how it works?

It takes two contiguous columns, in this case A and B. First a value from A is taken. If there is a next value down A, it will repeat the actual value until that row. If there are no values left in A, it returns "" (blank)

So, in "B1" we write this very long formula:

ArrayFormula(IF(ROW(A:A)<=MATCH(2;1/(A:A<>"");1);LOOKUP(ROW(A:A);ROW(A:A)/IF(A:A<>"";TRUE;FALSE);A:A);))

The result is like this:

A	B
foo	foo (here goes the formula)
	foo
bar	bar
	bar
	bar
qux	qux
	(blank)
	(blank)
	(blank)
	(blank cells continue till the end of the file, because col A ends with "qux")

This array is quite useful, but I guess if there is a cleaner formula, or a better approach to this task... I suspect that this formula is a workaround and Spreadsheets must have an elegant resource to get done this kind of task.

Solution

Try this:

=ARRAYFORMULA(
  IF(
    ROW(A:A) > MATCH(2, 1 / (A:A <> "")),,
      VLOOKUP(ROW(A:A), FILTER({ROW(A:A), A:A}, A:A <> ""), 2)
  )
)

MATCH(2, 1 / (A:A <> "")) gives you the row number of the last non-empty cell in a column A:A.

Or if there might be blank cells at the top, then use additional condition:

=ARRAYFORMULA(
  IF(
      (ROW(A:A) > MATCH(2, 1 / (A:A <> "")))
    + (ROW(A:A) < MATCH("*", A:A,)),,
      VLOOKUP(ROW(A:A), FILTER({ROW(A:A), A:A}, A:A <> ""), 2)
  )
)