Tech Support Guy banner
Status
Not open for further replies.
1 - 4 of 4 Posts

·
Registered
Joined
·
18 Posts
Discussion Starter · #1 ·
How do I get rid of duplicates in an Excell spreadsheet. Some of these spread sheets are 12,000 lines long and I hate scrolling through. Isn't there a command that will eliminate dupes?
 

·
Registered
Joined
·
11,755 Posts
Will you be deciding that a duplicate exists because one row is the same in (for instance) column A, or because all of the values in all of the columns match another record. In other words, when compared to

A......B......C

which of the following will you delete?

1) A......A......A
2) A......B......A
3) A......B......C
4) All of the above.

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

·
Registered
Joined
·
11,755 Posts
Nope. We can do it, forms.

Save the file to a new name first.

Now, let's suppose the data you're "matching" is in column A. Let's suppose column E is your first empty column to the left of A. Let's suppose you've got column headings in row 1 and your data starts in row 2.

Sort column A in ascending order (and allow all the rest of the data to go with it).

In cell E1, type "Dupes"

In cell E2, type =a2

In cell E3, type =if(a3=a2,"Y","")
Copy cell E3 down all 20,000 rows.

Now, select cell E1 and hold Shift-Ctrl and hit the down arrow key to select all the cells in column E for all 20,000 records.

Hit copy.

Hit Edit-paste special-Values.

Now, sort all the data by column E, but this time do it so the Ys all come to the top. Select all of the rows where column E contains the Ys. Delete the entire row(s).

Let me know if you have any problems or zip and email the file...

Good luck!

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 
1 - 4 of 4 Posts
Status
Not open for further replies.
Top