Populating field from another field in Excel

April 6, 2018 3:25 AM

I'm working with a spreadsheet which has been converted from XML. It has several thousand rows. The first field is a person ID which applies to the next few rows, but in each case the person ID is blank on every row except the first one. How can I populate a field on every row with the relevant person ID?

Spreadsheet looks something like this:

PersonID, Field1, Field2, Field3 ...
300,null, null,,,,
null,value,null,,,,
null,null, value,,,,

I want to end up with something like this:

PersonID, Field1, Field2, Field3 ...
300,null,null,,,,
300,value,null,,,,
300,null, value,,,,

Tried various combinations of formulae but am stuck - any help appreciated.

posted by paduasoy to Computers & Internet (3 answers total)

Best answer: Insert a new column B next to A. Put the following in B2:

if(isblank(a2),b1,a2)

and fill it all the rest of the way down column B.
posted by flabdablet at 3:33 AM on April 6, 2018

Response by poster: Thank you! Thought I had tried this but must have buggered it up somehow.
posted by paduasoy at 3:37 AM on April 6, 2018

Another way to do this is: to select all the blanks and populate them with the above value:

Select the PersonID column
Ctrl+G > Special...>Blanks
"= [cell above]"
Ctrl+Enter

Make sure you copy the column and paste as values only, otherwise the function will stay put and change if you move stuff around.
posted by RhysPenbras at 9:34 AM on April 6, 2018 [2 favorites]