Set up a custom button on your Excel menu and attach this Excel macro.  You can then quickly compare two lists by the items in a single sorted column of data.  This Excel macro is written in Visual Basic for Applications:

 

Sub Two_Lists_Compare_IDs()

    ' Macro VBA code written by Lynn Preston.

  

    'WHEN TO USE MACRO:  To compare items (we’ll call it, “Employee ID”) in a single sorted column

    ‘ from two individual lists.

    'Procedure:

    '1) Prepare First List for Compare by turning the color property to bright yellow.

    '2) Prepare Second List for Compare by turning the color property to bright blue.

     '3) Sort both lists by Employee ID.

    '4) Insert a new, blank Anomaly column to the immediate right of the Employee ID # column:

        a)  Fill this column with “1” for First List data.

        b)  Fill this column with “2” for Second List data.

    '5) Select both the Employee ID# column and the Anomaly column; then run macro.

    '6) Set data filter on the Anomaly column; select non-blank filtered items

    '   and copy these entire rows to the bottom of the Excel spreadsheet; and then

    '   turn off data filter.

    '7) Evaluate the anomalies by first sorting by employee name, for ID # snafus; then,

    '   by sorting by Anomaly column and then by employee name.  ANALYZE THE DATA THIS WAY!

    '   EXPLANATION OF ANOMALY CODES:

    '   "1" = Employee is on First List, but unmatched on Second List.

    '   "2" = Employee is on Second List, but unmatched on First List.

   

    ' Dimension variables

 

    Dim xRng, xCounter As Integer

    Dim ID_Current, ID_Prior, Match_Good_Literal, First_ID_of_Match_to_Compare As String

    First_ID_of_Match_to_Compare = "Y"

    Match_Good_Literal = " "

   

    Set xRng = Selection ' uses user-selected range in Excel.

    'Loop once for every row in the selected two columns.

         

    For xCounter = 1 To xRng.Cells.Count

        If xCounter = 1 Then

        ' On first cell read in range, initialize ID Number Compare Values

            ID_Current = xRng.Cells(xCounter).Value

            ID_Prior = xRng.Cells(xCounter).Value

            First_ID_of_Match_to_Compare = "N"

        End If

               

        If xCounter > 1 Then

       

            'reset current ID compare field

            ID_Current = xRng.Cells(xCounter).Value

           

            If ID_Current <> ID_Prior And First_ID_of_Match_to_Compare = "N" Then

                'Skip past error cell as it already has:

                'the "1" for First List or "2" for Second List, explaining the anomaly.

               

                'After mismatch, reset prior compare ID and first ID of match flag

                ID_Prior = ID_Current

                First_ID_of_Match_to_Compare = "Y"

            Else

                If ID_Current = ID_Prior Then

                    'First List and Second List have matching ID numbers

                                       

                    'Remove error flag in current ID cell

                    xCounter = xCounter + 1

                    xRng.Cells(xCounter).Value = Match_Good_Literal

                   

                    'Remove error flag in prior ID cell

                    xCounter = xCounter - 2

                    xRng.Cells(xCounter).Value = Match_Good_Literal

                                                          

                    'go back to sit in current ID field

                    xCounter = xCounter + 1

                    First_ID_of_Match_to_Compare = "Y"

                    Else

                        If ID_Current <> ID_Prior And First_ID_of_Match_to_Compare = "Y" Then

                        'reset starting to compare match flag as you are looking for 2nd ID

                        First_ID_of_Match_to_Compare = "N"

                        ID_Prior = ID_Current

                        End If

                 End If

            End If

           

        End If

      

     'skip anomaly column cell

     xCounter = xCounter + 1

    

    'go to next row to the cell with an ID # in it

     Next xCounter

End Sub

 

Set up a custom button on your Excel menu and attach this Excel macro.  You can then quickly compare two lists by the items in TWO columns of data, sorted by the first column, a unique ID.  This Excel macro is written in Visual Basic for Applications:

Sub Two_Lists_Compare_IDs_with_Rates()

    ' Macro VBA code written by Lynn Preston.

  

    'WHEN TO USE MACRO:  To compare two items (we’ll call them, the unique “Employee ID”

    ‘and the non-unique “Employee Rate”) in two sorted columns from two individual lists.

 

    'WHEN TO USE MACRO:  TO DO RUN ID AND RATE RECONCILIATIONS BETWEEN COMPANY AND

    ‘CLIENT.

 

    'Procedure:

    '1) Prepare First List for Compare by turning the color property to bright yellow.

    '2) Prepare Second List for Compare by turning the color property to bright blue.

    '3) Sort First List and Second List by Employee ID.

    '4) Insert a blank “Anomaly” column next to the Employee ID column:

        a)  Fill this column with a “1” for the First List.

        b)  Fill this column with a “2” for the Second List.

    '5) The order of field columns should be:  Employee ID, Anomaly column, Employee Rate.

    '5) Select 3 columns (Employee ID, Anomaly column, and Employee Rate); then run macro.

    '6) Set data filter on the newly inserted Anomaly column; select non-blank filtered items

    '   and copy these entire rows to the bottom of the Excel spreadsheet; and then

    '   turn off data filter.

    '7) Evaluate the anomalies by first sorting by employee name, for ID # snafus

    '   and total rate changes; then, by sorting by anomalies column and then by employee name,

    '   to sort out adds and deletes.  ANALYZE THE DATA THIS WAY!

    '   EXPLANATION OF ANOMALY CODES:

    '   "1" = The First List has this employee and rate, but the Second File does not match both.  Check it!

    '   "2" = The Second List has this employee and rate, but the First File does not match both.  Check it!

   

    ' Dimension variables

 

    Dim xRng, xCounter As Integer

    Dim ID_Current, ID_Prior, Match_Good_Literal, First_ID_of_Match_to_Compare As String

    Dim Rate_Current, Rate_Prior As String

   

    First_ID_of_Match_to_Compare = "Y"

    Match_Good_Literal = " "

   

    Set xRng = Selection ' uses user-selected range in Excel.

    'Loop once for every row in the selected three columns.

         

    For xCounter = 1 To xRng.Cells.Count

        If xCounter = 1 Then

        ' On first cell read in range, Initialize Compare Values

            ID_Current = xRng.Cells(xCounter).Value

            ID_Prior = xRng.Cells(xCounter).Value

           

            xCounter = xCounter + 2

            Rate_Current = xRng.Cells(xCounter).Value

           

            Rate_Prior = xRng.Cells(xCounter).Value

            xCounter = xCounter - 2

           

            First_ID_of_Match_to_Compare = "N"

        End If

               

        If xCounter > 1 Then

       

            'reset Compare fields

            ID_Current = xRng.Cells(xCounter).Value

           

            xCounter = xCounter + 2

            Rate_Current = xRng.Cells(xCounter).Value

                       

            xCounter = xCounter - 2

            

                       

            If ID_Current <> ID_Prior And First_ID_of_Match_to_Compare = "N" Then

                'Skip past error cell as it already has:

                'the "1” for First List or "2" for Second List, explaining the anomaly.

               

                'After mismatch, reset prior compare ID and first ID of match flag

                ID_Prior = ID_Current

                Rate_Prior = Rate_Current

               

                First_ID_of_Match_to_Compare = "Y"

            Else

                If ID_Current = ID_Prior And Rate_Current = Rate_Prior Then

              

                    'First List and Second List have matching ID numbers and rates

                       

                        'Remove error flag in current ID cell

                        xCounter = xCounter + 1

                        xRng.Cells(xCounter).Value = Match_Good_Literal

                    

                        'Remove error flag in prior ID cell

                        xCounter = xCounter - 3

                        xRng.Cells(xCounter).Value = Match_Good_Literal

                                                         

                        'go back to sit in current ID field

                        xCounter = xCounter + 2

                        First_ID_of_Match_to_Compare = "Y"

                 Else

                        If ID_Current <> ID_Prior And First_ID_of_Match_to_Compare = "Y" Then

                           'reset starting to compare match flag as you are looking for 2nd ID

                           First_ID_of_Match_to_Compare = "N"

                           ID_Prior = ID_Current

                           Rate_Prior = Rate_Current

                        End If

                 End If

            End If

         End If

      

     'skip anomaly column cell

     xCounter = xCounter + 2

    

    'go to next row to the cell with an ID # in it

     Next xCounter

End Sub