Formula Needed For Combining Rows with Exact Values in Separate Sheet

Hello Smartsheet Community,

I have a compliance tracker sheet that captures employee's HIPAA and FEMA certifications. I have been tasked with creating a dynamic view for their immediate supervisors. I have two separate views for FEMA and then HIPPA. However, I also have employees who have submitted separate reports for each individual FEMA module. And I would like in the dynamic view for these individuals to only appear once, however I am seeing an individual appear 5x (even if they have only 4 FEMA rows), there HIPPA row will appear too. So is there a formula or some type of way (whether creating a separate sheet/or running the view through a report) for me to combine these separate cells/rows into one row when the same individual submits multiple forms.

Thank you in advance,

Alan

Public Health

Find more posts tagged with

Formulas

Reports

Accepted answers

All comments

Andrée Starå

Hi @AlanBal

I hope you're well and safe!

Yes, if there is something unique for each submitter that we could use to "connect" the records, we could use cross-sheet formulas and either VLOOKUP or INDEX/MATCH to collect it together in a so-called helper sheet.

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

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

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

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

AlanBal

https://community.smartsheet.com/discussion/comment/323263#Comment_323263

Hello Andree,

I figured I needed to follow a similar process, but the part where I am stuck on is that I need individual records from the same individual to be joined together under certain conditions. So is there a way or how would I create a dynamic view with set conditions? Ex: Alan has submitted 3 separate forms, 2 for FEMA and 1 for HIPPA. I need all the FEMA rows to be be joined in the dynamic view or a separate sheet so that each individual appears only once in the dynamic view. However, I have noticed that when I filter just for rows with FEMA in the dynamic view, an individual's HIPPA row will also appear.

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.