Formula options for filling in blank value using previous rows

markkrebs

Data is coming in like this with blank Name gaps:

I'm using this in Name helper

=IF(ISBLANK(Name@row), IF(ISBLANK(INDEX(Name:Name, MATCH([Row ID]@row - 1, [Row ID]:[Row ID], 1))), INDEX(Name:Name, MATCH([Row ID]@row - 2, [Row ID]:[Row ID], 1)), INDEX(Name:Name, MATCH([Row ID]@row - 1, [Row ID]:[Row ID], 1))), Name@row)

and have successfully filled in as you could see up to 2 blanks. E.g. Name 4. To accommodate more than 2 gaps the only way I came up with is to keep repeating the if sequence but that'll be a crazy long formula if I want to accommodate say 8 or 9 gaps.. Looking for a simpler way.....

@Paul Newcome @Genevieve P. ... show me your magic 😁

Jeff Reisman

@markkrebs I've had to solve something similar. Try this:

=IF(ISBLANK(Name@row), INDEX(Name:Name, MATCH(MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], < [Row ID]@row, Name:Name, NOT(ISBLANK(@cell)))), [Row ID]:[Row ID], 0)), Name@row)

Logic: If the Name is blank, find the Name value from the row with the highest Row ID that is less than this row's Row ID, where the Name isn't blank; otherwise, set this cell to Name@row.

markkrebs

Awesome!!! thanks!

Jeff Reisman

@markkrebs I've had to solve something similar. Try this:

=IF(ISBLANK(Name@row), INDEX(Name:Name, MATCH(MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], < [Row ID]@row, Name:Name, NOT(ISBLANK(@cell)))), [Row ID]:[Row ID], 0)), Name@row)

Logic: If the Name is blank, find the Name value from the row with the highest Row ID that is less than this row's Row ID, where the Name isn't blank; otherwise, set this cell to Name@row.

markkrebs

Awesome!!! thanks!