Pulling Multiple Rows of Data into 1 Cell

LFa · 05/18/20

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

Paul Newcome · 05/19/20

I would suggest using a helper column on the source sheet that pulls the column data together in each row using something along the lines of...

=[Time Start]@row + " - " + [Time End]@row + " - " + Title@row + " - " + Speaker@row

Then in the sheet you want to combine these rows into the formula would look something like this:

=JOIN(COLLECT({Other Sheet Helper Column}, {Other Sheet Date Column}, DATE(2020, 05, 20)), CHAR(10))

LFa · 05/19/20

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, "-", ""))

L_123 · 05/18/20

You can just use join(collect())

https://help.smartsheet.com/function/collect

Paul Newcome · 05/19/20

I would suggest using a helper column on the source sheet that pulls the column data together in each row using something along the lines of...

=[Time Start]@row + " - " + [Time End]@row + " - " + Title@row + " - " + Speaker@row

Then in the sheet you want to combine these rows into the formula would look something like this:

=JOIN(COLLECT({Other Sheet Helper Column}, {Other Sheet Date Column}, DATE(2020, 05, 20)), CHAR(10))

LFa · 05/19/20

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 Newcome · 05/19/20

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. 👍️

LFa · 05/19/20

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

MelodyD · 10/30/21

https://community.smartsheet.com/discussion/comment/250702#Comment_250702

@Paul Newcome is absolutely brilliant

Pulling Multiple Rows of Data into 1 Cell

Best Answers

Answers

Categories