Replace Duplicate Value based on Highest Sales

Hello,

I have data that contains duplicate names. All of the names have a unique identifier(RepID). I am able to use Fuzzy Match to choose the correct name and then Summarize to remove the duplicate. My issue is for duplicate names who have different Territories and Cities and/or States.

I need to choose the Territory, City and State of the duplicate with the highest YTD Sales and replace the row with the lower YTD Sales with the info from the row with the highest YTD Sales. I also include a Company Name but I sort to avoid names that are a part of multiple companies showing up near each other(duplicate names with different company names can be ignored).

I have been playing around with Fuzzy Match & Group and Multi Row Formulas and I am unable to get this to work. Currently this is a manual process for me so I would really appreciate any help. I've searched the community but nothing is my exact issue with the sales portion being the criteria.

I've attached a dummy workflow of what my actual data looks like and a screenshot below of what I need Thank you!

ReplaceRows.yxmd

Fuzzy Match

Accepted answers

philipmannering

@BrandonB Not sure this is doing exactly as requested. It's just Territory, City and State that needs to be updated. Check the attached,

workflow

Select data from highest YTD.yxmd

All comments

BrandonB

What about something like this? I did a group by RepID up top and because you have already sorted YTD descending, I just say if row-1:RepID is null meaning that it is the first row of the RepID, then just use the YTD value. Otherwise use the value above it. This will put the largest YTD value across every RepID. Workflow is attached.

multi row.png

ReplaceRows.yxmd

philipmannering

@BrandonB Not sure this is doing exactly as requested. It's just Territory, City and State that needs to be updated. Check the attached,

workflow

Select data from highest YTD.yxmd

BrandonB

Ah, I think you might be right @PhilipMannering

I interpreted "replace the row with the lower YTD Sales with the info from the row with the highest YTD Sales" as the actual sales value itself.

Lauren_G

I can't believe it was this simple all along! I'm always going straight to formulas instead of trying the other existing tools. I have been working on this for a while.

Thank you @PhilipMannering !

Lauren_G

This formula didn't help for this case, but it will be useful for another workflow I'm working on. Thank you for your response @BrandonB

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703