double records

V

viktor

Hi,

How can i get the double records from one column with multiple
records(thousends)?

thanks!
 
B

Bruno Campanini

viktor said:
Hi,

How can i get the double records from one column with multiple
records(thousends)?
Click to expand...

The following writes from TargetSource down, all duplicates found
in SourceRange down.
Just set your Source and Target ranges in Definitions.
=========================================
Sub WriteDuplicates()
Dim SourceRange As Range, TargetRange As Range
Dim i, j As Long, TestColl As New Collection

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

' Definitions
' ---------------------------------
Set SourceRange = [Sheet10!AA189]
Set TargetRange = [Sheet10!AB189]
' ---------------------------------
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize _
(SourceRange.End(xlDown).Row - SourceRange.Row + 1)
End If
For Each i In SourceRange
On Error GoTo WriteDuplicate
TestColl.Add i, CStr(i)
Continue:
Next

Exit_Sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub

WriteDuplicate:
j = j + 1
TargetRange(j, 1) = i
Resume Continue

End Sub
==========================
Ciao
Bruno
 
R

Roland

Viktor,

Here's a technique that I use.

Assume your list is in column A starting with a header in cell A1, data
in cel A2, and as you said, runs over a thousand rows.

In cell B11 type this formula

=VLOOKUP(A11,A$1:A10,1,FALSE)

Copy the formula up and down column B to match your data.

A #N/A will appear next to the first instance of each data value. A
repeat of the data value will appear next to the 2nd and subsequent
occurances.
 
V

viktor

thank's, Roland! the formula was very usefull!
For Niek: when i will have some free time, i will study the forward site
that you give me! The thank's are for you, alsow!
 
Top