Note: This also applies to on-premise versions of SharePoint (probably — I didn’t test them all), although the PowerShell used to export list data would be different in that case.

Consider the following scenario:

• You have a SharePoint list that you’ve been using for some time and contains many records.
• You decide you want to make sure that users aren’t entering duplicates in a certain column.
• When trying to set the “Enforce Unique Values” setting to “Yes” on the column, the following error occurs:

“This field contains duplicate values. Remove all duplicate values and try the operation again”

Like the error says, there are duplicate values in that column. You need to find them and make them unique. If there are many (like hundreds or thousands) of items in that list, that can be difficult to do. One way or another, we need to get the list contents into Excel so we can more easily check for duplicates.

Option 1: Export to Excel:

Use the “Export to Excel” function available in the list toolbar:

Option 2: PowerShell

If for some reason, the “Export to Excel” function is not available, or is not working properly, then you can use PowerShell to export the column values to a CSV file. Using the sample below, you would just specify the URL to the site, the list name, and the column that you’re interested in. The script will export that column plus the ID column to a CSV file. Note: This particular sample uses the SharePoint PnP module, so you’ll need that.

#Title: ExportListData-PnP
#Description: Iterates through each item in a specified list and exports values for the specified columns
#Date: 4/29/2020
#Author: Joroar
#Disclaimer: This PowerShell script is provided "as-is" with no warranties expressed or implied. Use at your own risk.
#Dependencies: SharePoint Pnp module: https://www.powershellgallery.com/packages/SharePointPnPPowerShellOnline/
#Tested with PNP version https://github.com/pnp/PnP-PowerShell/releases/tag/3.20.2004.0
#Parameters: $SiteURL, $ListName, $CSVFile.

#Set parameter values
$SiteURL="https://contosoteam.sharepoint.com/sites/Team1"
$ListName="Dupe List"
$ColumnName="Title"
$CSVFile="C:\Temp\$ListName.csv"

#### --- Should Not need to change anything below this line --- ####
Install-Module SharePointPnPPowerShellOnline 
$Data = @() #Array to hold result
#Delete the Output report file if exists
if (Test-Path $CSVFile) { Remove-Item $CSVFile }
#Connect to SPO using web-style login
Connect-PnPOnline $SiteURL -UseWebLogin
#Get the list
$list = get-pnplist | ? {$_.title -eq $ListName}
#Iterate throgh each item
$items = Get-PnPListItem -List $list -Fields "ID",$ColumnName
foreach($item in $items)
{
write-host "Processing Item:" $item.id -f Yellow
    #Send Data to object array
    $Data += New-Object PSObject -Property @{
    'Item ID' = $item["ID"]
    $ColumnName =  $item[$ColumnName]
    }
}
#Export the data to CSV
$Data | Export-Csv $CSVFile -Append -NoTypeInformation
write-host -f Green "List Items Exported Successfully to:" $CSVFile

Analyze the output.

There are a number of different ways to find duplicate values in Excel. Just ask the Internet.

Not finding any duplicates?

There are a couple of “gotchas” I know about.

#1. Trailing Spaces

If you have two column values that are identical except one has a trailing space, the normal Excel methods for finding duplicate values will not find them.

For example:

“Test2”

“Test2 “

Note the trailing space in the second value above. The Excel methods will not mark those as duplicate. However, the process behind “Enforce Unique Values” does see them as duplicate and will fail.

I’m sure there are more sophisticated ways of finding this type of “duplicate”, but I just sorted the Excel spreadsheet alphabetically by the problem column and scrolled through it to find the issue.

In my testing, the problem was only with trailing spaces. I could not get it to fail with a leading space, or with extra spaces between words.

#2. Indexed Columns

Go to List Settings | Indexed Columns.

If there are any columns that have indexes, those must also have unique values. Either analyze those column values for duplicates as well, or just remove the indexes.

Note: By default, there are no column indexes on a SharePoint list, so if you see any, they were manually added. If you remove them, you should be able to re-add them later if needed.