2

I have a spreadsheet of data on Google Sheets where one order is spread over several rows, however, only the first row contains all the information. I need a way to copy the last populated cell down into all of the blanks.

So it looks like the left, but I need it to do what's on the right:

GB    GB
-     GB
AU    AU
-     AU
-     AU

I've tried every formula I could find online, but they all either don't work or require pasting into every blank cell. This spreadsheet could reach 20,000+ rows, so I really need a formula that will do this automatically. I don't mind if it duplicates the whole column into a new column using an ARRAYFORMULA, I just need it to contain all the correct data.

To summarise, I want a formula that will take an entire column of a spreadsheet and populate every blank cell with the value in the last populated cell above it.

player0
  • 124,011
  • 12
  • 67
  • 124
barbarajohnson
  • 47
  • 5

1 Answers1

2
=ARRAYFORMULA(IF(ROW(A2:A) <= MAX(IF(NOT(ISBLANK(B2:B)), ROW(A2:A))),
 VLOOKUP(ROW(A2:A), FILTER({ROW(A2:A), A2:A}, LEN(A2:A)), 2), ))

0

or remove NOT

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you so much, this works perfectly and does exactly what I wanted! I've been trying to sort this for 2 days with no luck and you did it in 10 minutes - thank you very much. – barbarajohnson Jul 11 '19 at 12:12