Combining Similar Columns and Group by/Sum by multiple columns

[aaggh2003]

Hi, I am getting some input data as below:

Name 1	Name 2	Group 1	Group 2	ValueA 1	ValueA 2	ValueB 1	ValueB 2
XXX		1		100		200
	YYY		2		100		200
	XXX		1		300		300

Note that if Name doesn't have a value than the respective columns afterwards will be null. (i.e. Name 1 is null then null in Group/ValueA/ValueB 1)

and I would like to combine columns based on the numbering while summing the values:

Name	Group	ValueA	ValueB
XXX	1	400	500
YYY	2	100	200

I've tried using Transpose and Cross Tab but run into trouble as the Value column will have both string and number formatted columns. So how can I group by combining several string columns and sum multiple columns?

Thanks in advanced for any help and please let me know if the question is unclear.

[Qiu]

@aaggh2003 
Just as you mentioned, there are both string and number formatted columns, so we have to seperate them and I use a Regex filter.
And I use record ID the keep tracking the records before I can join them togher.

In your real data, it can be much complex for removing the postfix in column names.
Hope it helps.

[alisonpitt · Accepted solution]

You can do this without regex, too, since the column names are nicely formatted. It is important to use a RecordID though, I agree with Qiu :)

[flying008]

Hi, @aaggh2003 

Another way for you , maybe this is a dynamic solution:

******

If can help you get your want, please mark it as a solution and give a like for more share. 

Otherwise please upload sample data for get advanced way.

[aaggh2003]

I added a new field in the formula by assigning an unique group_id for each _X, and uses both record_id and group_id during the cross tab tool, all good now!

Thanks everyone!

-------------------

I realized I misled you all by showing an input that did not have data in _1 and _2 at the same time, which is actually possible and with the cross tab tool having "First" value makes those _2 values disappear.

See attached for the sample record, which got values in all columns, and the output I am looking for should be:

Name	Group	Sum_ValueA	Sum_ValueB
250	Other	0.01	5772.57
104	Other	12500	237.45

I tried creating multiple transpose tools while adding numbers to create new record id and use Union tool at the end but I actually have 10 columns for each header, not sure if this is the best way to do it.

Apology for making the confusion.