removing duplicate entries

Hello, i'm trying to remove recurring duplicates from a list of 65,000+ its too long to do it manual, the macros i could find removed them but recompiled the data to a new list. i wanted to know if there was a way to get the data to stay put in its current cell but remove/delete every subsequent repitition. the data is a huge movie list for pricing and weights. it would look like this in column A data in cells would be weights 0.18, 0.18, 0.18, 0.19, 0.19, 0.20, 0.20, 0.20, etc and for prices in column B $5.50, 5.50, 5.75, 5.75, 5.75, 6.00, 6.00, 6.00, etc.. any help would be appreciated.

Hi there,

Before I reply, does a "duplicate" occur when EVERY cell in a given row is the same as the cell above it, or are you interested in INDIVIDUAL cells? In the first case we're talking about clearing entire rows, in the second case we're talking about clearing individual cells.

I'll get back to you when you let me know.

Regards,

Greg M

Hi,
posting a sample of your data might be of help

Cheers

Aloha! Any help is very much appreciated, one of my co-workers pulled out a doorstop for Visual Basic 6 Book (From the Ground Up-Cornell-McGrawHill) what im chasing is the ability to write VB that will see a duplicate price and take that out to make it a blank cell, showing the next number in sequence when it changes... sample data as follows

sellAmt upc
0.8 798622307323
0.8 798622309822
0.8 798622312921
0.8 798622303325
1.78 11301625236
1.78 11301621337
1.78 11301620231
1.78 11301626035
1.85 625282904398
1.85 625282904091
1.85 625282802199
1.85 625282800690
1.85 625282803691

what i'm looking to get when it completes like this sample

sellAmt upc
0.8 798622307323
798622309822
798622312921
798622303325
1.78 11301625236
11301621337
11301620231
11301626035
1.85 625282904398
625282904091
625282802199
625282800690
625282803691

the problem is its 65,000+ entrys and takes about 3+ hours to manual each column, ouch. Again, thanks a ton for any comments & help. CHeeers & heres to happier fields of amber waves of grain

well more like this finished
sellAmt upc
0.8 798622307323
___798622309822
___798622312921
___798622303325
1.78 11301625236
___11301621337
___11301620231
___11301626035
1.85 625282904398
___625282904091
___625282802199
___625282800690
___625282803691

Hi again,

The following code should do the trick for you - just copy it into a VBA module in your workbook:

Please Login or Register  to view this content.

Note that the values in the following statements:

Please Login or Register  to view this content.

should be changed to suit your worksheet. "strDataSheet" is the name of the worksheet from which you want to remove duplicate prices, "strPriceColumn" is the letter of the column which contains the prices, and "intFirstPriceRow" is the number of the row which contains the first price - the routine will check from this row down to the end of the worksheet.

Hope this helps - please let me know how you get on.

Regards,

Greg M

A Gentleman & a Scholar, it works perfectly, now how do i get it to do more than 1 column, can i use a;b or a|b doh...Cheers & thanks a ton!

i have the same movie list with the prices, and beside i want to be able to take out the duplicates on the weight side also, but it repeats in number sequence over & over. it might look like
WEIGHT
0.13
0.14
0.15
0.16
0.16
0.17
0.17
0.17
0.18
0.19
0.19
0.19
0.2
0.2
0.2
0.21
0.21
0.22
0.22
0.22
0.22
0.22
0.22
0.23
0.23
0.23
0.23
0.23
0.23
0.13
0.14
0.15
0.16
0.16
0.17
0.17
0.17
0.18
0.19
0.19
0.19
0.2
0.2
0.2
0.21
0.21
0.22
0.22
0.22
0.22
0.22
0.22
0.23
0.23
0.23
0.23
0.23
0.23

and after the first series is sorted and about 500 or so rows are done i have to re insert the module that will only do another 500 or so rows... whats that about? thanks for all your help!!!

Hi again,

Sorry for not getting back to you sooner, but I've been away for the last week.

Many thanks for the feedback - glad to hear that what we've covered so far seems to be doing what you want.

As far as the next stage is concerned I'm having difficulty in understanding exactly what you need. Can you give me some further explanation? Perhaps posting a sample of your data might help.

Best regards,

Greg M

no worries on this one, ended up not needing the other part... but what it is anyway... the titles are sorted by price, so on each one there is a weight, but its also sorted by weight 2nd, so the weight will go from 0.1 to 2 or whatever the highest # is, then repeat back at 0.1, so i guess it would need to be a loop or repeat once the macro had executed once and stopped. cheers!

Ok on that - thanks for the information.

Glad to have helped - if you need anything else, you know where to find me.

All the best,

Greg M