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:

[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

[jfha97]

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

[smoosh]

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

[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. 

[OllieClarke]

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