Find Unique Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I am trying to do I think is simple and what I have works but takes along time - any advice on a faster way would be grea

I have a bunch of data and I it is all dated - I set up in the last column and get a value that is "mmyyyy", but each file may have more than one moths worth of data so I copy and sort it and use the code below to delete the duplicates so that in the end I get a list of all the unique "mmyyyy" values there are. Then I take that and sort and send out to new files the origional data based on date

Sub GetFields112(
Application.Calculation = xlCalculationManua
Dim lastrow As Long, r As Lon
lastrow = ActiveSheet.UsedRange.Rows.Coun
For r = lastrow To 1 Step -
If Cells(r, 17).Value = Cells(r + 1, 17) Then Rows(r).Delet
Next
Application.Calculation = xlCalculationAutomati
End Su

What I need is a faster way to do this since a file may have many thousands of entries, is there a way to basically say, look in column "R" find all the unique values and then but them in column "S" starting in row one and listing them down

Any help would be great, what I have works but takes about 10 minutes to run, and I am looking for a shorter way to do it

Thanks

Jim
 
James

have a look at the Help for Advanced Filter.

You'll need Data | Filter | Advanced Filter... | Copy to another range |
Unique records only

In the dialogue box:
Copy to another location
List range: $R$1:$R$13
Criteria range: blank
Copy to: $S$1
Unique records only: tick

Should be VERY quick. No code required but you could record it if you want
a macro.

Regards

Trevor


James Stephens said:
What I am trying to do I think is simple and what I have works but takes
Click to expand...
along time - any advice on a faster way would be great
I have a bunch of data and I it is all dated - I set up in the last column
Click to expand...
and get a value that is "mmyyyy", but each file may have more than one moths
worth of data so I copy and sort it and use the code below to delete the
duplicates so that in the end I get a list of all the unique "mmyyyy" values
there are. Then I take that and sort and send out to new files the
origional data based on date.
Sub GetFields112()
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If Cells(r, 17).Value = Cells(r + 1, 17) Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
End Sub

What I need is a faster way to do this since a file may have many
Click to expand...
thousands of entries, is there a way to basically say, look in column "R"
find all the unique values and then but them in column "S" starting in row
one and listing them down.
Any help would be great, what I have works but takes about 10 minutes to
Click to expand...
run, and I am looking for a shorter way to do it.
 
Ji

If you definitely want a code option, then try this. On my machine, it took about 2 seconds to get a list of 3 unique values from 55,000 entries

Ton

Sub bbb(
Dim uniq As New Collectio
For Each ce In Range("r1", Range("r65536").End(xlUp)
On Error Resume Nex
uniq.Add Item:=ce.Value, key:=CStr(ce.Value
Next c
Range("s1").Selec
For Each ce In uni
ActiveCell.Value = c
ActiveCell.Offset(1, 0).Selec
Next c
End Su


----- James Stephens wrote: ----

What I am trying to do I think is simple and what I have works but takes along time - any advice on a faster way would be grea

I have a bunch of data and I it is all dated - I set up in the last column and get a value that is "mmyyyy", but each file may have more than one moths worth of data so I copy and sort it and use the code below to delete the duplicates so that in the end I get a list of all the unique "mmyyyy" values there are. Then I take that and sort and send out to new files the origional data based on date

Sub GetFields112(
Application.Calculation = xlCalculationManua
Dim lastrow As Long, r As Lon
lastrow = ActiveSheet.UsedRange.Rows.Coun
For r = lastrow To 1 Step -
If Cells(r, 17).Value = Cells(r + 1, 17) Then Rows(r).Delet
Next
Application.Calculation = xlCalculationAutomati
End Su

What I need is a faster way to do this since a file may have many thousands of entries, is there a way to basically say, look in column "R" find all the unique values and then but them in column "S" starting in row one and listing them down

Any help would be great, what I have works but takes about 10 minutes to run, and I am looking for a shorter way to do it

Thanks

Jim
 
Sorry to take so long to thank you, just got back and got a chance to get to work on this. What you have here works great and is going to save me alot of time, thanks for all the help.

Jim
 
Back
Top