INDEX, MATCH, COLLECT, CONTAINS ... oh my.

I have two worksheets. Worksheet A ("Verified Content Report") has about 100 columns. On each row are details about a specific asset. One of those details is in a column called "Owners," which contains one or more user IDs (comma-separated) for each individual "owner" of that asset.

Worksheet B has 2 columns: user IDs and the team to which that user belongs.

I need to "marry" these two worksheets into a report that will show all of the assets "owned" by users in a team. However, I cannot seem to figure out how to do this other than to know it's some combination of INDEX/MATCH and COLLECT or CONTAINS running in a column on the Verified Content worksheet that can be leveraged into a filtered report.

Thoughts? Suggestions? Coffee delivery?

Find more posts tagged with

Formulas

Accepted answers

Danielle Arteaga

I ended up solving this with the help of a colleague (who is way smarter at Smartsheet than I).

I would love to be able to share the full details of the solution here, but it ended up being so wildly complex (yet super elegant!), I don't even know where to start.

The TL;DR is:

We parsed the comma-separated data in the Verified Content Report Owners column into its own columns by formula. (Variations on this: =LEN(Owners@row) - LEN(SUBSTITUTE(Owners@row, ",", "")) + 1)
We matched the individual owners to their "groups" on the Verified Content Owners list with a basic INDEX/MATCH formula cross-referencing that sheet.
We then aggregated the "groups" for all owners of a single asset into a comma-separated cell with JOIN and an "*" delimiter
We then built a multi-select dropdown column using CHAR(10) to "translate" the comma-separated groups into the drop-down categories.
Lastly, we set up a "lookup" sheet like this:

Screen Shot 2021-03-05 at 12.35.54 PM.png

The yellow cell is a drop-down menu that uses the same category as the drop-down menu on the Verified Content Report. When you pick the "group," the formula in the Title cell runs an index/match against the new fields in the Verified Content Report that aggregate the delimited owner values:

=IFERROR(IF(hSlector@row = "ADD GROUP", INDEX({Title}, MATCH([#]@row, {ADD GROUP}, 0)), IF(hSlector@row = "CSM", INDEX({Title}, MATCH([#]@row, {CSM}, 0)), IF(hSlector@row = "CX", INDEX({Title}, MATCH([#]@row, {CX}, 0)), IF(hSlector@row = "Cloud", INDEX({Title}, MATCH([#]@row, {Cloud}, 0)), IF(hSlector@row = "Cloud & Compute", INDEX({Title}, MATCH([#]@row, {Cloud CPU}, 0)), IF(hSlector@row = "Collaboration", INDEX({Title}, MATCH([#]@row, {Collab}, 0)), IF(hSlector@row = "Competitive", INDEX({Title}, MATCH([#]@row, {Comp}, 0)), IF(hSlector@row = "ContentX", INDEX({Title}, MATCH([#]@row, {Content}, 0)), IF(hSlector@row = "Data Center Networking", INDEX({Title}, MATCH([#]@row, {DCN}, 0)), IF(hSlector@row = "Demo Publishing", INDEX({Title}, MATCH([#]@row, {DPub}, 0)), IF(hSlector@row = "Demos (dCloud)", INDEX({Title}, MATCH([#]@row, {DCloud}, 0)), IF(hSlector@row = "Enterprise Networking", INDEX({Title}, MATCH([#]@row, {ENT}, 0)), IF(hSlector@row = "GPO", INDEX({Title}, MATCH([#]@row, {GPO}, 0)), IF(hSlector@row = "GSO", INDEX({Title}, MATCH([#]@row, {GSO}, 0)), IF(hSlector@row = "Global Customer Advocacy", INDEX({Title}, MATCH([#]@row, {GCA}, 0)), IF(hSlector@row = "Growth Marketing", INDEX({Title}, MATCH([#]@row, {Growth}, 0)), IF(hSlector@row = "Meraki", INDEX({Title}, MATCH([#]@row, {Meraki}, 0)), IF(hSlector@row = "Monitoring & Analytics", INDEX({Title}, MATCH([#]@row, {MA}, 0)), IF(hSlector@row = "Partner Marketing", INDEX({Title}, MATCH([#]@row, {Partner}, 0)), IF(hSlector@row = "Proposals Publishing", INDEX({Title}, MATCH([#]@row, {Proposals}, 0)), IF(hSlector@row = "Sales", INDEX({Title}, MATCH([#]@row, {Sales}, 0)), IF(hSlector@row = "Sales Plays", INDEX({Title}, MATCH([#]@row, {Sales Plays}, 0)), IF(hSlector@row = "Security", INDEX({Title}, MATCH([#]@row, {Security}, 0)), IF(hSlector@row = "Service Provider", INDEX({Title}, MATCH([#]@row, {SP}, 0)), IF(hSlector@row = "Training Publishing", INDEX({Title}, MATCH([#]@row, {Training}, 0))))))))))))))))))))))))))), "")

I would have LOVED it if your one-line formula had worked, and I hugely appreciate you taking the time to trying to work through this. Sharing this here in case you or anyone else can leverage.

Thanks again!

All comments

Kelly Moore

Hey @Danielle Arteaga

I think this may work for you. I assumed you were creating a third column in Worksheet B for your Joined data. Once Joined, you can bring into your report.

=JOIN(COLLECT({Worksheet A Assets}, {Worksheet A ID#}, HAS(@cell, EmpID@row)), ", ")

cheers ☕

Kelly

Danielle Arteaga

Thanks, Kelly. I really appreciate the response. I get an #INCORRECT ARGUMENT SET error with this formula.

=JOIN(COLLECT({Verified Content Report Titles}, {Verified Content Report Owners}, HAS(@cell, [CEC ID]@row)), ", ")

I'll mess around with the syntax to see if I can figure out why. But, I also think this is the reverse of what I had in mind. I think I need the owner groups to show up in the Verified Content worksheet so I can pull the individual assets into the report with their owners on each line but filtered to the team.

Kelly Moore

Hey Danielle

Is it possible to see your screenshot of your datasets? The syntax is correct in the formulas. The column type that you're collecting into is a Text/Number?

In your original description you spoke of Teams. Is that the same thing as the Groups? Again, screenshots would be really useful

If your employeeID is all numbers, for grins, change one of them in both sheets to alpha-numeric and test formula.

Danielle Arteaga

I can't share the data, because it contains PII. But, here's the structure:

On the "Verified Content Report" worksheet, the "Owners" column contains user IDs as comma-separate values (if there is more than one owner). There are as many as 10 owners for each asset. The column is Text/Number and the values are unique alphanumeric strings (no special characters).

On the "Owners Report" each owner is on his/her/their own row, and the "CEC ID" column contains each owner's unique User ID. The "Group" column identifies to which team the user belongs, and a user is allocated to only one team.

I think that converting the comma-separated values on the Verified Content Report is unavoidable to get the results I'm hoping for, but I definitely appreciate the brainstorming!

Kelly Moore

Ok, here's a shot of my test data. I tried to mimic your worksheet B. I added the Joined column to gather the data

I was able to validate both HAS and CONTAINS - be sure to swap the syntax appropriately. I think it would be easy to build a report from data like this?

Danielle Arteaga

I ended up solving this with the help of a colleague (who is way smarter at Smartsheet than I).

I would love to be able to share the full details of the solution here, but it ended up being so wildly complex (yet super elegant!), I don't even know where to start.

The TL;DR is:

We parsed the comma-separated data in the Verified Content Report Owners column into its own columns by formula. (Variations on this: =LEN(Owners@row) - LEN(SUBSTITUTE(Owners@row, ",", "")) + 1)
We matched the individual owners to their "groups" on the Verified Content Owners list with a basic INDEX/MATCH formula cross-referencing that sheet.
We then aggregated the "groups" for all owners of a single asset into a comma-separated cell with JOIN and an "*" delimiter
We then built a multi-select dropdown column using CHAR(10) to "translate" the comma-separated groups into the drop-down categories.
Lastly, we set up a "lookup" sheet like this:

I would have LOVED it if your one-line formula had worked, and I hugely appreciate you taking the time to trying to work through this. Sharing this here in case you or anyone else can leverage.

Thanks again!

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.