002 Group & Summarize Excel Data with a Scripting Language

 

Title: Find duplicates in one column then sum quantities into another column

Question

Source: https://stackoverflow.com/questions/64182673/excel-find-duplicates-in-one-column-then-sum-quantities-into-another-column

DATA  RESULT

 

A  1  A  11

A  1  B  7

A  9  C  5

B  2  D  4

B  2  E  8

B  3

C  5

D  4

E  7

E  1

 

Answer

A simple grouping & aggregation on Excel can be achieved using pivot table or data SUBTOTAL. But I guess you want more than this. Probably you want to be able to perform the operation on different data using the same script or by modifying the script as little as possible.

A scripting language can do this.

I found that you are not satisfactory about the VBA script someone offered. I know how you feel. VBA is too roundabout in handling this. There are dozens of lines for such a simple grouping & aggregation. It’s dreadful for business people.

Another two scripting languages that handle Excel data conveniently and that are simpler to use and easy to understand than VBA are Python Pandas and esProc SPL. The latter does your task in a rather concise and effortless way:

A

B

1

=file("D:/data.xlsx").xlsimport()

2

=A1.groups(_1;sum(_2))

/ Group rows by the first   column and summarize the second column; you can also use column names

3

=file("D:/result.xlsx").xlsexport(A2)

SPL is good at handling operations on xls or csv files. Find more examples and learn more about SPL in http://www.raqsoft.com/p/script-over-csv-xls