Pulling Multiple Rows of Data into 1 Cell

LFaLFa
edited 05/18/20 in Using Smartsheet
05/18/20 Edited 05/18/20
Accepted

Hello,

Does anyone know how to pull multiple rows of data into one cell?

I'd like to build a cell that look-up the date "05/20/20" and brings all the values into one cell.

I'm thinking vlookup:


=VLOOKUP("5/20/20", {Test Range 1}, 2, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 3, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 4, false) + " - " + VLOOKUP("5/20/20", {Test Range 1}, 5, false)


But that gives me only the first line:

"02:30 PM - 02:50 PM - Cats in love - Wiskeys"


Any way to also pull the data for so that the cell can read:


"

02:30 PM - 02:50 PM - Cats in love - Wiskeys

02:50 PM - 03:10 PM - Cat nip - Meowton

03:10 PM - 04:00 PM - Q&A - All

"


?

Thank you in advance!


L

Best Answers

  • LFaLFa
    Accepted Answer

    thank you!


    I ended up doing a concatenation of cells horizontally with


    =JOIN([Time Start]1:[Session Speaker]1, " - ")


    and then doing a helper

    "

    -

    -

    "


    and a


    =JOIN(COLLECT({Test Range 1}, {Test Range 2}, [date]1), SUBSTITUTE(Helper$1, "-", ""))

Answers

  • L_123L_123 ✭✭✭✭✭

    You can just use join(collect())


  • LFaLFa
    Accepted Answer

    thank you!


    I ended up doing a concatenation of cells horizontally with


    =JOIN([Time Start]1:[Session Speaker]1, " - ")


    and then doing a helper

    "

    -

    -

    "


    and a


    =JOIN(COLLECT({Test Range 1}, {Test Range 2}, [date]1), SUBSTITUTE(Helper$1, "-", ""))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Yes. I was just going off of what you already had instead of the JOIN function in the helper column.

    CHAR(10) is actually a line break, so you could use that instead of using the SUBSTITUTE and the helper.


    Happy to help and glad you were able to get it working. 👍️

    thinkspi.com

  • Oh!!!!!!!!!! @Paul Newcome !!!!! You made my day!! thank you!!!!!!

Sign In or Register to comment.