# Merge multiple columns into a single column on another sheet?

✭✭
edited 10/25/22

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?

Best,

Tags:

• ✭✭✭✭✭✭

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.

• ✭✭

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.

• ✭✭✭✭✭✭

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.

• ✭✭

Thank you @Hollie Green for your help

best,

• edited 03/07/24

Hi,

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

Thanks!

• ✭✭✭✭✭✭

Hi @RRR ,

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

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 - Smartsheet Solution Partner.

• ✭✭✭

@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.

• ✭✭✭
edited 06/26/24

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)
)
)
```

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!