Merging Rows In a Worksheet with Unique Identifier

I have a worksheet with 3 forms (on the same sheet) that is collecting information and populating onto one master sheet where I currently have information related to each form. The problem is that the 3 forms, leave me with 4 separate rows (original entry info and data from the 3 forms) that all share 1 unique identifier, and I want to have 1 row. I control the identifier with a Data Shuttle dynamic dropdown. All forms are input by different people but the dropdown assures the unique identifier.

Each form fills out different columns (associated with a candidate) on the master sheet. Can I use a function match to combine the rows that share that unique identifier (name from dynamic dropdown)? If not, how can I gather all the information into one row?

Find more posts tagged with

Accepted answers

All comments

Monique Odom Stearn

Hello @Paula M.!

The form functionality in Smartsheet will always enter the information submitted in a new row. Forms are basically meant to be an alternative way to enter a new row on a sheet.

In this instance, I would consider using the Update Request functionality. You could set up 3 different update requests tied to the specific columns for each of the 3 individuals that need to enter the columns, and then set a trigger to automatically send the update requests via email after a new original entry.

Paula M.

Thanks Monique - because of the nature of how the info needs to be collected, Request Update is not an option. Is there a formula or any other ideas you might have? I see references to JOIN COLLECT formulas but not sure how/if that would work.

Georgie

Hi @Paula M.,

In each column in your master sheet that you want to pull data into, you could use formula combinations such as INDEX and MATCH, INDEX and COLLECT, or JOIN and COLLECT - take a look here for more information about what these different combinations are used for and how to create them: Formula combinations for cross sheet references. You may also wish to check out this article for more information on creating cross-sheet references.

If you’d like additional guidance on creating these for your specific sheets, could you share some screenshots (with any sensitive data hidden/removed) of the master sheet and the sheets you’ll be referencing, and a description of the data you want to pull into the master sheet?

Hope that helps!

Georgie

Paula M.

Thank you both for your help! Georgie I will try the formulas to consolidate the data. Monique I also have a situation where your solution is a great option that I had not considered!

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.