DR/CR Matching

[mlandry62]

Hello all, I've got a large accounting spreadsheet made up of a column for DR entries and another column for CR entries.
They are InterCompany entries so there's a DR value matching a CR value.
My job is to create some type of rule or formula that will match the values and highlight them.
I did try Conditional Formatting to Highlight Cells Rules with Duplicate Values. My problem with that was I had a situation with 3 CR values of $500 and 1 DR value of $500 and all 4 values were highlighted.
I need a one to one match situation.
We have hundreds of entries each month and I'm taking over from a co-worker so I'm trying to find an efficient way of tackling this project.
Just not sure how to accomplish this.
Any help would be greatly appreciated.
Marcel

 

[Joe4]

If you have 3 CR Values of $500 and 1 DR Value of $500, how do you know which of those 3 CR values that 1 DR value should match with?
Seems to me like you might need a second field to match, as there isn't really a good way to match on one field when there are duplicates on each side.

 

[mlandry62]

You're right Joe. I have some other columns that relate to Batch and Transaction ID as well as another Column that relates to Transaction Reference.
Is it possible to use ALL of the columns to come up with matches?

 

[Joe4]

Sure, though matching long lists on multiple fields can sometimes get a little cumbersome in Excel.
I find it much easier to do with Microsoft Access, as it is a relational database program (so it was built for stuff like this)!

It might actually be a good thing to do with Power Query, which allows you to do database-like queries and operations in Excel.
We actually have a Power Tools forum for questions about things like Power Query.