Jul 07 2022 04:26 PM
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?
Jul 07 2022 08:21 PM
Jul 08 2022 10:54 AM
@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:
What I'm trying to accomplish:
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!
Jul 10 2022 12:27 PM - edited Jul 10 2022 12:50 PM
Solution@RADical6142 Here you go...
Create a variable and connect to your list:
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
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.
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.
That's it, your list should be updated. Here's a big picture view, beginning to end.
Some thoughts...
Here is the json for the Title column.
Jul 10 2022 01:26 PM
@PamDeGraffenreid Thank you! This is exactly what I was looking for. I tested it and it works great.
Feb 22 2023 03:21 PM
@PamDeGraffenreid - Thank you for this suggestion. I replicated the steps in the flow since I have a similar use case.
The issue that I am experiencing is that the each line gets updated not just the line that have duplicates. From the best that I can tell, it looks at the line count of the list, and then simply sets the IsDuplicate field to 'yes.'
I would be curious if you have any thoughts and I am happy to provide screenshots of my flow as well.
Mar 09 2023 04:54 PM - edited Mar 09 2023 04:58 PM
I wanted to follow-up to my earlier response as I have identified a slightly more streamlined solution that circumvents the issue with the count. Basically, it compares both 'Get Items' like PamDeGraffenreid's solution but then trades the count for a condition that compares column values before comparing the ID number of the list item(s).
Namely, If the IDs don't match, it will update a field in the list, whose value I then leverage in the standard MS List conditional formatting (e.g. If column X = Yes then column Y will be formatted 'yellow') to highlight duplicate items.
I appreciate PamDeGraffenreid's approach as it was very insightful. Thanks!
May 19 2023 10:29 AM
@PamDeGraffenreid I have a quick question. I was looking at your soultion and was wondering if you could help me. I have a bunch of duplicates in my sharepoint list and instead of using the delete function I want to use the update function to replace the oldest record and replace by the newest record could you help me at all!
Aug 01 2023 07:39 PM
Hi @Mike_Marmon
I'm trying to implement what Pam has laid out, but for Addresses ina sharepoint list.
The flow runs fine, but never increases the variable more than 1, which marks all my items as 'IsDuplicate = No' even if there are duplicate addresses.
I even tried to create a custom view on the list, so the Get Items action returns only the address column.
any idea on how to properly implement this?
or any further info on your more streamlined approach?
cheers
Jul 10 2022 12:27 PM - edited Jul 10 2022 12:50 PM
Solution@RADical6142 Here you go...
Create a variable and connect to your list:
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
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.
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.
That's it, your list should be updated. Here's a big picture view, beginning to end.
Some thoughts...
Here is the json for the Title column.