Ideas/workarounds for how to look up multiple values into multiple rows?

Hi Smartsheet Wizards.

Bear with me, as I'm not sure about the best way to ask this question…!

I have a "look up" sheet with thousands of contacts/email addresses and organization ID numbers.

I need to pull out all of the contacts for a specific set of organization ID numbers. I have this set of ID numbers in another sheet.

In the past, I've simply used a Join/Collect to pull contacts for each organization together in one cell/same row - i.e., one row for each organization with multiple contacts in a cell. That won't meet my need this time, though - as I need each contact on a separate row. In other words, there will be multiple rows (a varying number) for each organization: one row for each contact.

Is there a way to do this without manually adding specific organization IDs to a report or filter? (I've considered using the Join/Collect method, exporting to Excel, and split the text to various rows… but then I think I lose the context/other data in the row.)

Thanks for any creative ideas you may have!

Find more posts tagged with

Accepted answers

All comments

Jgorsich

Have a helper column that is just 1, 2,3, 4, etc. You can see this sheet for an easy way to set that up:

https://community.smartsheet.com/discussion/82131/is-there-a-way-to-reference-the-row-above

Then use index(collect()) to move through your collection, 1 entry at a time. If you are worried about repeats, index(distinct(collect())).

Jennifer Kurtz

Thanks - this is a cool workaround! I don't think this quite fits my need, though, as I don't know how many contacts (and thus rows) I need for each organization. I could count them, of course - but I'm not aware of how to then automate adding that number of child rows.

Jgorsich

That is a tricky problem to overcome. In similar situations I've just ensured I had enough numbers in my "Row" column to ensure I always had an excess, but that isn't always practical. If you are building off of a template and need a separate sheet for each organization, you could do the same. If the problem is that you want each organization to be listed one after the other, you can do some interesting nested index()/count()/collect() combinations - but I've still ultimately always broken down and just had a manually filled column of integers that I've ensured had an excess.

Paul Newcome

Even if you go this route, you will still need to create some sort of filter for the org. Whether that be hard-coding it into the formula or using a sheet summary field and referencing that, it is still not that much different from a report, and reports are easier to manage.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.