Automatic sum of numbers in a column

guy038

Hi, All,

In the P.S. and last part of this post :

https://community.notepad-plus-plus.org/post/94513

I proposed a tip to get a sum of several numbers, selected in a rectangular selection

I automated this process in two different macros !

The first macro, called Rectangular_Selection_Counting, shown below, can summarize a column of numbers, even containing space characters or empty zones

        <Macro name="Rectangular_Selection_Counting" Ctrl="no" Alt="no" Shift="no" Key="0">
            <Action type="0" message="2178" wParam="0" lParam="0" sParam="" />
            <Action type="2" message="0" wParam="41001" lParam="0" sParam="" />
            <Action type="0" message="2179" wParam="0" lParam="0" sParam="" />
            <Action type="2" message="0" wParam="42007" lParam="0" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="(\R)(?!\z)|\R?\z" />
            <Action type="3" message="1625" wParam="0" lParam="2" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="?1+:=" />
            <Action type="3" message="1702" wParam="0" lParam="640" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="0" message="2177" wParam="0" lParam="0" sParam="" />
            <Action type="2" message="0" wParam="41003" lParam="0" sParam="" />
        </Macro>

Let’s try with this INPUT text :

•--------------•-------------•--------------•
|  Record  01  |    131 171  |   5 108 468  |
|  Record  02  |  1 481 305  |     733 406  |
|  Record  03  |    294 207  |   7 431 340  |
|  Record  04  |    134 771  |   3 067 732  |
|  Record  05  |    403 466  |  38 965 193  |
|  Record  06  |    268 431  |   5 877 610  |
|  Record  07  |     12 542  |   3 617 176  |
|  Record  08  |      5 047  |   1 031 890  |
|  Record  09  |             |           0  |
|  Record  10  |    138 887  |  22 610 726  |
|  Record  11  |    148 859  |  11 029 227  |
|  Record  12  |     16 635  |   1 435 130  |
•--------------•-------------•--------------•

How to use this macro ?

First, do, from a Sel: 12×9 to Sel: 12×13, rectangular selection of the second column
Run the Macro > Rectangular_Selection_Counting macro
Open the Microsoft program calc.exe
Do a Paste operation ( Ctrl + V )

=> You should get the 3 035 321 result, which is the exact sum of the 12 numbers of the second column table, including the blank record 09

Voila !

As you can verify, the spaces, embedded in numbers, do not matter as well as pure blank zones, like for Record 09

Now, this solution made me think of a second macro, based on the marked text, obtained by use of the Mark dialogue, which I called Mark_Text_Counting :

        <Macro name="Mark_Text_Counting" Ctrl="no" Alt="no" Shift="no" Key="0">
            <Action type="2" message="0" wParam="41001" lParam="0" sParam="" />
            <Action type="0" message="2179" wParam="0" lParam="0" sParam="" />
            <Action type="2" message="0" wParam="42007" lParam="0" sParam="" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="(\R)(?!\z)|\R?\z" />
            <Action type="3" message="1625" wParam="0" lParam="2" sParam="" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="?1+:=" />
            <Action type="3" message="1702" wParam="0" lParam="640" sParam="" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="" />
            <Action type="0" message="2177" wParam="0" lParam="0" sParam="" />
            <Action type="2" message="0" wParam="41003" lParam="0" sParam="" />
        </Macro>

For an example, we’ll use the same INPUT text as above !

Now, let’s suppose that we want to mark any number, in the third column, which ends with a 0 digit. Given our INPUT text, the following regex can be applied :

MARK ^.{30}\K[\d\x20]+(?<=0\x20\x20)

So, how to use this second macro ?

First, open the Mark dialog
Uncheck all the box options
Check the Wrap around option
Select the Regular expression search mode
Click on the Mark All button ( => Mark: 5 matches in entire file message )
Then, click on the Copy Marked Text button ( IMPORTANT )
Run the Macro > Mark_Text_Counting macro
Open the Microsoft program calc.exe
Do a Paste operation ( Ctrl + V )

=> You should get the 15 775 970 result, which is the exact sum of the 5 marked records of the third column ( 7 431 340 + 5 877 610 + 1 031 890 + 0 + 1 435 130 )

Remarks :

Regarding that second macro :

Don’t forget to click the Copy Marked Text button
Don’t forget to run the appropriate macro ( Mark_Text_Counting ), right after
Like the previous macro, spaces, embedded in numbers, do not matter, but all lines, of the selected column, must contain a valid number

=> This explains why I changed, for Record 09, the blank zone, in the second column by the 0 number in the third column

An other example, with this second macro :

This time, we would like to mark any number, in the third column, which ends with a 6 digit. Given our INPUT text, the following regex can be used :

MARK ^.{30}\K[\d\x20]+(?<=6\x20\x20)

First, open the Mark dialog
Uncheck all the box options
Check the Wrap around option
Select the Regular expression search mode
Click on the Mark All button ( => Mark: 3 matches in entire file message )
Then, click on the Copy Marked Text button ( IMPORTANT )
Run the Macro > Mark_Text_Counting macro
Open the Microsoft program calc.exe
Do a Paste operation ( Ctrl + V )

=> You should get the 26 961 308 result, which is the exact sum of the 3 marked records of the third column ( 733 406 + 3 617 176 + 22 610 726 )

To end, these macros do work :

Whenener the Settings > Preferences... > Editing 2 > Multi-Editing > Enable Multi-Editing option is checked or not
Whenever the Settings > Preferences... > Editing 2 > Multi-Editing > Enable Column Selection to Multi-Editing option is checked or not
Whenever the BetterMultiSelection plugin is enabled or not and, probably, other plugins …

BR

guy038