Compare two sheets Tabs and create another sheet based on below two scenarios

Help
, ,
1

Scenario 1 - Output sheet contains new records based on Employee ID (Which are uncommon in Dt1 & Dt2)

Scenario 2 - Output sheet contains records (compare records in Dt2 and check whether record having “In Progress” Status. If records contains “In Progress” then dont pick that record from Dt1. If record having Actioned status and not having In Progress - pick that record.

for e.g Dt1

Emp ID Name Status
1 ABC Actioned
2 CDE Actioned
3 EFG Actioned
4 HIJ Actioned
5 JKL In Progress
6 LMN In Progress
1 ABC Actioned
2 CDE Actioned
3 EFG In Progress
4 HIJ In Progress

Dt2

Emp ID Name
1 ABC
2 CDE
3 EFG
4 HIJ
5 JKL
10 OOO
8 YYY
7 XXX

Output Sheet must contains below records
Emp ID Name Status
1 ABC
2 CDE
10 OOO
8 YYY
7 XXX

2

Hi.
Did you try using “join data tables” in studio?
I think you can use ‘inner join’ of ‘join data tables’ at Scenario 1 and use condition (like ‘status != In Progress’) of 'join data tables at Scenario 2.
Thanks.

3

Scenario 1 is achieved using LINQ to get unmatched Emp ID.
I was not getting expected results using join data table…for e.g. if there are 5 records of ABC = “Actioned” and any new record of ABC Employee come-up in Dt2 then it needs to come up in Output Sheet to process further.

In another example, if ABC having 4 records as Actioned and one record as “In progress” then dont include in output sheet as this records is already in Progress @Dt1

4

@Sonalk
welcome to the forum

for scenario 2 give a try
(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
By d1(0).tostring.trim Equals d2(0).tostring.trim And d1(1).tostring.trim Equals d2(1).tostring.trim
Where d1(2).toString.Trim.Equals(“Actioned”)
Select d1).toList

With having a count check on the result it can be copied to a new/different datatable

2 Likes
5

Thanks @ppr.

image
image1920×1080 220 KB

6

Getting Compiled error

(From t1 In dtComponent2.AsEnumerable() Join t2 In dtOutputSheet.AsEnumerable() By t1(0).tostring.trim Equals t2(0).tostring.trim And t1(1).tostring.trim Equals t2(1).tostring.trim Where t1(2).toString.Trim.Equals(“Actioned”) Select t1).count>0

7

sorry was a typo give a try on:

(From t1 In dtComponent2.AsEnumerable()
Join t2 In dtOutputSheet.AsEnumerable()
On t1(0).tostring.trim Equals t2(0).tostring.trim And t1(1).tostring.trim Equals t2(1).tostring.trim
Where t1(2).toString.Trim.Equals(“Actioned”) Select t1).count>0

3 Likes
8

Thanks @ppr for given query…

One thing to mentioned here = if there are three records of ABC in DT1 (master database), One record of same employee ID is available in DT2 (raw data from system)

Two Old records were “Actioned” and latest record is “In Progress” (Dt1- master db) then it should not come in Output Sheet irrespective of record available in DT2 (raw data from system).

or If all records were Actioned in DT1 - master db, then it should come as new record is available in Dt2 (raw data from system).

DT1 DT2 Output Sheet
Actioned

10

@ppr: Hope this summary helps!!

DT1 (Master DB) DT2 (RAW Data from System) Output Sheet
Actioned - Not In progress record record available (employee ID exists) include this record from dt2
In progress record available (employee ID exists) dont include from dt2
Actioned - Not In progress record No record exists for same employee don’t include (not exists in DT2)
In progress No record exists for same employee don’t include (not exists in DT2)
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.