Sum up duplicate values in Excel

[kle2]

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

[td]

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. 

[stanl]

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.

[kle2]

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

[td]

They are Excel functions.  You can find more information about them in MSFT's online Excel documentation.

[kle2]

yes, I can do the sum via excel.  But that won't help me with the automation purpose. -Thanks!

[td]

You can add and execute Excel functions via COM Automation so I am not sure why Excel function wont help you with automation. 

[kle2]

Hi TD, can you give me a little more detail on how to automate via excel? -Thanks

[td]

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

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.
 

[kle2]

Thank you again, TD!  I will take a look at the link...