Google Sheets – How to Copy Data Down into Next Set of Blank Cells

formulasgoogle sheets

I have some text that I've taken in from a pdf, however the columns are not lined up the way I need them to be:

data

I want to duplicate the column A and D values down into any empty spaces/rows. So in the end, all the spaces in those columns would become like this:

changed_data

Is there any way automatically to do that to those columns in general?

_{An automatic way to delete the rows with blanks at the end would be really useful as well.}

Best Answer

You can do this with a simple formula. Given the range shown in your example, insert a new column to the right of column A. In this new column B, write:

B136: =A136
B137: =if( isblank(A137) ; B136 ; A137 )

Duplicate the formula in B137 into the cells below it.

Let's unpack this:

B136 looks to the left for its value (A136).
B137 looks to the left for its value (A137), but if blank, uses the previous value (B136).
B138 looks to the left for its value (A138), but if blank, uses the previous value (B137).
… and so on.

Depending on your needs, you might be done at this point. Optionally, you can hide column A. Or you can copy column B, "paste values only" into column A, and delete column B.

You asked to do the identical operation with the data in column D, so the solution will be the same.

E136: =D136
E137: =if( isblank(D137) ; E136 ; D137 )
…

Please post your followup question about rows with blanks at the end as a separate question, and provide more detail. It's not clear what the problem is.

Short answer

Use Google Apps Script or a Google Sheets Add-on as there is no built-in feature to set cell background color by color codes.

Explanation

Google Apps Script is a tool that can be used to extended Google Sheets and other Google apps. It could be used to created add-ons.

There are two class methods that can be used to set the color of a single cell:

setBackground(color) : Sets the background color of all cells in the range in CSS notation (like '#ffffff' or 'white').
setBackgroundRGB(red, green, blue) : Sets the background to the given RGB color.

Below is an example of a script that sets the color of one cell based on the CSS color the cell at it's left when the value of that cell is edited.

/*
 * Sets the color of adjacent cell on Sheet1 when cells
 * of the column A are edited
 */
function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var col = range.getColumn();
  if(sheet != 'Sheet1' && col != 1) return;
  var color = e.value;
  range.offset(0, 1).setBackground(color);
}

Demo

Best Answer

Related Solutions

Google-sheets – Dragging functions through multiple cells with spaces in Google Spreadsheets

Google Sheets – Change Cell Color to Hexadecimal Value in Google Spreadsheets

Short answer

Explanation

References

Related Topic