• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4141
  • Last Modified:

Removing duplicates in Sharepoint List

Im using WSS 3.0/Sharepoint and this nifty tool to import excel spreadsheet into a list: http://spreadsheet2splist.codeplex.com/

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?
2 Solutions
zephyr_hex (Megan)DeveloperCommented:
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.
I agree with the above recommendation. Export to Excel, clean up and push data back in. Copy/Paste also works.
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)
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now