Hi, I am getting some input data as below: Name 1Name 2Group 1Group 2ValueA 1ValueA 2ValueB 1ValueB 2XXX 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: NameGroupValueAValueBXXX1400500YYY2100200 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.

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.

Workflow

Accepted answers

alisonpitt

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

combining.yxmd

All comments

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.

0601-aaggh2003.yxmd

alisonpitt

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

combining.yxmd

flying008

Hi, @aaggh2003

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

录制_2023_06_01_08_03_51_762.gif

******

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335