Duplicate entries

G

Guest

Guest
I have a spreadsheet with approximately 55000 lines in it, and 12 columns. I would like to know if anybody knows how to (in one column) pick up duplicate entries so that I can then delete the duplicated entries, at the moment I have to go through each line indivdually and look and check to see if it is duplicated and then sort and delete, I would really like to pick up the duplicate entries, and then be able to delete them all in one go if possible.

Thank You John Harcourt-Rigg
Brisbane Australia

rosella@bigpond.net.au
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Depends how you define duplicates...is the whole row the same or just the cells in a particular column?

Anyway, the quickest non-VBA way I can think of doing this is to use Advanced Filter. Select your range, choose Data, Advanced Filter and check Unique Records only. When this is done select the whole of your range, hit F5, choose Special and then Visible Cells Only. Copy and then paste to a new sheet - et voila!

HTH,
D
 
Upvote 0
Hi
Here's another way

If your data is in Column A, starting at A2
Put this formula in B2 and scroll down.
This will return "duplicate" against all duplicate entries. Filter Column B to show only "duplicate" and delete the rows.

=IF(ISNA(VLOOKUP(A2,$A$1:A1,1,FALSE))=TRUE,"","duplicate")

Good Luck
Derek
 
Upvote 0
On 2002-02-26 04:45, Anonymous wrote:
I have a spreadsheet with approximately 55000 lines in it, and 12 columns. I would like to know if anybody knows how to (in one column) pick up duplicate entries so that I can then delete the duplicated entries, at the moment I have to go through each line indivdually and look and check to see if it is duplicated and then sort and delete, I would really like to pick up the duplicate entries, and then be able to delete them all in one go if possible.

Thank You John Harcourt-Rigg
Brisbane Australia

rosella@bigpond.net.au

As Derek also noted, it's not clear what you take to be a duplicate. What follows takes up two obvious possibilities.

[1]

Lets say that A4:C8 houses the following sample data:

{"field1","field2","field3";
"a1",2,3;
"a2",6,8;
"a1",7,6;
"a1",2,3}

If you want to keep one record of all the records that are identical like row 5 and row 8:

Activate A5;
Activate Data|Advanced Filter;
Set 'List range' to (if not already set) $A$4:$C$8;
Check 'Copy to another location';
Set 'Copy to' e.g., $H$4;
Check 'Unique records only';
Click OK.

[2]

If you want to keep just one record that has A-value (that is, a single record headed with "a1" and "a2", regarding the sample above:

In D4 enter: field4 [ just a label ]

In D5 enter: =ISNUMBER(MATCH(A5,$A$4:A4,0))+0 [ copy down this till last row of data ]

In A1 enter: field5 [ just a label ]

In A2 enter: =D5=0

Activate A5;
Activate Data|Filter|Advanced Filter;
Set 'List range' to (if not already set) $A$4:$D$8;
Check 'Copy to another location';
Set 'Copy to' e.g., $L$4;
Click OK.

You can copy the range where the data are filtered to and paste the copy in a different worksheet. Delete the old worksheet if desired.

Note. Make a copy of your WB before trying out these recipes.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top