Find duplicates in a column using Excel activities

[adrianab.98 (Adriana)]

Hello, I need to find the duplicates in a excel column, but I want to use excel activities not the workbook ones.

I have something like this:
IDCol
10
80
67
10
20
80

I I need that duplicates to have a specific format (idk, bold for example)
IDCol
10
80
67
10
20
80

How can I do that? It seems that For each row doesn’t work as I expected. I tried like I would do with other language (like openpyxl in python)to use two for loops and to compare the first item with entire column, then the second with the column and so on.

[ppr (Peter Preuss)]

one of many options is to use LINQ and calculate the duplicated values ( we do read range and work on datatable base). Let us know if you have more interest on this

arrDupCol1Vaues =

From d in YourDataTable.AsEnumerable
Group d by k=d("IDCol1").toString.Trim into grp=Group
Where grp.Count > 1
Select x = k).toArray

[Anil_G (Anil Gorthi)]

@adrianab.98

Another method to get unique values you can directly read the data into a datatable and then

uniquedt = Dt.Defaultview.ToTable(true,{"IDCol"})

If you want to make the specific one bold…

Initialize a list(L1)new list(of string) , Then loop through dt and then inside the loop you can use if condition to check if the list contains the currentrowitem …L1.any(function(x) currentrow("IDCol").Tostring.Contains(x))

In the else side use the below logic to make it bold(send ctrl+B)…and leave the then part blank

How to Make Spesific Excel Rows Bold

Hey @mazlumkacar What you can do is… Use excel application scope and open the excel file. In that use a read range to read the data so that you can get the number of rows… use a for each row loop and loop through… within the loop, have a variable that increments in each iteration. This variable can be used to identify the row number… have a if condition to check whether the current row has the required value if true, use select range activity and specify the range and highlight that row. You …

Cheers

[Kaviyarasu_N]

Hi @adrianab.98
If we want to find the array of duplicate values from a particular column of a table then
In a assign activity

Code:
List_variable = datatable.AsEnumerable().Select(Function (a) a.Field(of string)(“yourcolumnname”).ToString).ToArray().GroupBy(Function(x) x).Where(Function(y) y.Count() > 1).ToList()

Regards,
Kaviyarasu N