Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted

## How to merge rows and sum them?

How can we merge rows of a table as shown below:

 X Y STATE_1 1 STATE_1 6 896 STATE_1 2 STATE_2 7 STATE_1 3 STATE_3 22 STATE_2 4 STATE_4 861 STATE_2 3 STATE_2 94 94 STATE_3 2 STATE_1 37 412 STATE_3 5 STATE_2 4 STATE_3 10 STATE_3 18 STATE_3 5 STATE_4 353 STATE_4 576 STATE_2 94 94 STATE_4 33 STATE_1 37 37 STATE_4 25 STATE_2 4 4 STATE_4 97 STATE_4 54 STATE_4 76 STATE_2 94 STATE_1 37 STATE_2 4

1. First group the continuous occurrence of rows

2. Calculate the sum of sequential occurrence of STATE_1 to STATE_4

Pfa the sample table for your reference

Highlighted

## Re: How to merge rows and sum them?

I solved the problem using 2 new formula columns The Group column formula just creates a grouping number when :Big Value is detected

``````If( Row() == 1, theGroup = 0 );
If( Is Missing( :Big Value ) == 0,
theGroup
++);
theGroup;``````

The Sum column formula just looks one row ahead, and if it detects a non missing value for :Big Value, it adds up the sums for both :Value and :Big Value for the current Group and then adds them together

`If( Is Missing( :Big Value[Row() + 1] ) == 0,Sum( Col Sum( :Value, :Group ), Col Sum( :Big Value, :Group ) ),If( Row() == N Rows( Current Data Table() ) & Is Missing( :Big Value ) == 0,Sum( :Value, :Big Value )))`

Attached is the data table I used

Jim
Article Labels

There are no labels assigned to this post.