How to append columns of data

BrianDN · 02/27/23

Hello,

I'm hoping to append multiple columns into 1 (so "stack" them).

In this case the first 5 columns would result in the Append column as shown below:

Normally I'd use Power Query in Excel but how can we achieve this in SmartSheets.

Thanks for any help with this

Lucas Rayala · 02/27/23

The main problem is that a column formula will only extend to populated rows. You could technically add another column which is just a list of number (1,2,3, etc, as many as you need). Then you could use a formula like this:

=INDEX(COLLECT([Col15]:[Column18], [Col15]:[Column18], NOT(ISBLANK(@cell))), [NumberedRowColumn]@row)

Lucas Rayala · 02/27/23

The main problem is that a column formula will only extend to populated rows. You could technically add another column which is just a list of number (1,2,3, etc, as many as you need). Then you could use a formula like this:

=INDEX(COLLECT([Col15]:[Column18], [Col15]:[Column18], NOT(ISBLANK(@cell))), [NumberedRowColumn]@row)

BrianDN · 02/27/23

Hey Lucas,

That works perfectly! And it neatly eliminates all of the spaces in my actual sheet.

Thank you thank you!!!

Lucas Rayala · 02/27/23

No problem @BrianDN.

Formulas and Functions

Formulas and Functions

How to append columns of data

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Created Date of Row Copied from Another Sheet

Need to update dates to Q1_2025, Q2_2025, Q3_2025, Q4_2025

Writing a reference formula with multiple IF/AND situations