SOLVED

Highlight or count duplicates in a single Sharepoint List column

Occasional Contributor

I have a Yes/No column titled "isDuplicate" which I would like to populate based on duplicates located in another column titled SerialNumber. I tried to do this with a calculated column and if statement, but doesn't seem to work. Is there any way to count or highlight duplicates in a single column in Sharepoint List?

4 Replies
You can set a view up that is grouped by Serial number, which will give you a count, but you'll need to scroll through and look for anything greater than 1. You should be able to use Power Automate. Check this out: https://www.c-sharpcorner.com/article/get-distinct-count-from-sharepoint-list-item-using-power-autom...

@PamDeGraffenreid Thank you for your reply. It doesn't seem to address the problem I'm facing though. The union expression in the link eliminates duplicates. I'm trying to highlight and update duplicate rows.

 

So basically...highlight the duplicates in the serial number column and/or update the IsDuplicate column for the rows where there are duplicate serial numbers. 

 

This is the current view:

 

RADical6142_0-1657302438148.png

What I'm trying to accomplish:

 

RADical6142_1-1657302645907.png

 

I don't think there is a way to accomplish this with calculated or lookup columns. It looks like the alternatives are JSON formatting, Power Apps or a Flow. I'm not verse on the former two, so I'm thinking a Flow would be easier. Is there an expression that you know of that will do the opposite of "union" where it only return duplicates? I tried Intersection, but that doesn't work either. 

 

Thanks! 

best response confirmed by RADical6142 (Occasional Contributor)
Solution

@RADical6142   Here you go...

 

Create a variable and connect to your list:

PamDeGraffenreid_1-1657477468465.png

Now, for each item above - check the list to see if that serial number exists anywhere else in that list.  If it does, increment a variable.  When it's done, update the IsDuplicate field to yes or no.

In the apply to each

  • set the count back to 0 
  • Get the list items for searching

PamDeGraffenreid_3-1657477717318.png

Now add another Apply to each and a condition. The condition: if the Serial Number (in the first Get Items) equals the Serial Number (in the second Get Items), increment the variable.  It will always find a match once.  

PamDeGraffenreid_8-1657480328522.png

After that apply to each, add another condition that checks to see if the count is greater than 1, set IsDuplicate to Yes, if not, set IsDuplicate to No. 

 

PamDeGraffenreid_12-1657480501153.png

 

That's it, your list should be updated.  Here's a big picture view, beginning to end.

PamDeGraffenreid_14-1657480642875.png

 

Some thoughts...  

  • I set this to run as recurrence since it will probably take a long time to run depending on how many items are in your list.  You might want to filter that first Get Items to only check items that have been updated since the last time it ran. 
  • If this is a one-time cleanup, maybe set the Serial Number field in SharePoint to Enforce Unique Values

PamDeGraffenreid_16-1657480848080.png

Here is the json for the Title column.

{
"elmType": "div",
"debugMode": true,
"txtContent": "@currentField",
"style": {
"background-color": "=if([$IsDuplicate] == 'Yes', '#FFFFDD', '')"
}
}

 

@PamDeGraffenreid Thank you! This is exactly what I was looking for. I tested it and it works great.