How to merge rows and sum them?

jojmp — Fri, 09 Jun 2023 23:26:05 GMT

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

Re: How to merge rows and sum them?

txnelson — Thu, 23 Jan 2020 21:47:32 GMT

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

topic How to merge rows and sum them? in Discussions

How to merge rows and sum them?

Re: How to merge rows and sum them?