Identifying repeat ID's

[Guest]

On a monthly basis I have a sheet that is filled with lab data (up to 2000
rows and 15 columns). I need to identify samples that come from repeat
sources (i.e. Row 1 "ID"). When identified, I want to copy all of the data
for each of the repeat ID's to a new sheet for further analysis.
Anyone have some good ideas for an efficient method to accomplish this?
Thanks in advance for any assistance provided.
Brad

 

[Norman Jones]

Hi Brad,

Visit Chip Pearson's site:

http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[Guest]

Hi Norman,
The linked site has great information. However, the example given there
shows a formula entered onto the sheet. I am looking for a VBA solution to
this. Any suggestions.
Thanks,
Brad

 

[Norman Jones]

Hi Norman,
The linked site has great information. However, the example given there
shows a formula entered onto the sheet. I am looking for a VBA solution
to
this. Any suggestions.

Hi Brad,

i have assumed that, for duplicated values, you want to ignore each first
value and extract subsequent instances. I further assume that duplicates are
defined by the column A value.

If this accords with your intentions, initially on a *copy* workbook, try
something like:

Sub ExtractDupes()
Dim SrceSh As Worksheet
Dim DestSh As Worksheet
Dim sStr As String
Dim CalcMode As Long

sStr = "FurtherAnalysis" & Format(Now, "yyyymmdd (mm-ss)")
Set SrceSh = ActiveWorkbook.Sheets("Sheet2") '<<===CHANGE

With Application
CalcMode = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End With

Set DestSh = Worksheets.Add
DestSh.Name = sStr
SrceSh.UsedRange.Copy Destination:=DestSh.Range("A1")
Set rngFilt = DestSh.UsedRange.Columns(1)

rngFilt.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
On Error Resume Next
rngFilt.SpecialCells(xlVisible).EntireRow.Delete
On Error GoTo XIT
DestSh.ShowAllData

XIT:
With Application
.ScreenUpdating = True
Calculation = CalcMode
End With

End Sub


Near the top of the above procedure, I have:

Set SrceSh = ActiveWorkbook.Sheets("Sheet2")

Amend this to accord with your workbook/worksheet name.