Hi,
How do I sum up duplicate name in the excel using Winbatch for monthly automation?
Assume that I have the below excel file:
A B
Smith 200.00
Dean 100.00
Smith 100.00
Smith 50.00
Wilson 25.00
Dean 25.00
Barry 100.00
The end-result would look like this
Smith 350.00
Dean 125.00
Wilson 25.00
Barry 100.00
I suppose you could use a SUMIFS formula on each unique value in the first column . You could also use the brute force approach and manually spin through the rows summing when when you found a match but that would take time to develop.
If you know a bit about Excel, the best approach may be try and develop a macro to perform the task. Once the you have the macro, it can usually be easily converted to a WinBatch script.
The Tech Database contains several Excel examples of using Excel formulae and a more knowledgeable Excel user may chime in with a canned solution.
Quote from: td on December 11, 2014, 01:40:14 PM
I suppose you could use a SUMIFS formula on each unique value in the first column
Or dsum, which might be a little cleaner and works with Office 2003.
Thanks for your suggestions!
SUMIFS or DSUM, are they a Winbatch function? I could not find them...sorry I am not familiar with them!
Khoa
They are Excel functions. You can find more information about them in MSFT's online Excel documentation.
yes, I can do the sum via excel. But that won't help me with the automation purpose. -Thanks!
You can add and execute Excel functions via COM Automation so I am not sure why Excel function wont help you with automation.
Hi TD, can you give me a little more detail on how to automate via excel? -Thanks
There are close to 100 articles involving Excel in the searchable Tech Database. That is a good place to start.
Here is one example that demonstrates how to add a formula to a spread sheet.
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel/Samples~from~Users+Auto~Sum~Two~Columns~In~Excel.txt (http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel/Samples~from~Users+Auto~Sum~Two~Columns~In~Excel.txt)
As previously mentioned there are at least several ways to approach your problem and you don't have to necessarily use a formula. That is just one possibility. It depends a lot on what you want to do with the result of your summation.
Thank you again, TD! I will take a look at the link...