Duplicate Check on column of data

[TLA]

I want to check a column of data for duplicates. I am writing the column out line by line in VBA taken from other sheets based on certain criteria.
Column A are serial numbers
When I am done I want column F to list the row # of any duplicate serial numbers in the list.

Example if the same serial # appears in lines 1,3,5,7,9
I want Row F1 to list 3,4,5,9
Row F3 to list 1,5,7,9
Row F5 to list 1,3,7,9
Etc

I can do this by completing the list, then using nested for next loops to look down the column take the first cell, compare it to all the others and when it finds a match, write that row value out to Cell F, and concatenate on to any other values that are there.

This is a giant list through and that would take forever -search thousands of lines for each of thousands of lines. There must be a more elegant solution.

I want to do this without sorting the list if possible.

 

[AlphaFrog]

You could use the AdvancedFilter Method to filter column A (in place) for Unique entries and then copy those uniques to an array. Then clear the filter. Note: this doesn't sort or change column A

Then use the .Find Method to find the row number of each of the uniques in your array. Then put the results in column F using the same rows as indicated in the .Find results. This would be much faster than nesting loops.

Here is a good, albeit elaborate, example of using the .Find method to FindAll of a given value. Custom FindAll Function
If you do a web search for Excel VBA Find All, you will find several other good examples of essentially the same thing.

 

[mirabeau]

I want to check a column of data for duplicates. I am writing the column out line by line in VBA taken from other sheets based on certain criteria.
Column A are serial numbers
When I am done I want column F to list the row # of any duplicate serial numbers in the list.

Example if the same serial # appears in lines 1,3,5,7,9
I want Row F1 to list 3,4,5,9
Row F3 to list 1,5,7,9
Row F5 to list 1,3,7,9
Etc

I can do this by completing the list, then using nested for next loops to look down the column take the first cell, compare it to all the others and when it finds a match, write that row value out to Cell F, and concatenate on to any other values that are there.

This is a giant list through and that would take forever -search thousands of lines for each of thousands of lines. There must be a more elegant solution.

I want to do this without sorting the list if possible.

Did you get this one done OK?

Here's a macro you might perhaps like to consider for the job.

Sub checkdups()
Dim d As Object, lr&, u(), e
Dim k&, da As String, s As String
s = Chr(30)
Set d = CreateObject("scripting.dictionary")
lr = Range("A:A")(Rows.Count).End(3).Row
ReDim u(1 To lr, 1 To 1)
a = Range("A1:A" & lr).Value
For Each e In a
    k = k + 1
    If Len(d(e)) = 0 Then d(e) = k Else: d(e) = d(e) & s & k
Next e
For i = 1 To lr
    da = s & d(a(i, 1)) & s
    da = Replace(da, (s & i & s), s)
    If Len(da) > 1 Then da = Mid(da, 2, Len(da) - 2) Else da = vbNullString
    da = Replace(da, s, ", ")
    u(i, 1) = da
Next i
[f1].Resize(lr) = u
End Sub

 

[TLA]

Mirabeau I have no idea what that macro is doing, but when I get the sheet together this Saturday I will give that whirl and see if it does what I need. I need to spend some time figure out what that is doing it's very concise for what I'm looking for if it works it would be great.

 

[mirabeau]

The macro is supposed to do just what you requested, at least as far as I can tell what you wanted and according to my testing on sample data.

Anyway, Good Luck!

 

[TLA]

I assume it is... i just meant I can usually decipher someone elses code and see what it would do but in this case I'm not understanding how it would find the duplicates. No big deal I'll play around with it.