Merge multiple columns into a single column on another sheet?

RRR
RRR ✭✭
edited 10/25/22 in Formulas and Functions

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,


Answers

  • Hollie Green
    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
    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
    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
    RRR ✭✭

    Thank you @Hollie Green for your help

    best,

  • lupitafajardo
    lupitafajardo ✭✭
    edited 03/07/24

    Hi,

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

    Thanks!

  • Gia Thinh
    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.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!