How to find true reversal transactions?

[vinzdicate]

Hello everyone! Could someone please help?

I have a spreadsheet containing rows of transactions.
Some of the transactions are reversals. Is there a method to net out/sum out/zero out the original transaction with the reversal (that matches to the transaction number) such that I do not have to perform this task manually.
I've copied a portion of the spreadsheet below.

Fiscal Month Trans Number Cost
2016-M01 0306016265 9834.91
2016-M01 0306029857 8429.93
2016-M01 0305601550 (1292.59)
2016-M02 0306052419 2060.56
2016-M02 0306052419 2585.18
2016-M02 0305954989 1381.45
2016-M02 0305954989 1001.05
2016-M02 0305954989 (1381.45)
2016-M02 0305954989 (1001.05)
2016-M02 0306078110 3877.77
2016-M02 0305954989 1381.45
2016-M02 0305954989 (1381.45)
2016-M02 0305954989 1001.05
2016-M02 0305954989 (1001.05)
2016-M02 0306016265 (9834.91)
2016-M02 0306052419 (2060.56)
2016-M02 0306052419 (2585.18)
2016-M02 0305954989 (1381.45)
2016-M02 0305954989 1381.45
2016-M02 0305954989 (1001.05)
2016-M02 0305954989 1001.05
2016-M02 0306078110 (3877.77)
2016-M02 0306085310 1053.74
2016-M03 0304356338 2985.60
2016-M03 0304356338 (2985.60)
2016-M03 0306093769 1292.59
2016-M03 0306118371 35000.00
2016-M03 0306134887 7376.19
2016-M03 0306134887 2650.37
2016-M04 0305974344 (1053.74)
2016-M04 0305746045 (8429.93)
2016-M04 0306118371 (35000.00)
2016-M04 0306134887 1472.84
2016-M04 0306134887 (1472.84)
2016-M04 0306134887 (7376.19)
2016-M04 0306134887 (2650.37)

I currently use this formula COUNTIFS(E$2:$E3,E3)<=MIN(COUNTIFS($E$2:$E$9998,-E3),(COUNTIFS($E$2:$E$9998,E3))))

It is useful to match both corresponding positive and negative value, but it doesn't match to the corresponding transaction number. How do I add additional condition so that I can find out the 'true' reversal transaction?

Thank you guys!

[jewelsharma]

what would be a true reversal transaction? the one that has:

• same transaction number, and
• equal value, but opposing sign

if so, see attached that shows the first matching reversal row number (based on row numbers in Column A); else, please attach a sample spreadsheet clearly showing the same.

Note: There are certain entries such as the following that have more than 1 true reversals. The formula will only return the first one the many rows.

2016-M02 0305954989 1001.05

[vinzdicate]

Hi, thanks for your prompt reply.

You are right, a true reversal is one that has:

• same transaction number, and
• equal value, but opposing sign

I tried replicating it into my spreadsheet but it returns all "no match". Not sure if I am doing anything wrong. Please see attached.

Yes, there will be multiple orders that result in more than 1 true reversal, but as long as the criteria matches as a pair (i.e., same transaction # with equal value of opposing sign), it needs to be considered as 'false' entry while the remaining will be the true transactions that needs to be recognized.
I hope to use the filter to differentiate between true reversals and real orders for further investigation into those separately. Please advise what I am doing wrong.

Thank you very much.

[bebo021999]

How about in I2:

Please Login or Register  to view this content.

Drag down.

[vinzdicate]

Hi bebo021999,

Thanks for your reply.

I tried but it considers it as a match even when the transaction number is different. The condition to be considered as a reversal transaction needs to have both transaction number and equal value of opposing sign to be 'true'.

[jewelsharma]

you need to enter the formula as an Array formula, and then drag down to copy it to all cells in Column I.
To do so, formulas should be entered/confirmed using [Ctrl] +[Shift] + [Enter]. If entered correctly, you would see the formula in curly braces { =IFERROR(MATCH( ... and so on ... )}

I've tried it on your sheet and it works. e.g. #2 matches #34; #14 matches #37.

Note: You cannot type the curly braces manually.

[vinzdicate]

Hi jewelsharma,

yes, i did that. however, how do i link the array formula to column A with the numbers so that it will be able to show e.g., #2 matches with #34, #1 matches with #100 and etc.?

[vinzdicate]

Hi jewelsharma,

I managed to have i figured out! Really thanks to your help in assisting to solve this problem. It really makes it much easier for me to separate reversal transactions and 'true' transactions, which allows me to further investigation.

For my understanding, would appreciate if you could make a break down of the formula, as i really want to learn how you manage to come up with this formula. Really appreciate your help

[jewelsharma]

the formula works as follows:
the MATCH part looks for the a match where the "Transaction Number" is same; and the "Cost" is of same value but opposing sign.

Please Login or Register  to view this content.

Match formula will always return the position of the 1st such match. (that's why I mentioned regarding mutiple row with same Transaction Number and Value/Signs).

If no match if found, then the MATCH formula returns an error, which we trap using the IFERROR part and return "No Match".
Hope that helps!

Please take a moment to mark this thread as SOLVED.
How to?

• Go to the top of the first post
• Select Thread Tools
• Select Mark thread as Solved

[vinzdicate]

Hi jewelsharma,

appreciate taking your time to explain the formula. i encountered one problem while checking through my transactions:

for example:

# Order Number Ord Lgl Net CLC First Matching #
4 0305439960 2900.48 7
6 0305439960 2900.48 7
7 0305439960 (2900.48) 4

I would require one of the transacted order (either #4 or #6) as a 'true' order since the other pair is negated and considered as a 'reverse order'. How can i get excel to recognize reverse order to be an 'even' pair of transacted order with same value and opposing sign only?