Filter through excel spreadsheet and add/subtract values from total

[olsonse]

Hi I have an excel spreadsheet where I need to filter to find from a specific vendor and then take those values that match with that vendor and subtract them from the whole total of all the vendors. How would I go about doing this? I attached an example of what I need to do: So I need to take the two amounts from Vendor W_01 and subtract them both from the total amount.

[olsonse]

sorry I highlighted the wrong one for the second one. I need to subtract 14,000 from 1,000 and 1,000 for W_01 Vendor.

[Pratik_Wavhal]

Hi @olsonse

So i have created one workflow which gives the exact output what you want.
Below is the workflow for the same :-
MainVendor.xaml (16.3 KB)

Created one dummy excel like your data :-
Add Premium Amt.xlsx (10.5 KB)

Output :-

Mark as solution and like it

Happy Automation

Best Regards
Er Pratik Wavhal

[olsonse]

I keep getting an error for the Total variable with CInt(item(4).ToString) expression. It says Option Strict On disallows late binding?

[Pratik_Wavhal]

Hi @olsonse

Can you send me your workflow .xaml file ??
So i can see what wrong is der going in your workflow
Bcz what solution i have gave you as the input and output file it is working correctly so

Happy Automation

Best Regards
Er Pratik Wavhal

[olsonse]

figured it out. thank you!

[Pratik_Wavhal]

Hi @olsonse

You are welcome

Happy Automation

Best Regards
Er Pratik Wavhal

[olsonse]

do you know how to get that difference amount and move it to another excel file? instead of having it pop up in a message box.

[Pratik_Wavhal]

Hi @olsonse

Just assign that difference amount to some variable and then make use of Write Cell activity.
Just thats it

Happy Automation

Best Regards
Er Pratik Wavhal

[olsonse]

thank you very much!