Check Duplicate records with specific condition/filter Excel

Build Studio
, , ,
#1

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
#2

Hi @James_Callos

Can you tell me where is the data, i.e., is it in Excel Sheet or SQL Database?

Thanks

#3

Hello @ksrinu070184 good morning! It is in excel. :slight_smile:

#4

Excel application Scope
__Read range -> Read excel data to a datatable variable (e.g. dtData)
__For each row in dtData
____Filter Data Table with below conditions
______Input DataTable: dtData
______Output DataTable: dtDataCheck
______column (0) = row(“Name”).ToString()
______column (1) = row(“Age”)
______column (2) = row(“SurName”).ToString()
______column (3) = row(“Location”).ToString()
____Assign strComment = “”
____If dtDataCheck.Rows.Count() = 1 -> Assign strComment = “ok”
____Else
______Assign bolAll111 = True
______Assign bolAllSYD = True
______For each rowCheck in dtDataCheck
________If rowCheck(“Item”).ToString() <> “111” -> Assign bolAll111 = False
________If rowCheck(“Location”).ToString() <> “SYD” -> Assign bolAllSYD = False
______If bolAll111 AND bolAllSYD -> Assign strComment = “Exception Location SYD”
______Else If bolAll111 -> Assign strComment = “Exception Same Item Code”
______Else Assign strComment = “Exception Multiple Item”
____Assign row(“Comment”) = strComment

The variable dtData will contain your expected result

How to check Duplicate records with specific condition/filter Excel