Pulling Multiple Rows of Data into 1 Cell

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!

Find more posts tagged with

Sheets

Formulas

functionality

Cell linking

Accepted answers

Paul Newcome

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

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

All comments

L_123

You can just use join(collect())

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

Paul Newcome

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

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

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

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

MelodyD

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

@Paul Newcome is absolutely brilliant

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.