Compare items in 1 Table under multiple Criteria & Match

Hi,

I have a problem for work where I am trying to match opposite entries if they are unique matches and highlight opposite entries where they have more than 1 match.
I am providing a Spreadsheet with and example.

I am also providing what I attempted and why it wont work within.

This is really important for work. Please help.

Originally Posted by MarvinP

Hi easy899,

I believe your problem is how you've structured your data. You have different columns for Income and Expense. This makes it hard to run a total. If you make expenses negative and income positive, you can do an easy total. If you use Pivot Tables you can very easily total by month or account. See the attached where I've added a Total column and a Pivot. Does this help?

Attachment 550417

Hi ya,

Thanks for the help but that's not what I am looking for.

It is okay to make the Income Column Negative. I made it positive to simplify.
However, the issue is that I don't just want the totals.

What I want is to identify the items which match uniquely with 4 criteria (month, account, amount & description) and to idneitify those that match but with more than 1 item separately.

Is this possible?

What I want from the results is to identify the 1 to 1 matches.
I.e income 500 in april = expenditure 500 in april and therefore matches off.
This means income 500 in april cannot match off against expenditure 500 in may.
Hence a 1 to 1 match.
I want to identify these so that I can filter foir them and remove them.

Where I have duplicate matches such as income 500 in april can be matched off against 2 different expenditure 500 in april.
These are highlighted differently so I can investigate them.

Originally Posted by MarvinP

So you have an income of 1000 and you expense it out in 1 or 3 or 10 different payouts. If you expand on my idea of using a pivot table you could use all the criteria of month, account and description to do sums. Because you don't know how many rows will be included in the matching income/expense using totals is the only way you can tell if they are equal at the end.

Add more row filters to the Pivot and see if that helps.

Not quite, these items are not matched off in different payouts.
It is a 1 to 1 match not a 1 to many match.
So Income 500 can only match 1 expense 500 and not multiple payments of 250 or 500 etc.