Community
    • Login

    Automatic sum of numbers in a column

    Scheduled Pinned Locked Moved
    General Discussion
    2
    4
    146
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • guy038G
      guy038
      last edited by 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

      1 Reply Last reply Reply Quote 4
      • Alan KilbornA
        Alan Kilborn
        last edited by Alan Kilborn

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

        I find that first macro the most interesting – very interesting, indeed.

        I was curious about how it does what it does (which would have been a nice but not absolutely necessary thing for @guy038 to have described), so I dug into it and I’m changing it to be the following (note that I didn’t actually change anything in the macro that alters its execution):

                <Macro name="Col. Block Sum (paste into calc.exe)" Ctrl="no" Alt="no" Shift="no" Key="0">
            <Action type="0" message="2172" wParam="0" lParam="0" sParam="see https://community.notepad-plus-plus.org/topic/26363" />
            <Action type="0" message="2178" wParam="0" lParam="0" sParam="SCINTILLAMESSAGE.SCI_COPY" />
            <Action type="2" message="0" wParam="41001" lParam="0" sParam="MENUCOMMAND.FILE_NEW" />
            <Action type="0" message="2179" wParam="0" lParam="0" sParam="SCINTILLAMESSAGE.SCI_PASTE" />
            <Action type="2" message="0" wParam="42007" lParam="0" sParam="MENUCOMMAND.EDIT_SELECTALL" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="start defining replacement of  (\R)(?!\z)|\R?\z  with  ?1+:=" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="(\R)(?!\z)|\R?\z(?# find what )" />
            <Action type="3" message="1625" wParam="0" lParam="2" sParam="search mode: regex" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="?1+:=" />
            <Action type="3" message="1702" wParam="0" lParam="640" sParam="option: in selection only" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="execute: replace-all" />
            <Action type="0" message="2177" wParam="0" lParam="0" sParam="SCINTILLAMESSAGE.SCI_CUT" />
            <Action type="2" message="0" wParam="41003" lParam="0" sParam="MENUCOMMAND.FILE_CLOSE" />
        </Macro>

        All I did was change its name, because I thought the name @guy038 used was poor–the macro isn’t “counting” anything–and add documentation to the macro so that I could tell what each step was doing. Also I exploited the sParam data in each Action of the macro to contain a “comment” about what that lines is doing – but of course only in lines where the sParam isn’t needed for its Action.

        Things I found interesting about the macro:

        • the macro does a replacement, but after running I don’t see any changed data…
        • near the start of the macro is a File > New command – when I saw this I wondered why, after running, I don’t see a “new X” tab…or get prompted to close such a tab…

        These interesting things occur because:

        • the macro doesn’t operate on the file/tab that is active when the macro is run, so you don’t see changed data after the replacement
        • the macro removes all text from the “new X” tab before closing that tab – and with Notepad++ behavior, if you close an untitled tab that has no data in it, there is no save prompt dialog (the untitled tab simply closes)
        1 Reply Last reply Reply Quote 2
        • Alan KilbornA Alan Kilborn referenced this topic on
        • guy038G
          guy038
          last edited by guy038

          Hello, @Alan-kilborn, and All,

          Alan, your solution to place a comment for each command of a macro in the sParam parameter is very clever ! I do appreciate, also, your regex comment, added in the 1601 message ;-))

          Of course, I suppose you were a bit upset to not be able to add a comment for the 1602 message, relative to the replacement. Weren’t you ?

          But, wxith N++, no task is impossible ! Here is the solution :

                  <Macro name="Col. Block Sum (paste into calc.exe)" Ctrl="no" Alt="no" Shift="no" Key="0">
            <Action type="0" message="2172" wParam="0" lParam="0" sParam="see https://community.notepad-plus-plus.org/topic/26363" />
            <Action type="0" message="2178" wParam="0" lParam="0" sParam="SCINTILLAMESSAGE.SCI_COPY" />
            <Action type="2" message="0" wParam="41001" lParam="0" sParam="MENUCOMMAND.FILE_NEW" />
            <Action type="0" message="2179" wParam="0" lParam="0" sParam="SCINTILLAMESSAGE.SCI_PASTE" />
            <Action type="2" message="0" wParam="42007" lParam="0" sParam="MENUCOMMAND.EDIT_SELECTALL" />
            <Action type="3" message="1700" wParam="0" lParam="0" sParam="start defining replacement of  (\R)(?!\z)|\R?\z  with  ?1+:=" />
            <Action type="3" message="1601" wParam="0" lParam="0" sParam="(\R)(?!\z)|\R?\z(?# 'Find what' regex)" />
            <Action type="3" message="1625" wParam="0" lParam="2" sParam="search mode: regex" />
            <Action type="3" message="1602" wParam="0" lParam="0" sParam="(?1+:=)(?2 'Replace with' regex)" />
            <Action type="3" message="1702" wParam="0" lParam="640" sParam="option: in selection only" />
            <Action type="3" message="1701" wParam="0" lParam="1609" sParam="execute: replace-all" />
            <Action type="0" message="2177" wParam="0" lParam="0" sParam="SCINTILLAMESSAGE.SCI_CUT" />
            <Action type="2" message="0" wParam="41003" lParam="0" sParam="MENUCOMMAND.FILE_CLOSE" />
        </Macro>

          I tested it without any problem ! Of course, the reference to a comment is not so obvious, because of the lack of the # character, but it’s just fine to me !

          Best Regards,

          guy038

          Alan KilbornA 1 Reply Last reply Reply Quote 3
          • guy038G guy038 referenced this topic on
          • Alan KilbornA
            Alan Kilborn @guy038
            last edited by Alan Kilborn

            @guy038 said:

            I suppose you were a bit upset to not be able to add a comment for the 1602 message, relative to the replacement.

            <Action type=“3” message=“1602” wParam=“0” lParam=“0” sParam=“(?1+:=)(?2 ‘Replace with’ regex)” />

            Nice one!
            For readers that don’t see it, I’m talking about the addition of (?2 'Replace with' regex) as the “comment” for this action line.
            And note also, with that addition, it is needed to wrap the original ?1+:= with parentheses.

            When considering “commenting” a macro by using unused sParam values, how is one to know if sParam is needed by the macro, or you can safely stick a comment in there?

            Well, it isn’t always obvious, so, in that case it might be best to use a “null” command, which for sure doesn’t use sParam, like I did in the first line of the revised macro:

            <Action type="0" message="2172" wParam="0" lParam="0" sParam="see https://community.notepad-plus-plus.org/topic/26363" />

            Message 2172 is SCI_NULL.

            1 Reply Last reply Reply Quote 3
            • First post
              Last post
            The Community of users of the Notepad++ text editor.
            Powered by NodeBB | Contributors