Subtact from two data table

Help Studio
, , , , , , ,
1

Hello Team,

We have Sheet1 with Below data

A table displays supply information, listing SupplyId, Product, and KeyProducts with corresponding values for each record. (Captioned by AI)
A table displays supply information, listing SupplyId, Product, and KeyProducts with corresponding values for each record. (Captioned by AI)571×187 6.59 KB

We have Sheet2 with Below Data

A table with columns labeled "SupplyId," "Product," and "KeyProducts," containing two rows of data. (Captioned by AI)
A table with columns labeled "SupplyId," "Product," and "KeyProducts," containing two rows of data. (Captioned by AI)589×196 5.79 KB

How can we get Sheet 3 in below format based on Subtraction done

Example
In Sheet 1 Product Column 89 -Sheet 2 Product Column 89 = 0

Which is Updated in Sheet 3

A spreadsheet displays a table with three columns titled "SupplyId," "Product," and "KeyProducts," containing the respective values 2, 0, 0 in the first row and 8, 72, 1 in the second row. (Captioned by AI)
A spreadsheet displays a table with three columns titled "SupplyId," "Product," and "KeyProducts," containing the respective values 2, 0, 0 in the first row and 8, 72, 1 in the second row. (Captioned by AI)603×188 6.16 KB

Have attached Excel Sheet for Reference which contains Expected format of Sheet 3
Test dt (1).xlsx (7.4 KB)

Look forward to you suggestions

Thanks in advance

2

Hi @NISHITHA

Read the Sheet1 to datatable as dt_input and Sheet2 to dt_input2, Use the below linq

dt_Result= dt_input.AsEnumerable().SelectMany(Function(row1) dt_input2.AsEnumerable().Where(Function(row2) CType(row2("SupplyId"), String) = CType(row1("SupplyId"), String)).Select(Function(x) dt_input.Clone().LoadDataRow({x("SupplyId").ToString(), CDbl(row1("Product")) - CDbl(x("Product")), CDbl(row1("KeyProducts")) - CDbl(x("KeyProducts"))}, False))).CopyToDataTable()

Write the dt_Result to Sheet3 which solves your usecase!

Output:
image

Do mark it as a solution if it helps you!

Thank You

1 Like
3

Any specific reason why you want to try via Data table only and not using Excel Formulas?

4

Thanks @Sanjay_Bhat it works

5

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