Combining Columns & SUM

[kmontoya]

Hi, I have data that looks like this: 

I want to combine all the "XY COMPANY" columns & summing them at the same time. How would I be able to do that? Thanks in advance! 

Entity	XY COMPANY1	EW COMPANY	XY COMPANY	YY COMPANY2	XY COMPANY3	XY COMPANY4
Yeti	200		1	1		10
Desk		43			0
Power					1

[jrgo]

Not sure if this is what you're looking for, but this will sum up all the columns by doing some pivoting (Transpose and Crosstab) of the data. The data cleansing tool is to simply remove the number at the end of the the field names in preparation for the last pivot.

If this doesn't solve the problem, I recommend sharing what the desired output should look like based off the sample table you shared.

[kmontoya]

I've attached the end result that I am looking for. I want to combine columns while summing the values from the rows

[MilindG]

@kmontoya 

[jrgo · Accepted solution]

@kmontoya 

Here's an updated version based off the file you shared. Note that the tools in the red container may not work as intended with your actual company names. This was added match up with your expected results by keeping the name that may or may not have a number at the end. but based off what you shared, the result is an exact match to your "End Result".