Duplicate entries

[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

 

[dk]

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

 

[Derek]

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

 

[Aladin Akyurek]

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.