Duplicate Check on column of data

TLA

Board Regular
Joined
Jul 15, 2003
Messages
127
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Last edited:
Upvote 0
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top