Sum rows from different excel sheets if value matches and assign it to third column

I have two excel sheets Sheet1 and Sheet2 shown below:
Sheet1 image
Sheet2 image
Output image

The output file has a new column “Sqty New” = Sqty - Aqty.

Here if the value of sheet1 matches with sheet2 the do subtraction otherwise leave as it is .

I tried using for each loops but it is saying "Sequence contains no elements "

Can anyone suggest the workflow to be used for the above mentioned situation.

1 Like

Hi,
You Can try This Logic
Sequence.xaml (13.2 KB)

3 Likes

Thanks @Manish_Talele the workflow is working correctly but when i am using it on my actual data it is showing error "Cannot convert Generic Value to System.Double " in the assign of if condition.

The problem is with regional settings.
Are you using dots or comma as a delimiter?

Change the variable type to string rather than using Generic Value of the particular variables.

No i just change the column names only.
I also tried by changng the variable type from Generic to Double but it is showing error “Can not assign ‘CDbl(row1(“Storeusedqty”))’ to ‘strSqty’”

Can you show us the workflow?

Thanks @pllo2ptk Main.xaml (16.4 KB) Here is the workflow which i edited.

You sent xaml file without json…

Sorry @pllo2ptk sequence.zip (14.2 KB)
Here is the folder attached.

I think that I know what is the issue.
Assign StrAqty should be: "CDbl(row2(“New Actual Inventory”).toString)
and StrSqty: CDbl(row1(“Storeusedqty”).toString)

Thanks @pllo2ptk I tried what you said but it is showing error " Can not assign ‘CDbl(row1(“Storeusedqty”).ToString)’ to ‘strSqty’ "

can you show the excel file?

What is Variable type of strSqty?

@pllo2ptk when i used CDbl(row1(“Storeusedqty”).ToString) the variable type of strSqty is System.Double otherwise it was showing error.

Thanks @Manish_Talele I can’t share the actual data but it is exactly the same format i shared

Okay, so problem is during debugging, I guess?
Try to make a breakpoint before assign and check value.
I think the problem is with decimal sign.

While storing the data in variable use String variable later you can convert it to any other variable type.
I mean use Strqty variable as string and assign it to row(“Storeusedqty”).ToString

For a glimpse of actual data it is like

Thanks @Manish_Talele I tried assigning strSqty and strAqty to string and the while doing subtraction used “CDbl(strSqty)-CDbl(strAqty) " the error occuring is " Conversion from string “” to type ‘Double’ is not valid.”