Designing a function/macro that compares 2 columns and highlights rows that are missing

[jfk1991]

Hi Everyone,

Here's my situation, I'll try to be as clear as possible. I have two tables of data in my workbook on separate sheets. The first sheet, "Accounts Master" is a master list that is updated monthly. The second sheet "Current Accounts" contains the same info (in a different order), but it is added to as accounts are added into the system. Both lists will be expanding in quantity as the months go by, in case that's relevant.

Here are the two sheets side by side for reference.

http://i.imgur.com/ktH6eKh.png


Ideally, what I'd like to do is have the master list "consult" the current list and see if there are any accounts on the master list that are not on the current list. If the account is missing, I would like to highlight that row in a yellow color on the master list.

Incase it's not clear from the picture, the account #'s begin at E11 on the "Current Accounts" sheet and on the "Accounts Master" sheet the #'s begin at B10

I have tried to adapt some similar macros from the board to fits my needs but I have been having a lot of trouble because I am very inexperienced with macros.

I would really appreciate any help you guys could offer. It would make my job 1000x easier!

Thank you

 

[apo]

Hi..
The image you posted is blurred to the point of being unreadable where you values are in both sheets (something must have gone wrong when taking the screenshot)...

Here is a super simple example of what i think you are trying to achieve...

I will show the sheet structure for both sheets using Excel Jeanie (look into it.. it is far better for taking screenshots for excel help forums)..

I will also attach the sample Workbook for you to see it in action..

Option Explicit
Private Sub CommandButton1_Click()
   Dim i As String, it, x0, cnt As Long
    With CreateObject("scripting.dictionary")
        For Each it In Sheets("Current Accounts").Range("E11:E" & Sheets("Current Accounts").Range("E" & Rows.Count).End(xlUp).Row)
            x0 = .Item(it.Value)
        Next
        For Each it In Sheets("Accounts Master").Range("B10:B" & Range("B" & Rows.Count).End(xlUp).Row)
            If Not .Exists(it.Value) Then
            i = i & " " & (it): cnt = cnt + 1
            Cells(it.Row, 2).Resize(1, 2).Interior.ColorIndex = 6
            End If
        Next
        MsgBox "Accounts Missing: " & cnt
    End With
End Sub

To make the highlight go out wider.. just change the red "2" value in the following line:

Cells(it.Row, 2).Resize(1, [COLOR=#ff0000]2[/COLOR]).Interior.ColorIndex = 6

<b>Current Accounts</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td >Account #</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">8</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">10</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

<b>Accounts Master</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >Account #</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">2</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="text-align:right; ">4</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="text-align:right; ">5</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="text-align:right; ">6</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="text-align:right; ">7</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td style="text-align:right; ">8</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td style="text-align:right; ">9</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td style="text-align:right; ">10</td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Link to Workbook:
Compare Accounts

 

[jfk1991]

This is excellent. I'm sorry for the bad quality of the photos. I will absolutely use Jeanie in the future. The sample macro looks great, however when I put it into my workbook, I can't seem to get the "click" button to show up. I'm sure it's something silly that I'm missing. Do you know what might be causing this? I have macros enabled.

 

[jfk1991]

Okay I was able to get the macro to work in my workbook, but it is not recognizing any of the account #'s from the current accounts sheet. For example, right now I have 518 accounts in my master list and 420 in my current list. However, when I run the macro it says I have 518 accounts missing. Any ideas on what might be happening? Let me know if you need additional info. I really appreciate your help, apo

 

[apo]

Hi.. without seeing your Workbook.. its hard to tell.. have you double checked that the ranges you are referring to for both sheets are correct?

Try this.. add the following line just below the first "Next" statement..

MsgBox .Count

This will tell you how many unique values (keys) you have in your Current Accounts sheet.. (should be 420).. if it says zero.. then you are definitely referring to an incorrect range..

If you upload your Workbook to somewhere like Mediafire or dropbox (free accounts available at both).. then i can see what is going wrong..

 

[jfk1991]

Here is the workbook that I am working with. I removed the macro so that you could look at it fresh.

https://www.mediafire.com/?2b474qv4vxasgje

Thanks for all of your help. I really appreciate it. I'm sorry for being a pain.

 

[apo]

Hi..

The issue was that the Account # in your "Account Master" sheet were formatted as text.. whilst the Account # in your "Current Accounts" sheet were numbers..

This takes care of it..

it.Value = it.Value

and also your highlighting will cover all cells in the row you need.
Add it to a commandbutton and save as a .xlsm file.

Private Sub CommandButton1_Click()
Dim i As String, it, x0, cnt As Long
    With CreateObject("scripting.dictionary")
        For Each it In Sheets("Current Accounts").Range("E11:E" & Sheets("Current Accounts").Range("E" & Rows.Count).End(xlUp).Row)
            x0 = .Item(it.Value)
        Next
        For Each it In Sheets("Accounts Master").Range("B10:B" & Range("B" & Rows.Count).End(xlUp).Row)
        it.Value = it.Value
            If Not .Exists(it.Value) Then
            i = i & " " & (it): cnt = cnt + 1
            Cells(it.Row, 2).Offset(, -1).Resize(1, 11).Interior.ColorIndex = 6
            End If
        Next
        MsgBox "Accounts Missing: " & cnt
    End With
End Sub

 

[jfk1991]

Awesome!!!! Thank you so so so much. You have been a tremendous help!