WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: kle2 on December 11, 2014, 12:02:03 PM

Title: Sum up duplicate values in Excel
Post by: kle2 on December 11, 2014, 12:02:03 PM
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
Title: Re: Sum up duplicate values in Excel
Post by: 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  .  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. 
Title: Re: Sum up duplicate values in Excel
Post by: stanl on December 12, 2014, 03:56:18 AM
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.
Title: Re: Sum up duplicate values in Excel
Post by: kle2 on December 12, 2014, 04:18:42 AM
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
Title: Re: Sum up duplicate values in Excel
Post by: td on December 12, 2014, 02:36:56 PM
They are Excel functions.  You can find more information about them in MSFT's online Excel documentation.
Title: Re: Sum up duplicate values in Excel
Post by: kle2 on December 15, 2014, 04:25:57 AM
yes, I can do the sum via excel.  But that won't help me with the automation purpose. -Thanks!
Title: Re: Sum up duplicate values in Excel
Post by: td on December 15, 2014, 06:32:44 AM
You can add and execute Excel functions via COM Automation so I am not sure why Excel function wont help you with automation. 
Title: Re: Sum up duplicate values in Excel
Post by: kle2 on December 15, 2014, 08:40:56 AM
Hi TD, can you give me a little more detail on how to automate via excel? -Thanks
Title: Re: Sum up duplicate values in Excel
Post by: td on December 15, 2014, 01:41:08 PM
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.
 

Title: Re: Sum up duplicate values in Excel
Post by: kle2 on December 18, 2014, 07:19:18 AM
Thank you again, TD!  I will take a look at the link...