Duplicate rows

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Toddles18

Thread Starter
Joined
Jul 18, 2001
Messages
2,910
I have a spreadsheet with around 3000 rows. There are some duplicates. Is there an easy way to find the duplicates in excel and export them to another sheet for review, or even highlight or separate them in some manner?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
do you have access you could import to access and remove duplicates. I did this for large data sents - infact linked input and then export as spreadsheet.

is this the entire row or just 1 cell in each row which is duplicated

i do a sort and then OR((A1=A2),(A2=A3)) then you can see if A1=A2=A3 and see duplicates.

or

if the rows all the same - depending on number of characters
you could concatenate them into one cell and do the above.
A1&A2&A3
then use OR test after sort

i'm sure theres better ways with macros - just my twopence worth
 
Joined
Sep 4, 2003
Messages
4,916
How many columns are you using for each record? Be more specific on what comprises a duplicate record and we'll help. Does each duplicate record only use a single column or does a duplicate record mean that the entries in columns A, B, C, etc. are all the same for each record?




Rollin
 

Toddles18

Thread Starter
Joined
Jul 18, 2001
Messages
2,910
There are 8 columns. Column A is the key value that dictates whether it is a duplicate or not.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
can you zip up and attached - and then describe as "rollin' rog" suggests which columns = duplicates
 
Joined
Sep 4, 2003
Messages
4,916
Select the entire range of cells in Column A and click INSERT --> NAME --> DEFINE and give the range selection the name Range1

Next click FORMAT --> CONDITONAL FORMAT and change the Condition1 drop down selection to read "FORMULA IS"
(make sure your range of cells in column A is still selected)

Paste the formula below into the formula textbox then click the format button and select the highlight color for your duplicate records. Now click OK and all duplicate items in column A will be highlighted.

=IF(COUNTIF(Range1, A1)>1,TRUE,FALSE)

(Change A1 in the formula above to represent the first cell in Column A containing data)



Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top