Marking rows as DUPLICATE except for 1 row with Max Quantity

Question

Hi all, I think I may be overthinking this. I'm looking for a way to identify the duplicate rows (based off of recipient, sku, validated size) and only leave the one with the Max Qty unmarked.

Here is the data I'm working with.

RECIPIENTSKUORIGINAL SIZEVALIDATED SIZEQTYJOHN DOEFB5368-120L/XLM/L1JOHN DOEFB5368-120M/LM/L2JOHN DOEFB5368-120S/MM/L1JANE DOEFB5368-120M/LM/L2JEFF DOEFB5368-120S/MM/L1

Example with the new DUPLICATE column I am trying to create.

RECIPIENTSKUORIGINAL SIZEVALIDATED SIZEQTYDUPLICATEJOHN DOEFB5368-120L/XLM/L1DUPLICATEJOHN DOEFB5368-120M/LM/L2 JOHN DOEFB5368-120S/MM/L1DUPLICATEJANE DOEFB5368-120M/LM/L2 JEFF DOEFB5368-120S/MM/L1

I was thinking some combination of Summarize->Max QTY and Unique tool, but I'm not sure how to join it all back together.

jrlindem · Accepted Answer

I would probably use summarize to find the max value and group by your key fields.  Then use formula tool to flag that as “non-duplicate”.  Then join that back to the stream ahead of the summarize on all your key fields.  Those that now have the “non-duplicate” can be changed to blanks and the remaining rows changed to “duplicate” using formula tool.

Edit:  Here's the workflow that supports the above method:

you could probably also do this using Sort and Multi row Formula tool as well.

Hope this helps, Jay

Qiu · Accepted Answer

@mcha54

My idea is to use the Sample tool after sorting by group to take out the max qry row then Union back to the remaining data stream.