Write duplicate in front of duplicate values in excel

Help Studio
, ,
1

Hello Friends,

I have excel data in column c with a few duplicate values, and I want to write the duplicate word in column d in front of the duplicate values in column c. How can we do it? If possible please do it and provide me XAML for this.

duplicate highlight
duplicate highlight842×722 82.8 KB

2

Hi @Jeeru_venkat_Rao ,
Welcome to forum, And yes you can do that ,

Use For each row, and using lookup data table check if that element is available in the datatable if yes then use an assign activity to update the d column with name “Duplicate”.

Please let me know if stuck while you are doing.

HAPPY AUTOMATION !!!

3

How can we use the lookup data table here, Have you seen the attachment? I want it like that. Can you elaborate bit more.

4

Hi @Jeeru_venkat_Rao ,

Can you try this,

input

S.NO Name Department Duplicate
1 Uma Test
2 Keerthivas Test
3 Sarvanan Test1
4 Likith Test1
5 Muk Test2
6 Hari Test

Output

S.NO Name Department Duplicate
1 Uma Test
2 Keerthivas Test Duplicate
6 Hari Test Duplicate
3 Sarvanan Test1
4 Likith Test1 Duplicate
5 Muk Test2

Identify Duplicate Rows.xaml (16.3 KB)

5

Bro, Thanks for your response.
But this is not what I am looking for.

duplicate highlight
duplicate highlight842×722 82.8 KB

Please see attached image.

If name comes twice then it write Duplicate in front of 2nd value as shown in attachment.

6

Hi @Jeeru_venkat_Rao ,

Can you look into my example i’m targeting “Department” column, Your targeting column is “Company name”. targeting column only differed. Just change input DT and column name. you will get the output

Thanks

7

Hi @Jeeru_venkat_Rao ,

You could also try with an alternate using Linq. Check the steps below :

  1. Assumed the Datatable is read using Read Range Activity, say DT. Also assuming that the Remarks Column is not present already in this data.

  2. Next, we clone the Input datatable DT and create the Output datatable, OutputDT where OutputDT is a variable of type Datatable.

OutputDT = DT.Clone

The above is done using an Assign Activity.

  1. Next, Add the Remarks Column to the Output Datatable using Add Data Column Activity.

  2. We can now use the Linq below in an Assign Activity to update the Remarks Column.

OutputDT = (From d1 In DT.AsEnumerable
Group d1 By k=d1(2).toString.Trim Into grp=Group
From g In grp.DefaultIfEmpty
Select ra = g.ItemArray.Append(If(grp.Count>1 AndAlso g.ItemArray.SequenceEqual(grp.Last.ItemArray),"Duplicate","")).ToArray
Select OutputDT.Rows.Add(ra)).CopyToDataTable
  1. You could then check the output by writing the OutputDT datatable to an Excel file.

Let us know if able to understand the approach or if faced with errors provide us more info on it.

8

Thank you Uma, Its working fine.

Thank you SuperManPunch, I will try and let you know if any issues.