How to check Duplicate records with specific condition/filter Excel

Hello Everyone

Good day!

I badly needed your help, I’ve been stuck with this problem for a couple of days… My problem is I need to check the duplicate records (group by Name, Age, Surname, and Location). I put the below logic as per requirements. Please need your help everyone!

//Single record
Logic 1
-Comment ‘ok’ or don’t do anything if single record

//Duplicate records
Logic 2
-If duplicates, check if the item only has ‘111’, if yes, comment ‘Exception Same Item Code’
else, put comment ‘Exception Multiple Item’

Logic 3
-If duplicates, check if it has item ‘111’ AND Location = ‘SYD’, if yes, comment ‘Exception Location SYD’

Name Age Surname Location Item Comment
James 12 Callos MELB 111 Exception Multiple Item
James 12 Callos MELB 123 Exception Multiple Item
Joy 13 Joy SA 111 Exception Same Item Code
Joy 13 Joy SA 111 Exception Same Item Code
Escanor 14 Merlin CAN 123 ok
Rom 13 Kapoor SYD 111 Exception Location SYD
Rom 13 Kapoor SYD 111 Exception Location SYD
Deku 13 Toshiro SA 124 ok
Arjun 13 Khan MELB 111 ok

I’ll start off by assuming you have the first 5 columns of data in a datatable, with the 6th column empty.
Run a nested “For Each Row” loop. Both loops should be on the same datatable. Inside the nested loop, check if the names match. If they do, add the “Name”, “Location” and “Item” to a separate datatable (let it be TEMP_TABLE), with only these three columns. After the inner loop finishes and before the next iteration of the outer loop, check the number of rows in TEMP_TABLE.

If there’s 1 row, you have no duplicates, add the comment with a normal assign,
row.Item(“Comment”) = “Ok”

If there’s more than 1 row, use a loop to check TEMP_TABLE for your logic 3. It should be structured as follows:
if (temp_row.Item(“Item”).ToString == “111” AND temp_row.Item(“Location”) == “SYD”)
{ row.Item(“Comment”) = “Exception Location SYD”;
break;}

As for logic 2, copy only the Item column of TEMP_TABLE to a different single-column datatable, and use the Remove Duplicates activity on it. If it results with 1 row, no duplicates, so you do:
row.Item(“Comment”) = “Exception Same Item Code”

If there are more than 1 rows after the Remove Duplicates activity, then
row.Item(“Comment”) = “Exception Multiple Item”

Let me know if anything i’ve written here is unclear.

2 Likes

hello @rpa4 yes, your logic is correct, would you please be able to provide a sample xaml of these? I cannot do these in code :frowning: apologies!

Hello @rpa4 attached is my xaml for this project. Thank you! Emp_Record.zip (37.7 KB)

@James_Callos
Please check my answer