How to erase dublicates (Excel/ DataTable)?

datatable
excel

#1

Hello
I Need some help.

I have an Excel file with 3 columns (ID,Channel, Date).
In the first column “ID” are some equal values respectively duplicates, which i want to erase from the file (the whole row).

How can I delete all the “ID” duplicates?
Is it possible to do this directly in a data table? (Read range-> generate DT -> erase duplicates)

Thanks a lot for your help.
Reti


#2

Hi @Reti,

Kindly check the example that I have provided below, it will only work if the entire row contains duplicated data, otherwise it won’t work.

Main.xaml (7.6 KB)


#3

Hi @acaciomelo

Great, Thank you! This is exactly what i need :slight_smile:

Furthermore I would like to count the ID’s per Date and put the amount of each Date in a specific cell of another Excel file.
Is there an easy way to solve this? Is it possible to evaluate this directly in a data table or should I do it in a separate Excel file with a formula (write cell)?

Thank you!
Best regards
Reti


#4

Hi @Reti,

Yes, in order to do it you could use LINQ like the example from this link:

I’m trying the following LINQ query, but it’s not working yet:

(From sales In From row In dtResult.AsEnumerable()  
Group row By row.Field(Of Int32)("Id") Into sales
Order By sales.Id 
Select New With { _
	.Id = sales.Id, _
	.CountOfClients = sales.Count() _
})

It says that it contains an error, but I couldn’t find how to solve it until now.


#5

I’ve extracted a datatable from an Excel file and this datatable don’t have column names.

Could I use this method using column indexes? How? If not, how to do it?

Thanks!


#6

Hi,

You can just use the row index in place of “Id”