VBA to Move Duplicate Numbers to new sheet

Really need someones help here

I annalyse very large sets of numbers with anything upto 1'000'000 cells in any one sheet and i would like a code to allow me to pull out duplicate numbers from column A (sheet 1) and put them into say Column A (sheet 2) therefore leaving only the first occurence of numbers on sheet 1.

Many thanks in advance for this

Don't know how quick it would be but you could use something like:

Please Login or Register  to view this content.

What sort of numbers?
Are they integers or no?
Does the order of them matter? That is, to you want them to retain the same order both before and after the duplicate removal?

Just standard 10 digit numbers, order doesnt matter too much to be honest just need a quick way to remove the duplicates to another sheet

Originally Posted by yudlugar

Don't know how quick it would be but you could use something like:

Please Login or Register  to view this content.

this works great thaks however when i use it on say 300'000 numbers it causes excel to crash ??????

perhaps this?

Please Login or Register  to view this content.

Originally Posted by kalak

perhaps this?

Please Login or Register  to view this content.

This comes back with the following error highlighted in yellow ?

Re post 5:

I guess you can't use countif on that many numbers...

If you sort the data first so the duplicates are together it would be a lot easier... then you can just use:

Please Login or Register  to view this content.

Thanks for the reply, however I wouldnt be able to sort after highlighting dupes on that size file either otherwise I would just copy and paste to sheet 2.

Try this one.

Please Login or Register  to view this content.

Originally Posted by AB33

Try this one.

Please Login or Register  to view this content.

Thanks for the reply but again when trying to run it on a large file it kills Excel !

My code output is in new sheet. You can not get faster(Except may be filter) than a combination of an array and dictionary.

I can bet you the code will run in less than 10 seconds with 1m rows. So, I do not understand when you say it kills excel. You need to explain what is going.

Originally Posted by AB33

My code output is in new sheet. You can not get faster(Except may be filter) than a combination of an array and dictionary.

I can bet you the code will run in less than 10 seconds with 1m rows. So, I do not understand when you say it kills excel. You need to explain what is going.

Always getting (Not Responding) even for a file of 40000 it hangs for about a minute before working but for the larger files it just doesnt respond. Ive removed all formatting aswell incase that was slowing the process down.

Stats are
AMD Athlon II P360 Dual Core 2.30ghz
Running 6gb ram

Is it my system thats the problem here ?

Jamie,
Please do not reply with quote. Just a reply will do.
A while back, I had encountered similar issue. The person on the other end kept telling me when he run the code, it froze his PC. I then asked him to send me the data. My PC had crashed 3 times and were lucky not to lose it. He had downloaded data from application and used excel to do simple formula and formatting. I suspect you have got the same issue. I would do this:
Copy the data manually(Go to the top left hand of excel, highlight all copy and paste VALUES ONLY in to new sheet(Only one sheet) and save it. Then run the code and you will see a massive difference in speed.

No Difference, Still hanging after 5 minutes.

Originally Posted by kalak

Jamie,

re the code I posted above, that will error on your indicated line if you don't start from, or if your data are not on, a sheet with name Sheet1.

Try moving the line Sheets("sheet1").Activate two lines up, between lines beginning Set d = and rws =

I can't get that one not to work, but if it doesn't work for you and assuming we can find the right sheet to start on, I can give you another fast code to try that should.

Does your active sheet when you start running the code have a name?

Hi
Thanks for helping, im on a fresh "sheet1" and ive tried moving the line up but i still come back with the same error ?

Just tested with 1000000 random numbers, took about 27 sec.

Please Login or Register  to view this content.

Jamie,
Look at the link and read post 16 (Second) page until the end.

http://www.excelforum.com/excel-prog...ata-set-2.html

Originally Posted by jindon

Just tested with 1000000 random numbers, took about 27 sec.

Please Login or Register  to view this content.

Thanks fot the help, Its still hanging after 5 mins but hopefully it will work eventually. Im starting to think its my system thats the problem. Im also wondering if the office suite im using is genuine or not . Would that effect its performance if it turns out to be a copy ?

Thanks for the help everyone.

Can any of these codes be altered to list the unique values in one sheet and the duplicate values in another and is it possible to add extra colums if i needed to have say a referance number before each number. the amount of columns before and after the numbers would differ depending on the data type though

Originally Posted by jamie wilson82

Thanks for the help everyone.

Can any of these codes be altered to list the unique values in one sheet and the duplicate values in another and is it possible to add extra colums if i needed to have say a referance number before each number. the amount of columns before and after the numbers would differ depending on the data type though

jamie,

do we take it that you now have some/all of the codes working?

for your red comment above, that's what you asked for in your opening post and that's just what my code above (modified version) does.

Perhaps you could try it on some very simple data and check.

It's possible that a non-genuine copy of Office could result in some features not working or not working properly.

In the event that your Office can't use the dictionary object I can give you another code that doesn't use it at all. With 1million randomly generated numbers between 1 and 1million it took me about 7.5secs to keep only the uniques on sheet1 and put the duplicates on sheet2.

If your numbers are integers that can be done quite a bit faster ...

For the second part of your question above, could we first be sure that you have the first part working?

Originally Posted by kalak

jamie,

do we take it that you now have some/all of the codes working?

for your red comment above, that's what you asked for in your opening post and that's just what my code above (modified version) does.

Perhaps you could try it on some very simple data and check.

It's possible that a non-genuine copy of Office could result in some features not working or not working properly.

In the event that your Office can't use the dictionary object I can give you another code that doesn't use it at all. With 1million randomly generated numbers between 1 and 1million it took me about 7.5secs to keep only the uniques on sheet1 and put the duplicates on sheet2.

If your numbers are integers that can be done quite a bit faster ...

For the second part of your question above, could we first be sure that you have the first part working?

Yes the codes are all working now all be it very slow (10-20mins to separate a big file ) but its better than nothing. for some reason yours wasnt but now is so thanks thats exactly what I needed .

10-20 minutes seems a very long time for this sort of problem.

here's another code which I'd be curious to know if it runs any faster - or even runs at all.

it does give output in sorted order, but I think you said earlier that's OK

Please Login or Register  to view this content.

Originally Posted by kalak

10-20 minutes seems a very long time for this sort of problem.

here's another code which I'd be curious to know if it runs any faster - or even runs at all.

it does give output in sorted order, but I think you said earlier that's OK

Please Login or Register  to view this content.

Wow That ran in seconds, we may nearly be there !

Originally Posted by jamie wilson82

Wow That ran in seconds, we may nearly be there !

Can this be adjusted to have the numbers in column O and all details in columns A all the way upto and including S be Moved as well ?

Originally Posted by jamie wilson82

Can this be adjusted to have the numbers in column O and all details in columns A all the way upto and including S be Moved as well ?

Jamie,

It would be too much guesswork for me to have a try at that on the info you provide. Code writing does take some time and we're all (or at least I am) a bit short of that.

Could you post a file showing some brief sample input data and what you want the final result to be, and where?

Example.xlsx

See attached , Basically what i'd like from this spread sheet is to paste data into the sheet 'raw data' and then to run a code to remove duplicate rows based on the telephone number in column O and then also list all the unique values together on a 3rd sheet both times moving all details either side of that particular number .

An added bonus for me would be if when the code ran it would also remove spaces fron the telephone number and 44 before a number therefore allowing it to recognize two identicle numbers even if they were entered differently.

Hope this makes sense and thanks in advance

Sub anor()

Dim c&, n&, i&, k&, a, m()
n = Cells(Rows.Count, "a").End(3).Row
ReDim m(1 To n, 1 To 1)
c = n + 1
With Cells(1, "a").Resize(c)
.Sort .Cells(1), Header:=xlNo
a = .Value
For i = c To 1 Step -1
If a(i, 1) <> a(c, 1) Then m(i, 1) = 1: c = i
Next i
.Offset(, 1).Insert
.Offset(, 1).Resize(n) = m
.Resize(, 2).Sort .Offset(, 1), Header:=xlNo
k = Application.Count(.Offset(, 1))
If k > 0 Then
Range(Cells(k + 1, 1), Cells(n, 1)).Cut Sheets("sheet2").Cells(1)
.Offset(, 1).Delete
End If
End With

End Sub

Spoke to soon !! used this code a few times but am starting to get this error for some reason ? seems tempramental