Find Certain Cell Value and Replace with The Same Value

[k3pineapple]

Hi All,

I would like to build a section to replace value.

The objective is to:

Find the error value & replace it with existing value.

Within the same Firm, under the same product, replace "Removed" value to existing sales value.

Sample input & output below

Data source:

Firm#	Product	User	Sales
1	Apple	Amy	Removed
1	Apple	Ben	Zeb
1	Banana	Cree	Zegger
1	Banana	Ben	Zeb
1	Cheery	Cree	Removed
2	Apple	Dav	Vic
2	Banana	Dav	Vic
3	Banana	Frank	Kath
3	Banana	Gree	Removed

Expected Output:

Firm#	Product	User	Sales
1	Apple	Amy	Zeb
1	Apple	Ben	Zeb
1	Banana	Cree	Zegger
1	Banana	Ben	Zeb
1	Cheery	Cree	Zegger
2	Apple	Dav	Vic
2	Banana	Dav	Vic
3	Banana	Frank	Kath
3	Banana	Gree	Kath

Thank you for your time

[binuacs · Accepted solution]

@k3pineapple One way of doing this with the multi-row formula tool

[DataNath · Accepted solution]

@k3pineapple here's how I'd go about this, by nulling all 'Removed' values before finding the max for each firm and product. However, how would you handle instances like 'Cherry'? You say it needs to be by Firm and Product, but there's no existing/non-Removed one for that: