Find dups and choose which to keep

Hello -I have a file that has duplicate email addresses in it. I want to keep the one that has to highest $. In my example below, I'd like to identify and keep the first one while removing the second one from the output. There are also records that are not dups that I want in the output as well.
I've tried the Unique tool and, while it identifies the dups it doesn't help me to remove the ones I do not want. I also tried summarize and couldn't figure out how to configure it to get me to where I need to be. (Sorry for the terrible formatting - It's my first time posting and I kept getting an error that I had HTML in my post that had to be removed.)

Example:

CustomerName Email Dollars
ABC cust@cust.com $60
ABC cust@cust.com $2
CDE pat@pat.com $2

Output I want:

CustomerName Email Dollars
ABC julie@julie.com $60
CDE pat@pat.com $2

Accepted answers

atcodedog05

Hi @JuBo28

Here is how you can do it.

Workflow:

1. Using formula tool to convert dollar to numeric value.

2. Using sort tool to sort name of ascending and dollar value on descending.

3. Using sample tool keeping only first row for name (highest dollar value row for name).

Hope this helps : )

dedup.yxmd

All comments

atcodedog05

Hi @JuBo28

Here is how you can do it.

Workflow:

1. Using formula tool to convert dollar to numeric value.

2. Using sort tool to sort name of ascending and dollar value on descending.

3. Using sample tool keeping only first row for name (highest dollar value row for name).

Hope this helps : )

dedup.yxmd

Garabujo7

Hello @JuBo28 ,

How about this simpler approach:

Summarize and get the max value of dollars.

JuBo28

Thank you for the quick reply @atcodedog05 ! This is what worked for my solution.

JuBo28

Hi @Garabujo7 -- thank you for your quick reply. While I didn't use this is my solution, I appreciate you responding. There were many other columns in my actual workflow so I found it easier to use the first solution posted. Although, your solution did jar my creative juices to check something along my workflow to see how the joins, etc. changed my output.

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703