Subracting rows where duplicate values are present in a column

[hoaglam]

I've been working on a solution to subtracing duplicate credits from a ledger. Is anyone able to lend a hand on this? Sample data below:

TRAN_CD	TRAN_AMT	DEBIT_CREDIT_INDICATOR	new_key
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322351000000.00
353	15000.00	D	109560345122351000000000.00
753	15000.00	C	109560345122351000000000.00
753	15000.00	C	109560345122351000000000.00
776	29.00	C	1095603451223510000000.00
776	40.00	C	1095603451223510000000.00
776	40.00	C	1095603451223510000000.00

In the case of the $15000 transaction that occurred three times, I would want to basically negate the debit and one of the credits to show a net $15000 transaction, not $45000 worth of transactions. I concatenated a few feilds within the data to create a unique key that you see in the far right column. Very stuck. Any help is appreciated!

[ballardw]

---

@hoaglam wrote:

I've been working on a solution to subtracing duplicate credits from a ledger. Is anyone able to lend a hand on this? Sample data below:

TRAN_CD	TRAN_AMT	DEBIT_CREDIT_INDICATOR	new_key
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322350000000.00
776	40.00	C	109560357322351000000.00
353	15000.00	D	109560345122351000000000.00
753	15000.00	C	109560345122351000000000.00
753	15000.00	C	109560345122351000000000.00
776	29.00	C	1095603451223510000000.00
776	40.00	C	1095603451223510000000.00
776	40.00	C	1095603451223510000000.00

In the case of the $15000 transaction that occurred three times, I would want to basically negate the debit and one of the credits to show a net $15000 transaction, not $45000 worth of transactions. I concatenated a few feilds within the data to create a unique key that you see in the far right column. Very stuck. Any help is appreciated!

---

And just how are we to tell which are duplicates?

From what you are showing so far I would not even know that debit you reference is in any way related to the credit value.

I would guess that there is more identification information that you are not providing that is very likely going to be important for actually having a chance to program a solution.

[hoaglam]

Hi there. In the data I'm showing the duplicates I'm trying to remove are in rows 5, 6 and 7. In reality, we have a net $15,000 transaction. All three of these keys share the same "new_key" identifier, which is how I'm identifying duplicates. Hopefully thats helpful. Thanks again!

[ballardw]

---

@hoaglam wrote:

Hi there. In the data I'm showing the duplicates I'm trying to remove are in rows 5, 6 and 7. In reality, we have a net $15,000 transaction. All three of these keys share the same "new_key" identifier, which is how I'm identifying duplicates. Hopefully thats helpful. Thanks again!

---

You have three 109560357322350000000.00 for the "new_key" in the first three records. Why are they not "duplicates"? Same with 1095603451223510000000.00 for the last three. Why are they not duplicates.

I think that your problem description is a bit incomplete or you have multiple meanings for "duplicate".

[hoaglam]

Im using that unique key as a grouping only. The duplicates that I'm worried about are in the transaction values where both debits AND credits are present within the same account for the same amount. In the table I posted, the only duplicate I posted was for row 5. Row 5 should negate row 6 in this case, leaving a single transaction for that "new_key" group.

[Reeza · Accepted solution]

Mark Credits as -1 and Debits as +1 in a column called direction, and then group by your identifier columns and sum the transaction amounts multiplied by the direction.
You may want to flip debits/credits positive/negative to get the sign you need. Not sure which fields will uniquely identify a record since your first three look identical to me.

[hoaglam]

Thanks, I'll tinker with this concept to see if I can make some progress.

[Reeza]

Is that a type on your 5th record then, with the TRAN_CD being 353 rather than 753?

[hoaglam]

Yes, that 353 is a transaction code that means "debit", which is also represented as a "D" in the "Debit Credit Indicator" column.