Need duplicate data and write DUP in another cell

Help Studio
,
1

Hi

The file has 2 sheets
First file sheet1 is the input.
We need to find the duplicate values of A column and enter DUP in B column
I give the sample output in sheet 2
(I tried using for each activity and if activity but data is not correct . In A column 1st data is having 1 time but the output DUP KB)

2

@Aarthy1

inputDataTable=(From row In inputDataTable.AsEnumerable()
 Let duplicate = If((From r In inputDataTable.AsEnumerable()
                    Where r("AB header ").ToString().Trim() = row("AB header ").ToString().Trim()
                    Select r).Count() > 1, "DUP", row("TA").ToString().Trim())
 Select inputDataTable.Clone.Rows.Add(row.ItemArray.Take(row.ItemArray.Length - 1).Concat({duplicate}).ToArray())
).CopyToDataTable()

Output:

image
image498×632 10.5 KB

image
image784×476 18.9 KB

3

Hi @Aarthy1

You can use the VB code to achieve it, Follow the below steps,
→ Use the Read Range workbook activity to read the excel and store in a datatable called dt.
→ Then drag and drop the Invoke code activity and click on Edit code and paste the below code,

Dim duplicates As List(Of DataRow)
Dim isFirstDuplicate As Boolean

For Each row As DataRow In dt.AsEnumerable()
    duplicates = dt.AsEnumerable().Where(Function(x) x(0).ToString() = row(0).ToString()).ToList()
    isFirstDuplicate = duplicates.IndexOf(row) = 0 AndAlso duplicates.Count > 1
    If isFirstDuplicate Then
        row(1) = "DUP"
    End If
Next

→ Click on Edit arguments and configure as below image,

image
image952×522 20 KB

→ Then use the Write Range workbook activity to write the dt to the same excel file.

Check the below workflow for better understanding,
Sequence3.xaml (7.7 KB)

Sequence3
Sequence3815×580 42.9 KB

Hope it helps!!

4

we would recommend to use a groupby approach for duplicate detection
OR
Lookup Dicitionary calculating in advance the duplication status

5

Hi @Aarthy1 ,

Could also check with the below similar post :