Identifying repeat ID's
 

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

Identifying repeat ID's
 

Hi Brad,

Visit Chip Pearson's site:

http://www.cpearson.com/excel/duplic...gingDuplicates


---
Regards,
Norman



"Brad K." wrote in message
...
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

Identifying repeat ID's
 

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" wrote:

Hi Brad,

Visit Chip Pearson's site:

http://www.cpearson.com/excel/duplic...gingDuplicates


---
Regards,
Norman



"Brad K." wrote in message
...
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

Identifying repeat ID's
 

"Brad K." wrote in message
...
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.

---
Regards,
Norman