How to merge rows and sum them?

jojmp

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

test.xlsx

txnelson

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

doublesum.jmp