duplicate amount - for trip and invoices

[david gabra]

Hi

I have created a sample file with the expected results. I hope it is clear.
I have been doing this test manually for 100s of records and there must be a better way.
I would like to know where we have duplicates based on the same trip number and amount - but different invoice number.

Sometime a new line was posted (a minus value) to negate the duplicated value, so this should not show as a duplicate of course, since it was 'removed' - i have this is my example.

I hope I was clear and my sample file helps.

Thanks to anyone who can automate my checks.

 

[r2c2]

@david gabra

Interesting problem. I am not sure if your sample data covers all cases. But assuming it does, here is one formula that can answer the question.

=CHOOSE(MIN(ABS(COUNTIFS($D$2:$D$11,D2,$C$2:$C$11,C2)-COUNTIFS($D$2:$D$11,-D2,$C$2:$C$11,C2)),3),"No Duplicate", "Duplicate", "Duplicate more than once")

Here, column D has the amount and C has the trip number. I guess A or B column data is irrelevant for this analysis.

Let us know if this doesn't solve the problem.

 

[david gabra]

sorry it does not
you need to also take the invoice number into account

 

[david gabra]

+formulae did not work for me
please can you upload the sample file with it there

 

[david gabra]

it did work but can you add the condition for same invoice number aswell

I would like to know where we have duplicates based on the same trip number and amount - but different invoice number.

 

[david gabra]

that is really key since sometime the same amount appears 3 times the same but the invoice number is the same
this we can ignore

 

[r2c2]

that is really key since sometime the same amount appears 3 times the same but the invoice number is the same
this we can ignore

In your example data, the invoice numbers are not same. So if we add a condition with that you are going to get "No duplicate" for everything. If you mean Document number, you can modify the formula like this.

=CHOOSE(MIN(ABS(COUNTIFS($A$2:$A$11, a2, $D$2:$D$11,D2,$C$2:$C$11,C2)-COUNTIFS($D$2:$D$11,-D2,$C$2:$C$11,C2)),3),"No Duplicate", "Duplicate", "Duplicate more than once")