viewpoint-particle

Author Topic: Sum up duplicate values in Excel  (Read 5668 times)

kle2

  • Newbie
  • *
  • Posts: 16
Sum up duplicate values in Excel
« 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

td

  • Tech Support
  • *****
  • Posts: 3992
    • WinBatch
Re: Sum up duplicate values in Excel
« Reply #1 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. 
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

  • Pundit
  • *****
  • Posts: 1595
Re: Sum up duplicate values in Excel
« Reply #2 on: December 12, 2014, 03:56:18 am »
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

  • Newbie
  • *
  • Posts: 16
Re: Sum up duplicate values in Excel
« Reply #3 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

td

  • Tech Support
  • *****
  • Posts: 3992
    • WinBatch
Re: Sum up duplicate values in Excel
« Reply #4 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

kle2

  • Newbie
  • *
  • Posts: 16
Re: Sum up duplicate values in Excel
« Reply #5 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!

td

  • Tech Support
  • *****
  • Posts: 3992
    • WinBatch
Re: Sum up duplicate values in Excel
« Reply #6 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. 
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

kle2

  • Newbie
  • *
  • Posts: 16
Re: Sum up duplicate values in Excel
« Reply #7 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

td

  • Tech Support
  • *****
  • Posts: 3992
    • WinBatch
Re: Sum up duplicate values in Excel
« Reply #8 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

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.
 

"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

kle2

  • Newbie
  • *
  • Posts: 16
Re: Sum up duplicate values in Excel
« Reply #9 on: December 18, 2014, 07:19:18 am »
Thank you again, TD!  I will take a look at the link...