match a positive and negative amount in excel 2010

[EmmyLou]

Hi there everyone,

I have a report I extract from my accounting program JDEdwards. It tweaks out and doesn’t correctly match up positive and negative transactions. At the end of every month, there should be $0 in this account, however, because it tweaks out, it sometimes misses a corresponding transaction. Part of my job is to go through the transactions and find any unmatched transactions and manually enter a corresponding transaction into the account to balance it to ‘$0’.

The report can be hundreds of rows long, and I am hoping there is a better way to filter/extract/highlight this information that what I am currently doing.

Firstly I would like to explain how I am achieving this at the moment:
1. Firstly I will go to Column H and put the filter on and choose ‘0’ and then I will delete all these rows
2. Then I will take this filter off
3. I then go to column B, and choose the very first PO Number, in this instance, I go to PO 85065859. I can see I have two transactions there, Row 317 & 613. You can see both these transactions are -351. These both are missing corresponding transactions to balance $0 so therefore I will highlight both cells (H317 & H613) in yellow
4. I then go on to filter to the next PO
5. I continue this until I have reached the last PO
6. I then un-filter B column, I go to H column and filter by colour, filter by yellow
7. This leaves me with a spread sheet for all unmatching transactions
8. I can then print this off and use it as my guide for manually entering each transaction

Ok, so now I have explained how I complete this process, I am sure there is someone out there who can show me an easier way to complete this. I have also included two examples to help explain what I am after.

8506 5859 two rows of -351 (need these to be left in the spread sheet as these two are unmatched for PO 8506 5859)

8507 4643 I can see two match up’s
Row 90 (-217.5) and 419 (217.5)
Row 94 (-507.5) and 415 (507.5)
Leaving remaining row 98 (-217.5)

I have to manually go through each PO and match up the transactions and then highlight the unmatched transactions….

I know of I have explained this in a lot of detail and hope this was not to tedious for you to read. I am trying to detail as much as possible to explain what exactly I am after.

Please be kind to be, I’m really not that good on excel and all you bloggers out there sound so smart, I’m sure there is probably an answer out there, I just haven’t been able to work it out. I really appreciate anyone out there who’s willing to help and thanks so much in advance

Moderator's note: Attached file checked and safe, unzipped then scanned with MSE & Malwarebytes, seems to be a genuine Excel file - fbs

 

[alow]

This seems to be a complex one, so I just want to double check before I go off on a formula tangent. You want to find numbers on different rows with the same PO that have the same Absolute Value, essentially. Right?

 

[Stoneboysteve]

I took an approach using pivot analysis on the attached file.
First, I created column I as an audit column that matches the row numbers.
Next I created a pivot table on the Pivot tab. I added P/O Number and Amount to the Row Labels, and Amount (shown as sum) in Values.

That gives me a list of POs with totals. On the file I attached, I left column A expanded on the Pivot tab. You can shorten the list by right clicking on the PO number (cell A4 in this case) and collapse the data. On each PO total row I was able to double click non zero amounts that show me a list of transactions. In the example of 85065859 the two -351 rows are shown.

I did have to rename the tab. When you double click it creates a new sheet with the next number available.

If you want a single worksheet you can drop down the PO Number (A4) list and uncheck POs with zero values. Then double click the value of the grand total and it will give you a single sheet. I tried the single sheet, it still needed to be filtered by PO in order to give you what you need to balance.

Thank you moderators for checking and allowing the file upload!

EmmyLou can you please provide feedback? alow is pretty keen and may be able to offer a different approach.

Stoneboysteve

 

[alow]

It has been so long since I've made a pivot table, I often forget about that approach. Good suggestion! and thanks for the kudos!