Removing duplicates in Sharepoint List

Posted on 2009-12-29
Last Modified: 2012-05-08
Im using WSS 3.0/Sharepoint and this nifty tool to import excel spreadsheet into a list:

while it does import the spreadsheet succesfully, it will not remove the duplicates or overwrite existing rows if the same spreadsheet was to be imported (with changes made to some rows) again. The duplicate rows will appear .

Is there a ool or a way to remove duplicates in a specified sharepoint list?
Question by:Anti-Mhz
    LVL 42

    Expert Comment

    there is no cool way.

    export the list to Excel.

    edit your data.

    delete the original list in sharepoint & re-create.

    then re-create the list by importing your spreadsheet.
    LVL 16

    Expert Comment

    I agree with the above recommendation. Export to Excel, clean up and push data back in. Copy/Paste also works.
    LVL 19

    Accepted Solution

    There used to be two-way editing with excel. Changes in excel would be pushed automatically to the list and back.
    Right now if you want to achieve two-way binding, the only way is to use acces. Making changes in acces will be done on SharePoint as well without duplicates (also no need to import it again)

    Assisted Solution

    If you have a really large list and it's worth the effort, you could link the list to Access, and do an Advanced Filter to find the duplicates in the list. Pick a field to do a filter on (field1), then in the criteria put in:

    IN (SELECT [field1] FROM [table] As Tmp GROUP BY [field1], [field2], [field3], etc... HAVING Count(*)>1)

    This will display all the rows that have duplicates in the field(s) after the GROUP BY (if the only difference between two rows is in fields not listed after the GROUP BY, it will not be displayed in the filtered list).

    Unfortunately I haven't been able to get Access to return only 1 row of each duplicated set to delete them all at once. My attempts using DISTINCT and nested SELECT/GROUP BY statements have only pegged Access and forced me to start over. :) Depending on your situation, you should be able to sort/filter further to get the 2nd copies together so you can delete them at once.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
    If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc. The most natu…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now