Count Filtered/Duplicate Columns in Excel

Help Studio
, ,
1

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

image
image1655×250 14.6 KB

2

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
3

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 - #11 by aksh1yadav

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

Regards,
Nithin

4

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

5

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

6

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

7

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

8

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

2 Likes
9

Thanks for this @ppr! It works!

10

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