I found out that an office at my work spent weeks manually going through an Excel spreadsheet containing a database with >500,000 rows looking for duplicate rows matching certain criteria. The duplicates could not simply be erased before being researched, as a single mistake could have potentially lost hundreds of thousands of dollars in lost production. I decided simply flagging them and referencing the originating row would be the best answer in this case. So I decided to look into macros to see how much time *could* have been saved by using a simple macro instead. I am using this as a programming learning experience, so please no "here's a =function()" answers.

I've written a macro and changed it several times to no avail (most current is below). I wanted to use String variables because there's no telling what has been entered into the cells that will be checked. Here's what I've tried, failed, and learned(?) from this site:

Initially, I tried declaring a variable, and attaching a value from a cell directly to it. e.g. `Dim myString As String Set myString = Cells(x, x).Value` However, I kept getting object errors. Thanks to [Michael's](https://stackoverflow.com/users/323560/michael) response [here](https://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba), I learned that you have to use the `Range` variable to use `Set`.

My next issue has been getting a "type mismatch" error. I'm trying to assign and compare a stored variable against another stored variable, and I'm sure this is causing the issue. I initially tried `Dim myRange As Range, myString As String Set myRange = Cells(x, x).Value myString = myRange`. This obviously didn't work, so I tried using the `CStr()` "change to string" function to convert the Range variable to the String variable I want. And that's where I'm stuck.

```
Sub Duplicate()

'Declare the variables
Dim NSNrange, PNrange, KitIDrange As Range
Dim NSN, PN, KitID As String
Dim NSNCheck, PNCheck, KitIDCheck As String
Dim i, j, printColumn, rowCount As Integer

'Set which column we want to print duplicates on, and count the number of rows used
rowCount = ActiveSheet.UsedRange.Rows.Count
printColumn = 9

'Lets get started!

'Clear the duplicate list column for a fresh start
Columns(printColumn).EntireColumn.Delete

'Start on line 2, and grab the cell values for the NSN, Part number and kit ID.
For i = 2 To rowCount

   Set NSNrange = Cells(i, 5).Value
   Set PNrange = Cells(i, 7).Value
   Set KitIDrange = Cells(i, 2).Value

   'Change whatever is contained in those cells into a string and store them into their respective containers
   NSN = CStr(NSNrange)
   PN = CStr(PNrange)
   KitID = CStr(KitIDrange)

      'Now let's look through the rest of the sheet and find any others that match the 3 variables that we stored above
      For j = 2 To rowCount

      'To avoid needless checks, we'll check to see if it's already had a duplicate found. If so, we'll just skip to the next row
      If Cells(j, printColumn).Value = "" Then

      'If the print column is blank, we'll grab the 3 values from the current row to compare against the above variables
      Set NSNrange = Cells(j, 5).Value
      Set PNrange = Cells(j, 7).Value
      Set KitIDrange = Cells(j, 2).Value

      'Now we store the contents into their very own container
      NSNCheck = CStr(NSNrange)
      PNCheck = CStr(PNrange)
      KitIDCheck = CStr(KitIDrange)

         'Check the initial row with the current row to see if the contents match. If so, print which row it is duplicated on.
         If NSN = NSNCheck And PN = PNCheck And KitID = KitIDCheck Then Cells(j, printColumn).Value = "Duplicated on row " & i

        End If

        Next j

   Next i

MsgBox "Search Complete"

End Sub

```