Hi Everyone, I want to remove the duplicate records in an Excel file by comparing the values of 2 columns and only keep unique records,
here is the file now I want to remove Duplicate records from the file where Invoice number and Supplier Number column values are same. Ex. in the pic I want to remove 4th row as row 3 and row 4 have same supplier and invoice number. Rest the data will be kept as it is
Can someone please provide me the query
1. Read Range (Output: DataTable)
2. Assign uniqueRows = (From row In dataTable.AsEnumerable()
Group row By key = New With {Key .Column1 = row.Field(Of String)("Column1"), Key .Column2 = row.Field(Of String)("Column2")} Into Group
Where Group.Count() = 1
Select Group.First()).CopyToDataTable()
3. Write Range (Input: uniqueRows)
Try this
distinctDataTable = yourDataTable.AsEnumerable() _
.GroupBy(Function(row) New With {
Key .Col1 = row.Field(Of String)("Column1"),
Key .Col2 = row.Field(Of String)("Column2")
}) _
.Select(Function(group) group.First()) _
.CopyToDataTable()
Try this:
dt_Output = (From row In dtInput.AsEnumerable()
Group row By Key = New With {
Key .Column1 = row.Field(Of String)("Column1"),
Key .Column2 = row.Field(Of String)("Column2")
} Into Group
Where Group.Count() = 1
Select Group.First()).CopyToDataTable()
dt_Output is of DataType System.Data.DataTable
Hope it helps!!
getting this issue "Unable to cast object of type ‘System.Double’ to type ‘System.String’
Try this:
dt_Output = (From row In dtInput.AsEnumerable()
Group row By Key = New With {
Key .Column1 = row.Field(Of Double)("Column1"),
Key .Column2 = row.Field(Of Double)("Column2")
} Into Group
Where Group.Count() = 1
Select Group.First()).CopyToDataTable()
Hope it helps!!
the data is in string format
Please share the sample excel file
here is the file now I want to remove Duplicate records from the file where Invoice number and Supplier Number column values are same. Ex. in the pic I want to remove 4th row as row 3 and row 4 have same supplier and invoice number
→ Read Range Workbook
Output-> InputDt
→ Use syntax in Assign:
dtExtractedText = (From row In InputDt.AsEnumerable()
Group row By Key = New With {
Key .InvoiceNumber = row.Field(Of Object)("Invoice Number"),
Key .SupplierNumber = row.Field(Of String)("Supplier Number")
} Into Group
Select Group.First()).CopyToDataTable()
=> Write Range Workbook dtExtractedText
Please try this @Vineet_Joshi
Hope it helps!!
Try this
DataTable.AsEnumerable() _
.GroupBy(Function(row) New With {
Key .InvoiceNumber = row.Field(Of Object)("Invoice Number"),
Key .SupplierName = row.Field(Of String)("Supplier Name")
}) _
.Select(Function(Group) Group.First()) _
.CopyToDataTable()
Input:
Output:
Regards,
Please try this, it is working
uniqueRows = (From row In dataTable.AsEnumerable()
Group row By key = New With {Key .Column1 = row.Field(Of Object)("Invoice Number"), Key .Column2 = row.Field(Of String)("Supplie Name")} Into Group
Where Group.Count() = 1
Select Group.First()).CopyToDataTable()
Output:
please share the sequence once @rlgandu
Hi @rlgandu , can you please share with me the sequence file? I need it for verification Thanks
Check the below workflow
Sequence33.xaml (10.7 KB)
AAAA.xlsx (9.6 KB)
Sheet1 is Input and Sheet3 is Output.
Regards
Sequence10.zip (1.9 KB)