Automation_idea_Excel

Help Something Else
1

Hi All,

I have two Datatable, it should compare the last columns(“Supplier Consignment”), in DT2 last column, it sholud sum one by one and compare DT1 column if its matches, then my FinalDT should be as below datatable,

4000+4000=8000
104000+116000+88000+44000=352000

DT1

Item Number Site UM Supplier Consignment
150275-37 217 EA 8000
150275-44 217 EA 352000

DT2

Location Lot/Serial Supplier Consignment
EBL02B2 L202405170027 4000
EBL02B2 L202405250026 4000
EBE06C1 L202405080004 104000
EBE06C1 L202405170010 116000
EBE06C1 L202405240006 88000
einspec L202405290002 44000

FinalDT should be

Item Number Site UM Location Lot/Serial Supplier Consignment
150275-37 217 EA EBL02B2 L202405170027 4,000.00
150275-37 217 EA EBL02B2 L202405250026 4,000.00
150275-44 217 EA EBE06C1 L202405080004 104,000.00
150275-44 217 EA EBE06C1 L202405170010 116,000.00
150275-44 217 EA EBE06C1 L202405240006 88,000.00
150275-44 217 EA einspec L202405290002 44,000.00

Thanks in advance

2

@ashokkarale can you please help here

3

Assuming the data table rows will always be in an order such that this logic works you could try a nested loop. Iterate through DT1 to get the Supplier Consignment total you are looking for. Then loop through DT2 storing the running sum and index of the row in the data table. Once your running sum matches the Supplier Consignment in DT1 then break the nested loop execution and move onto the next DT1 row until complete.

You’ll want to make sure you keep track of row indexes in both tables so you start at the correct positions.

I’m not sure what your expected dataset size will be but it could also be done in invoke code activites with C# or VB.