Check duplicates and put comments

Help Studio
, , ,
1

Hello Everyone

Good day!

I have an inquiry regarding in checking if the records have duplicates… (Filters are First name, Last name, and Company) and I need to put comment such as ‘Duplicate Entry 1’++… Appreciate your help!

ID First Name Last Name Age Company Location Comment
1 James Fernandez 20 Company A US Duplicate Entry 1
2 James Fernandez 21 Company A US Duplicate Entry 2
3 James Fernandez 20 Company b US Duplicate Entry 3
4 James Fernandez 20 Company b US Duplicate Entry 4
5 Escanor Lion 22 Company A UK Singe Record
1 Like
2

Hi @Callos_James_AU,

Good day.

The general method is to use for each loop with the of condition.

But to simplify the following steps can be used…
The above table is in Dt.

  1. Clone the Dt
  2. For each loop.
  3. Filter the condition and update comments to the new filtered Dt.
  4. Add the data rows of filtered-dt to the clonedt.
  5. Remove the datarows from the Dt.

This might be better than the general one.

1 Like
3

@Callos_James_AU

Once you have read the excel with column
ID First Name Last Name Age Company Location Comment, say output in DT1

Now get distinct Values from the excel based on your filter

`DataTable (DT2)= DT1.DefaultView.ToTable(true, “First Name”, “Last Name”, “Company”)

Now loop through DT2,
Inside loop use Linq Query
Array of datarow (Z) = (From r in DT1.AsEnumerable.where(function(x) convert.tostring(x(“First Name”)) = Convert.tostring(row(“First Name”)) and convert.tostring(x(“Last Name”)) = Convert.tostring(row(“Last Name”)) and convert.tostring(x(“company")) = Convert.tostring(row(“Company”)) ) select r).toarray

use if to Check z.count>0 , if yes then
Use for each and loop through Z
assign counter= 1
and update the comment with counter as dynamic

Regards

2 Likes
4

Hello! Sorry but im not familiar how to use linq. Is there any other way to do it? I also want to put the comment in the same excel file, not create a new one… Thank you!!

5

Hello @Priyanka_Ramesh

Would you please be able to provide a sample xaml for this? Thanks! I understand the logic you said but I don’t know how to apply it… Thanks again

6

Check this workflow and output file, @Callos_James_AU
Excel_Automation.xaml (15.5 KB)
and Updated_Comment.xlsx (7.4 KB)

2 Likes
7

Thanks for this @Manish540 Will check this and let you know! :slight_smile:

8

Okay @Callos_James_AU