Hello, I have a invoice data and I want to remove any duplicate invoice numbers where the status is "removed". The issue is that there are some invoices that have the "removed" status that I would still want to keep. Example input date: Invoice NumberAmountSub-statusApprover10000500ProcessedA10000500Removed 10001200RemovedB10002100ProcessedC1000312ProcessedD1000312Removed Output: Invoice NumberAmountSub-statusApproverKeep/Remove10000500ProcessedAKeep10000-500Removed Remove10001200RemovedBKeep10002100ProcessedCKeep1000312ProcessedDKeep10003-12Removed Remove My attempt: I tried the summarize tool, but it didn't work since the sub-status and Approver columns are different. I also tried only summarizing on invoice number, and then joining the data set, but that re-adds the duplicate values. Summarize tool:

Hi @andreahq Is this what you're after? Could also use a unique tool instead of the summarise, but the nice thing is the summarise will drop the Sorting column I made Hope that helps, Ollie Dedupe.yxmd

Remove Duplicate IDs with Different Status

andreahq

Hello,

I have a invoice data and I want to remove any duplicate invoice numbers where the status is "removed". The issue is that there are some invoices that have the "removed" status that I would still want to keep.

Example input date:

Invoice Number	Amount	Sub-status	Approver
10000	500	Processed	A
10000	500	Removed
10001	200	Removed	B
10002	100	Processed	C
10003	12	Processed	D
10003	12	Removed

Output:

Invoice Number	Amount	Sub-status	Approver	Keep/Remove
10000	500	Processed	A	Keep
10000	-500	Removed		Remove
10001	200	Removed	B	Keep
10002	100	Processed	C	Keep
10003	12	Processed	D	Keep
10003	-12	Removed		Remove

My attempt:

I tried the summarize tool, but it didn't work since the sub-status and Approver columns are different. I also tried only summarizing on invoice number, and then joining the data set, but that re-adds the duplicate values.

Summarize tool:

Input

Help

Accepted answers

All comments

cjaneczko

Have you tried playing around with the Unique Tool on invoice number? Do you ever want two of the same invoice numbers, or only one? Alternatively, is there always an approver? Can you filter out any records where there are no approvers? In the sample data set this seems to solve the problem.

alisonpitt

You can do this by counting up the number of lines with a given invoice number, and then making your filter criteria be 'Removed' and Invoices > 1. Here are a couple solutions, not sure whether you wanted to match the output above, or if you were just trying to get rid of the dupes entirely.

Spoiler

duperemove.yxmd

jfha97

To echo @alisonpitt, whose solution should work perfectly, I got a similar result.

Remove Duplicate IDs with Different Status.yxmd

smoosh

Would something like this work? You can play around with the logic, but it does give the expected output shown in your post.

Remove some dupes.yxmd

albert_alaluf

@andreahq Can you please provide sample raw data and the result you need? I'm a little bit confused on output? What do you like to keep?

andreahq

Sorry it was a mistake in the example data set. In the real one the unique Invoice numbers that have "Removed" status also don't have an approver so they would be filtered out and I want them to stay

cjaneczko

Can you provide a Sample input with the desired output. The original post is confusing.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703