Split the datatable based on sum of two rows as 0

Help Activities
, ,
1

Hi,
this is my datatable input
image
this is my expect output
image
matched records
image
Unmatched records
for every row if there is a matching negative amount in B column
it should be segregated to Matched records and remaining rows to Unmatched records
can anyone help me with linq query for this problem
thanks in advance

2

Hi @Sanjeev_Kumar4

→ Read Range Workbook
Output-> dtInput

image
image319×537 3.71 KB

→ Use below syntax in Assign activity

-> dtMatched= dtInput.Clone()
-> dtUnmatched= dtInput.Clone()
-> matchedRows= From row In dtInput.AsEnumerable()
                  Let A = row.Field(Of String)("A")
                  Let B = row.Field(Of Double)("B")
                  Where dtInput.AsEnumerable().Any(Function(r) r.Field(Of String)("A") = A AndAlso r.Field(Of Double)("B") = -B)
                  Select row
-> dtMatched= matchedRows.CopyToDataTable()
-> unmatchedRows= From row In dtInput.AsEnumerable()
                    Where Not matchedRows.Contains(row)
                    Select row
-> dtUnmatched= unmatchedRows.CopyToDataTable()
-> DataTable_Matched= dtMatched.DefaultView.ToTable(True, "A", "B")
-> DataTable_Unmatched= dtUnmatched.DefaultView.ToTable(True, "A", "B")

Note: dtMatched, dtUnmatched, DataTable_Matched, DataTable_Unmatched are of DataType System.Data.DataTable. matchedRows and unmatchedRows are of DataType IEnumerable(System.Data.DataRow).
→ Use Write Range Workbook to write DataTable_Matched and DataTable_Unmatched back to excel.
Output:
DataTable_Matched
image
DataTable_Unmatched
image
xaml:
Sequence3.xaml (11.6 KB)

Hope it helps!!

3

Hi mahesh thank you for the solution but the i need the duplicates also for matched records for every -ve amount we should have +ve amount but i am getting all -ve amounts
image

4

Hi @Sanjeev_Kumar4

If possible could you share input file. I will check and let you know.

Regards

5

Forum question.xlsx (24.2 KB)
hi mahesh sheet1 is the input and matched and unmatched sheets are output

thanks in advance

6

@Sanjeev_Kumar4

small clarity we need to compare the every row with the preceding row or all the rows ?

image

7

every row but once you find the -ve match break the loop and proceed with next row basically i want for every credit transaction amount i want debit transaction amount in matched records.