Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assigning id's | Excel Discussion (Misc queries) | |||
List of Id's in one Colum list of names in another. More Id's on s | Excel Discussion (Misc queries) | |||
Unique ID's | New Users to Excel | |||
User ID's | Excel Worksheet Functions | |||
Face ID's | Excel Discussion (Misc queries) |