Lookup Duplicates in a List

[sgeorge1102]

Hello,

I have a list that gets updated each month and I am trying to create a flow to identify the duplicates so then I can delete them out. I use a concatenate column after I try and "Get Items 1" and then in an "Apply to each" use the GI1 to search against the GI2 in a condition if it is true, it updates the line to "Yes" duplicate. The flow never failed but it ran for 6+ days if I didn't stop it. The list usually has about 4,000 items in it and 1,000 of them are duplicates.  Here are some screen shots of my flow. Happy to take any suggestions. 

[Nived_Nambiar]

Hi @sgeorge1102 

In other words , you are asking how to make this flow shorter in execution ?

[sgeorge1102]

Yes, I can do this process manually in 3-4 days, so I would need the flow to the work about that same time or faster. The workflow does work and identifies the NON duplicates but it seems like the flow was having a tough time working through the ones that were duplicates and there were lanes left over that had no value and most of them were duplicates. If there is a better way than what I am doing it now or better filtering or something happy to hear options.

[Nived_Nambiar]

Hi @sgeorge1102 

Could you please explain me the logic behind this section ?

I think we can make your flow efficient 🙂

[sgeorge1102]

Hello Nived, this section is to set a value to “Yes” if the condition is true as a duplicate and then “No” if it is not. Once the flow is done I have no the flow to delete out the items that are marked “Yes”. Make sense?

[Nived_Nambiar]

But the question is regarding count parameter ?

Or may be you can give me a complete idea of flow functionality so that i can help in making flow better 

[sgeorge1102]

The count variable is there because we use the same report to search for the duplicates. So the first time it will choose 1 but the second go around if it finds a duplicate in the second report it would be greater than two so then those should be marked as duplicates.

[Nived_Nambiar]

Hi @sgeorge1102 

But the looping may have many duplicates apart from duplicates you are searching for, in such cases, current logic would skip others duplicates and focussing only on first record which it has started right ?

Also show me the Get items 2 action ?

[sgeorge1102]

Hi Nived, I am using one list that has duplicates. I need to identify the duplicates efficiently. Do you have any suggestions based on what I have done to help me achieve a more efficient? Thanks, Sarah

[sgeorge1102]

Get Items 2 action.

[Nived_Nambiar]

Hi @sgeorge1102 

First use the get items to retrieve all items from SharePoint list 

Now make the concatenated column from that get items as unique array. 

Now loop through each element in that concatenated array using apply to each loop and use filter array to filter the value output from get items with concatenated column value, if the output of filter array has count >1 , now update the last row of items obtained from filter array output with column value set as Yes since we need to mark last occuring column as duplicates if I am right.

This way you can make your code a bit faster ☺️