Merge multiple columns into a single column on another sheet?

RRR

Hi,

I have 3 columns on the same sheet (C1, C2 and C3) and I want to Merge them into a single column "A1" on another sheet. 

Is this possible in Smartsheet and how can I proceed?

Thank you for your help.

Best,

Hollie Green

If you have the same number of rows in each column all the time and the values just change you can just set up a cell reference. If the number of rows in each column change I have not been able to find a way to move or copy specific column information into another sheet. That would definitely be a handy feature.

RRR

Hi @Hollie Green

Thanks for your help. Yes, the number of rows is the same all the time, only the number of rows is continuously updated. So, how can I handle this? Thanks in advance.

Hollie Green

Since the number of rows is the same all the time go to your second sheet and just do an =If statement that references only the cell and not the column you want to move over.

=If(Value 1 cell="","",Value 1 cell) you can't just copy and paste the formula you will have to set up the cell reference. Do the same formula in each of your cells on your new sheet referencing the cell you want to bring in. Anytime the value is updated on your reference sheet it will bring in the new value on your new sheet.

RRR

Thank you @Hollie Green for your help

best,

lupitafajardo

Hi,

How would this work if the number of rows is changing?

Thanks!

Gia Thinh

Hi @RRR ,

Simplifying your problem, I developed a solution in a single sheet as screenshots below :

Follow these steps:

1- Add a new "No" column and populate numbers in increasing as much as you want.

2- Add sheet summary fields #C1, #C2, #C3 are number of non-blank elements in each column with formula

#C1 = COUNTIF([C1]:[C1], @cell <> "")

#C2 = COUNTIF([C2]:[C2], @cell <> "")

#C3 = COUNTIF([C3]:[C3], @cell <> "")

3- Add a new "ID" column with formula : =IF(No@row <= [#C1]#, No@row, IF(No@row - [#C1]# <= [#C2]#, No@row - [#C1]#, IF(No@row - [#C1]# - [#C2]# <= [#C3]#, No@row - [#C1]# - [#C2]#)))

4- In A1 column, use this formula:

=IF(No@row <= [#C1]#, INDEX(COLLECT([C1]:[C1], [C1]:[C1], @cell <> ""), ID@row), IF(No@row - [#C1]# <= [#C2]#, INDEX(COLLECT([C2]:[C2], [C2]:[C2], @cell <> ""), ID@row), IF(No@row - [#C1]# - [#C2]# <= [#C3]#, INDEX(COLLECT([C3]:[C3], [C3]:[C3], @cell <> ""), ID@row))))

This is a general solution, regardless of whether the number of rows in the C columns is the same or not.

Try it and see if it works for you.

Jimrny

https://community.smartsheet.com/discussion/comment/424319#Comment_424319

@Gia Thinh wondering if you could help me with a similar formula. I am trying to collect Planned and Unplanned absent into one column in separate rows.

Jimrny

Disregard, I was able to figure it out with:

=IF(Helper@row <= [Planned]#,

 INDEX(COLLECT([ABSENT PLANNED]:[ABSENT PLANNED], [ABSENT PLANNED]:[ABSENT PLANNED], @cell <> ""), ID@row),

IF(Helper@row - [Planned]# <= [Unplanned]#,

 INDEX(COLLECT([ABSENT UNPLANNED]:[ABSENT UNPLANNED], [ABSENT UNPLANNED]:[ABSENT UNPLANNED], @cell <> ""), ID@row)

)

)