You might want to know which records in an Excel worksheet column have identical items.  This Excel macro written in Visual Basic for Applications flags all items that repeat in a single column, sorted by the item:

 

Sub Eliminate_Duplicate_Items()

'

' Eliminate_Duplicate_Items Macro

' Macro VBA code written by Lynn Preston.

'

    'EXAMPLE OF WHEN TO USE MACRO: 

    '   TO ELIMINATE DUPLICATE ss#'s TO EDIT CO.'S INPUT FILE.

     'Procedure:

    '1) Prepare Company Input Data for an Edit for Duplicate SS#'s:

    '   a) Sort all Company data by SS#.

    '   b) Insert a blank column next to the Company Data File SS# column;

    '       Fill this column with "C" for Company data.

    '2) Select both columns (SS# column and Anomaly column) and run macro.

    '3) Set data filter on the newly inserted column; select BLANK filtered items and

    '  check to see if the record needs to be deleted because it contains a duplicate SSN.

     

    ' Dimension variables

 

    Dim xRng, xCounter As Integer

    Dim ID_Current, ID_Prior, Match_Bad_Literal, First_ID_of_Match_to_Compare As String

    First_ID_of_Match_to_Compare = "Y"

    Match_Bad_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:

                '"C" for Company, 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

                    'These are matching ID numbers

                                        

                    'Insert error flag in current ID cell

                    xCounter = xCounter + 1

                    xRng.Cells(xCounter).Value = Match_Bad_Literal

                   

                    'Insert error flag in prior ID cell

                    xCounter = xCounter - 2

                    xRng.Cells(xCounter).Value = Match_Bad_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

 

You might want to process records in an Excel worksheet column that contains identical items.  This Excel macro written in Visual Basic for Applications flags ONLY the first occurrence of a unique or repeating item in a single column, sorted by the item:

 

Sub Flag_1st_Unique_Item_Do_Not_Flag_Duplicates()

'

' Flag_1st_Unique_Item_Do_Not_Flag_Duplicates Macro

' Macro VBA code written by Lynn Preston.

 

  'EXAMPLE OF WHEN TO USE MACRO: 

    '   Flag Top Unique Item, but Don't Flag Duplicates

    'TO EDIT CO.'S INPUT FILE.

     'Procedure:

    '1) Prepare Company Input Data for an Edit for Duplicate SS#'s:

    '   a) Sort all Company data by SS#.

    '   b) Insert a blank column next to the Company Data File SS# column;

    '       Fill this column with "C" for Company data.

    '2) Select both columns (SS# column and Anomaly column) and run macro.

    '3) Set data filter on the newly inserted column; select BLANK filtered items and

    '  check to see why this record is the first with a duplicate SSN.

     

    ' Dimension variables

 

    Dim xRng, xCounter As Integer

    Dim ID_Current, ID_Prior, Match_Bad_Literal, First_ID_of_Match_to_Compare As String

    First_ID_of_Match_to_Compare = "Y"

    Match_Bad_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:

                '"C" for Company, 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

                    'These are matching ID numbers

                                       

                    'Insert error flag in current ID cell

                    xCounter = xCounter + 1

                    xRng.Cells(xCounter).Value = Match_Bad_Literal

                   

                    'Insert error flag in prior ID cell

                    xCounter = xCounter - 2

                    'xRng.Cells(xCounter).Value = Match_Bad_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