Identify duplicates in excel file & assign those accounts to same user

Help Activities
,
1

Hi, I have a scenario where in excel file I have to find the duplicates whether it can be repeated twice or more I have to identify those accounts & assign to the same user from a list of different users available, if it is matching with 5 column values then only it is considered as duplicate, we have unique account number in another column for those identified duplicates. Kindly provide an solution for the above scenario

2

Hi @Somanath_Somu

I think it would be more helpful to understand your requirement if you also share the screenshot from excel.

3

Kindly find the attached screenshot fyr.

IMG_1071
IMG_10711920×1387 446 KB

4

check below thread for how to check for duplicate values in data table.

also can you please explain what do you mean by " assign to the same user from a list of different users available" ?

5

I have a separate mapping sheet with users list, while assigning I have to take one of the user from that list where we have some condition that max allocation per user is 175 accounts

6

  1. Use Use Excel File (Modern) or Excel Application Scope (Classic).

  2. Use Read Range → Output: dtData (DataTable).

  3. Use Add Data Column activity:

  • Column Name: “AssignedUser”
  • DataType: String
  • Add to dtData

Use Assign activity:

  • usersList = New List(Of String) From {“User1”,“User2”,“User3”}

Use For Each Row in DataTable (dtData):

  • Check if the current row is already assigned (skip if row(“AssignedUser”).ToString <> “”).
    Use Filter Data Table or Select Data Table to find all duplicates:
  • Filter condition:

Col1 = currentRow(“Col1”).ToString
AND Col2 = currentRow(“Col2”).ToString
AND Col3 = currentRow(“Col3”).ToString
AND Col4 = currentRow(“Col4”).ToString
AND Col5 = currentRow(“Col5”).ToString

  • Output: dtDuplicates

Check If dtDuplicates.Rows.Count > 1 → Duplicate group found.

Use Assign

  • assignedUser = usersList(userIndex Mod usersList.Count)
  • serIndex = userIndex + 1
    Use For Each Row in DataTable (dtDuplicates):
  • Assign: row(“AssignedUser”) = assignedUser

Use Write Range to update the Excel with the new column AssignedUser.

Try this if usefull mark it as Solution
Happy Automation