How to fill null values with data in a before and after row

[NadiaMazlan25]

Hi all, I have a problem where I want to fill the null value with data from before and after the row. I tried to use a multi-row formula, but it can only be before or after. Is there any solution for this?

Input:

 

Desirable Output:

 

[flying008]

Hi, @NadiaMazlan25 

Update Existing Field with: 

IF IsEmpty([Name]) && [Row-2:Name] = [Row-1:Name] THEN [Row-1:Name]
ELSEIF IsEmpty([Name]) && !IsEmpty([Row-1:Name]) && !IsEmpty([Row+1:Name]) THEN [Row+1:Name]
ELSE [Name] 
ENDIF

[NadiaMazlan25]

Hi @flying008 

I tried the formula, but I noticed the value for 'Company D' is replaced by 'Company E'

[flying008 · Accepted solution]

Hi, @NadiaMazlan25 

IF IsEmpty([Name]) && [Row-2:Name] = [Row-1:Name] THEN [Row-1:Name]
ELSEIF IsEmpty([Name]) && !IsEmpty([Row-1:Name]) && !IsEmpty([Row+1:Name]) THEN [Row+1:Name]
ELSE [Name] 
ENDIF

[NadiaMazlan25]

Hi @flying008 

Thank you. It works perfectly. For an additional question, what if there are 2 null columns above it? how to solve it?

 

[flying008]

Hi, @NadiaMazlan25 

Well , there is a full dynamic resolution for you , if you get your want, please mark it as a solution and give a like for more share.

Spoiler

Replace(REGEX_Replace([Name], '((?<=,)(?:<,)+([[:alpha:]\s]+),(?:<,)+)', '$2$1'), '<', '')

Input			Output
Name	Address		Name	Address
Company B	Address 2		Company B	Address 2
	Address 2		Company C	Address 2
	Address 3		Company C	Address 3
Company C	Address 4		Company C	Address 4
	Address 4		Company C	Address 4
	Address 5		Company C	Address 5
Company D	Address 6		Company D	Address 6
	Address 7		Company E	Address 7
	Address 7		Company E	Address 7
	Address 7		Company E	Address 7
Company E	Address 7		Company E	Address 7
	Address 7		Company E	Address 7
	Address 7		Company E	Address 7
	Address 7		Company E	Address 7
Company F	Address 7		Company F	Address 7