Find Certain Cell Value and Replace with The Same Value

Question

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#ProductUserSales1AppleAmyRemoved1AppleBenZeb1BananaCreeZegger1BananaBenZeb1CheeryCreeRemoved2AppleDavVic2BananaDavVic3BananaFrankKath3BananaGreeRemoved

Expected Output:

Firm#ProductUserSales1AppleAmyZeb1AppleBenZeb1BananaCreeZegger1BananaBenZeb1CheeryCreeZegger2AppleDavVic2BananaDavVic3BananaFrankKath3BananaGreeKath

Thank you for your time

binu_acs · Accepted Answer

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

DataNath · Accepted Answer

@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: