Need duplicate data and write DUP in another cell


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)


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())


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

→ Click on Edit arguments and configure as below image,

→ 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)

Hope it helps!!

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

Hi @Aarthy1 ,

Could also check with the below similar post :