Highlight duplicates in two sheets with multiple columns

[Somedude]

Hi everyone, I'm new here so let me briefly introduce myself.

• I graduated college a few months ago
• I work in the accounting dept. of a large company
• I am a novice level VBA user. I can understand about 20% of the code I have looked at and know how to use maybe 5% of it.

So here's my problem:
One of my recurring tasks is month-end account reconciliations for six different general ledger accounts. Several transactions go through each account, but I only have to reconcile the specific transactions related to six business units. Each account is a ton of data, and I'm really lazy. The easiest but most time consuming part is locating the numbers that DO match. Once I do that, I can analyze the ones that don't match and actually start to make some progress.

The files:

I have two files that I put together in one workbook as seperate sheets. Lets call the workbook "SAP & PBG Detail." The two sheets inside the workbook are as follows:

• "SAP_Month_Detail" - the general ledger account's list of transactions that I export from our SAP software. All transaction amounts are displayed as a list in one column. For example, all transaction amounts can be found on "N2:N101"
• "PBG_Detail" - a table of transaction amounts that i get from the business group. This table displays the transaction amounts per week. So there are rows for each week and transaction amounts are listed in that row under seperate columns. Essentially what I'm saying is that all the transaction amounts are located in different rows and columns. They are (for whatever reason) not listed with dates. As an example, all transaction amounts can be found on "F2:M35"

Each sheet lists transaction amounts as opposite signs. If "SAP_Month_Detail" displays the amounts as positive, "PBG_Detail" displays them as negative, and vice versa.

What I want:
Is there a VBA code that will find the amounts on each table that are perfect opposites of each other (in other words, that are identical in amount but opposite in terms of positive/negative value), and then highlight them for me?

If anyone needs more detail/clarification, please let me know. I will try to be as clear as possible. Thank you all for your help.

 

[RSXchin]

What are the odds that 2 people paid the exact same amount?

 

[Somedude]

Correct me if I am wrong, but are you asking me what are the odds that two amounts could be coincidentally matching but not be of the same transaction?

I had a lot of free time today (which is very rare) so I manually highlighted thet amounts that matched for one of the GL accounts. It took forever. The ones that coincidentally matched but were not related in any way were less than 2% of all transactions. Seeing if they match due to mere coincidence will be easier than manually matching them, so even though there is a possibility of this happening, its definitely manageable.

 

[RSXchin]

question 2. Are those ranges always the same?

 

[Somedude]

Yes and no. The number of columns in each sheet will always be the same. "SAP_Month_Detail" will always have 1 column, and "PBG_Detail" will always have 8 columns. The number of rows, however, will change on both sheets depending on activity.

 

[RSXchin]

Please give me the column ranges for both sheets (please tell me which is which)

 

[mirabeau]

What I want:
Is there a VBA code that will find the amounts on each table that are perfect opposites of each other (in other words, that are identical in amount but opposite in terms of positive/negative value), and then highlight them for me?

If anyone needs more detail/clarification, please let me know. I will try to be as clear as possible. Thank you all for your help.

Try this VBA code and see if it does anything for you

Sub hilights()
Dim d1 As Object, d2 As Object
Dim a As Range, e As Variant
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set a = Sheets("sheet1").UsedRange
For Each e In a
    If IsNumeric(e) Then d1(-e.Value) = 1
Next e

For Each e In Sheets("sheet2").UsedRange
    If d1(e.Value) = 1 Then
        e.Interior.Color = vbYellow
        d2(-e.Value) = 1
    End If
Next e

For Each e In a
    If d2(e.Value) = 1 Then e.Interior.Color = vbYellow
Next e
End Sub

 

[Somedude]

Thanks. I just ran that code and I got:

Run-time error '9': Subscript out of range

what does this mean?

 

[RSXchin]

hit debug when that comes up again, and tell us which line is highlighted.

 

[Somedude]

Nevermind I got it to work. Wow what a time saver!! Thanks a lot guys. PS. How can I start to learn to make these codes myself? How did you guys learn?