Check Duplicate records with specific condition/filter Excel

[James_Callos]

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

[Srini84 (Srinivas)]

Hi @James_Callos

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

Thanks

[James_Callos]

Hello @Srini84 good morning! It is in excel.

[Doanh]

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