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]

Trolling the forums. I was lazy, so I started to learn how to code. Ironically working harder to work less.

 

[Somedude]

Hi sorry I actually was using it more now and realized that it does not work. When I first tested it, I was very busy so I just did a brief overview and it looked like it worked.

Now I even tested it by using two sheets that I know have the exact same numbers as each other. Only some of the numbers highlight but not all do. I would say the accuracy ranges from 5 to 60%. I looked in some of the cell values of each sheet and some of them are not an exact match. For example, 38626.38 on one sheet and 386262.38000000000000001 in the other sheet. This seems to be a very rare occurence, though (I've only been able to locate it twice among 4 sheets with tons of data).

Any ideas?

 

[mirabeau]

Hi sorry I actually was using it more now and realized that it does not work. When I first tested it, I was very busy so I just did a brief overview and it looked like it worked.

Now I even tested it by using two sheets that I know have the exact same numbers as each other. Only some of the numbers highlight but not all do. I would say the accuracy ranges from 5 to 60%. I looked in some of the cell values of each sheet and some of them are not an exact match. For example, 38626.38 on one sheet and 386262.38000000000000001 in the other sheet. This seems to be a very rare occurence, though (I've only been able to locate it twice among 4 sheets with tons of data).

Any ideas?

Are you wanting the example you give to be shown as an exact match or not?

Is it the case that you only want to check if values to 2 decimal places (currency) will match?

Is this non-matching at 15 or so decimal places the only problem you have found with that code?

 

[Somedude]

Sorry to get back to you so late. To be more clear, lets say there are 100 values total. Lets say 40 of those values are supposed to match. By using your code, about 15 actually get highlighted as matching. Out of the other 25, only about 1-3 of them have a decimal issue. I can live with the values that have the decimal issue. If its that easy for you to modify the code to search values only to their 2nd decimal places (currency) then that would be cool, but there would still be about 12-14 values that should have been highlighted as being identical (although of opposite signs) matches.

I hope that information helps.

 

[mirabeau]

Sorry to get back to you so late. To be more clear, lets say there are 100 values total. Lets say 40 of those values are supposed to match. By using your code, about 15 actually get highlighted as matching. Out of the other 25, only about 1-3 of them have a decimal issue. I can live with the values that have the decimal issue. If its that easy for you to modify the code to search values only to their 2nd decimal places (currency) then that would be cool, but there would still be about 12-14 values that should have been highlighted as being identical (although of opposite signs) matches.

I hope that information helps.

Hi,

You can try the following modified code and see if it helps.

But if it still sometimes doesn't show a match when you think there is a match, could you independently verify that there really is a match?

By "independently verify" I mean something like adding the actual entries directly from the sheets (not just the apparent numbers as they appear to you) and checking they do indeed sum to zero.

Sub hilights2()
Dim d1 As Object, d2 As Object
Dim a As Range, e As Variant, x
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set a = Sheets("sheet1").UsedRange
For Each e In a
    If IsNumeric(e) And e <> "" Then
        x = CCur(e)
        d1(-x) = 1
    End If
Next e

For Each e In Sheets("sheet2").UsedRange
    If IsNumeric(e) And e <> "" Then
        x = CCur(e)
        If d1(x) = 1 Then
            e.Interior.Color = vbYellow
            d2(-x) = 1
        End If
    End If
Next e

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

 

[Somedude]

This one works perfectly. Thank you very much. Out of curiosity, I went back to the past code and independently verified the numbers, and they did indeed sum to zero. It happened almost exclusively with large numbers (more than six figures) but it looks like that is solved now. Thank you so much! You just saved me hours of work!

 

[mirabeau]

This one works perfectly. Thank you very much. Out of curiosity, I went back to the past code and independently verified the numbers, and they did indeed sum to zero. It happened almost exclusively with large numbers (more than six figures) but it looks like that is solved now. Thank you so much! You just saved me hours of work!

OK!
Thanks for the feedback.

I hope there's no further issues. But I am somewhat puzzled about the "more than 6 figure" numbers causing problems. Sometime I'd better track that one down.