CODEWITHSUNDEEP
emeditor
Heisenberg
Heisenberg

Reputation: 29

Can i group values in a column and sum their totals in emeditor?

Please how do i group a column and sum up their corresponding values in Emeditor? Similar to "Group by" in Power Query or PowerBi. For Example:

File

NAME    COST
John    5
Mike    3
Dan     5
John    2
Dan     8
John    5

Expected Outcome

NAME    COST
John    12
Mike    3
Dan     13

In this case i get the total cost of each value under "Name Column" in the file

Upvotes: 0

Views: 485

Answers (1)

Yutaka
Yutaka

Reputation: 1806

Assuming two columns are separated by a tab, you can use EmEditor to sort by the first column, combine lines, and then use a regular expression with JavaScript to replace the second column. If you want to do it fast, you can skip to Using a Macro section.

  1. Open your file.

  2. Click the Tab button on the CSV/Sort toolbar to swtich to the Tab-separated CSV mode.

EmEditor - CSV/Sort toolbar

  1. Click the Heading button on the CSV/Sort toolbar, and then select Heading 1.

  2. Select the first column, and the click the Sort A to Z button on the CSV/Sort toolbar to sort the first column.

  3. Right-Click on the Column 1 of the Headings (or select the CSV sub menu on the Edit menu), then select Combine Lines.

  4. Ensure the 1: COST check box in the Columns to inspect duplicates is set, and 2: NAME check box in the Columns to combine is set. Enter + to the String to Insert text box. Click OK.

EmEditor - Combine Lines

  1. Select the second column, press Ctrl + H to bring up the Replace dialog box, enter [\d\+]+\d to the Find text box, and \J eval("\0"); to the Replace with text box, set the In the Selection Only and Regular Expressions options, and click Replace All.

EmEditor - Replace

Using a Macro

If you want to use a macro, you can run the following macro after opening your file.

editor.ExecuteCommandByID(22529);  // TSV
editor.ExecuteCommandByID(3894);   // Heading 1
document.selection.StartOfDocument(false);
document.Sort("1>A+",eeSortBinaryComparison,"");
document.CombineLines("1","2","+","",0,"");
document.selection.SetActivePoint( eePosCellLogical, 2, 1 );
editor.ExecuteCommandByID(4461);   // Select Column
document.selection.Replace("[\\d\\+]+\\d","\\J eval(\"\\0\");",eeFindReplaceSelOnly | eeReplaceAll | eeFindReplaceRegExp,0);
document.selection.Collapse();

To run this, save this code as, for instance, GroupCol.jsee, and then select this file from Select... in the Macros menu. Finally, open your file to convert, and select Run GroupCol.jsee in the Macros menu while the file you want to convert is active.

Upvotes: 1

Related Questions