How to concatenate several columns

[Virgie0113]

Hi,

How will I concatenate col c-m, some cells does not have a number.

Thank you,

Virginia

[Luke_C · Accepted solution]

Hi @Virgie0113 

I would suggest transposing the data, filtering out empty/null values, then crosstabbing it back to the original layout. That will get you in a much better spot. You may need to use multi-row formulas to fill down columns C, D,E and H since G has multiple options. 

[cmcclellan]

Because you mention hierarchy, I'd be tempted to run a few multi-row formulas to "fill down" so that you have the parent value in each row.  Then when you concatenate you will have all the values.

[HomesickSurfer]

Hi @Virgie0113 

I'm on mobile and unable test...since your values are all numeric, perhaps you can use the Data Cleanse tool to replace nulls with 0s then use the Summary tool for each field?

[Virgie0113]

Thank you.  this is helpful.

[chukleswk]

You can use something like this to bring all of the values into a single row: 

 

[Virgie0113]

Thank you all! I used transposing the data.