Count Filtered/Duplicate Columns in Excel

Hello Everyone

Good day!

In the below table, I wanted to count if the record has duplicate with the criteria of ‘Name’ only. My logic/activity is like this…

  1. Read Range
  2. Filter Data Table (see below image for the logic)
  3. If (dt.rows.count > 1) then “duplicate” else “not duplicate”

However, the count of rows is always 0. Am I doing something wrong?

ID Name Age
1 James 25
2 James 25
3 James 25

Hai @Callos_James_AU
Use a for each loop on the rows. assign the value to a variable to check other rows for duplicates

CheckDups = row.item(“ColumnName”).tostring

Then have another for each loop going through the same column and an IF statement. Obviously it’ll find itself as a match but you can handle this

IF row.item(“ColumnName”).tostring = CheckDups

THEN

Counter = Counter+1

1 Like

Hi @Callos_James_AU,

You can populate a data view with unique values in 1 column in it. It will give you a datatable with only 1 column & only the unique values.

You can find the code here :Select Distinct and obtain all row of it

Gist:
Datatable DistinctValuesDt = Datatable.DefaultView.Totable(true,“Name”)
DistinctValuesDt .Rows.Count will give you the count of distinct values.

Regards,
Nithin

Hello @Nandhuba Thank you for this but is there a less action doing this? Because I saw before that this can be done by just using filter data table. Thanks

Hello @Nithin_P I tried this but it is giving me ‘23’ counts even if the records that I wanted to count the duplicates are just 3. If by chance, do you know how to do this by just using filter data table? Thanks

Hello @Callos_James_AU
In Attached image in value field look like ,you checked with datarow.
Could your share screenshot for workflow.
Like input datatable, output datatable
And you should be checked filtering mode

Hi @Callos_James_AU,

I think what you need is to get the duplicate values in the datatable & not the unique values. In that case here is what you need to do:

  • Datatable DistinctValuesDt = Datatable.DefaultView.Totable(true,“Name”)
  • Using For Each Row loop on DistinctValuesDt
  • Use Filter DataTable inside the loop & Assign “Name”=row(“Name”) & Assign the output to a new DataTable
  • If the Number of rows in the new DataTable is more than 1, then its a duplicate. You can write the datatable to an excel to verify & if other rows also come up with count more than 1, merge the DataTables

Regards,
Nithin

@Callos_James_AU
Find a demo XAML here:
Callos_James_AU_2.xaml (11.4 KB)

2 Likes

Thanks for this @ppr! It works!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.