Distinct column based on a condition

Help Activities
, ,
1

Hi Team i have a data table from which based on three columns A,B,C i need to to remove duplicates. If column A has a value 1 and column B has a value 2 and column C has a value 3. I need to check if all 3 are matching in any other row in the datable and get the total of those column and remove the duplicate. If All 3 are not matching with any other rows. have to let it be. In the final output datable only distinct should be there if any rows match it’s sum should only be there. What is the best approach for this?

2

Hi @Ajith209

Could you please share sample input excel with dummy data.

Regards

3

@Ajith209

  1. Read Range (InputDataTable from Excel)

  2. OutputDataTable = Create DataTable (same structure as InputDataTable)

  3. For Each Row in InputDataTable
    a. Assign: currentRowA = row(“A”).ToString()
    b. Assign: currentRowB = row(“B”).ToString()
    c. Assign: currentRowC = row(“C”).ToString()

    d. Assign: sum = 0

    e. For Each DataRow in OutputDataTable

    • Check if DataRow(“A”) = currentRowA AND DataRow(“B”) = currentRowB AND DataRow(“C”) = currentRowC
      • If true, accumulate sum, remove DataRow from OutputDataTable, and exit loop

    f. If sum > 0

    • Add new DataRow to OutputDataTable with accumulated sum

  4. Write Range (OutputDataTable to Excel or Text File)

4

@Ajith209

Assign - To: distinctRows
Type: System.Data.DataTable
Value: dt.AsEnumerable()
.GroupBy(Function(row) New With { Key .A = row(“A”), Key .B = row(“B”), Key .C = row(“C”) })
.Select(Function(group) dt.Clone.LoadDataRow({group.Key.A, group.Key.B, group.Key.C, group.Sum(Function(row) Convert.ToDecimal(row(“YourNumericColumn”)))}, False))
.CopyToDataTable()
I hope this will help you

5

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