Help finding and collecting matches

Andrew S

Hi,

I have an task I believe Smartsheet can do but I've had no luck figuring it out. I believe the solution lies with formulas of vlookup, match, index and possibly collect but for the life of me I'm stumped.

To simplify what I'm trying to do - in essence I have a Smartsheet grid filled with data on what foods people like. Each person has their own row. The first column is filled with the person's name, columns 2 and up have list a food item that person can each. Everyone has a different number of foods they like. For example:

Amy Banana Tomato

Ben Apple Orange Banana

Chris Tomato

Debbie Orange Apple Banana

And I would like to turn it into a list where each food has its own row, with the first column containing the one type of food, and the second column having the names of everyone who eats that food. So the example above would turn into:

Apple Ben, Debbie

Banana Amy, Ben, Debbie

Orange Ben, Debbie

Tomato Amy, Chris

My sheet has 100+ rows, and each person can have over 100 columns. Needless to say I'm really keen to find a way to do this as doing it manually is not an option.

Thanks

Andrew

Brett Wyrick

Hey Andrew.

What you're wanting to do can be done, but not without hassle of doing something manually. This is something you could do in Excel very quickly through a Pivot Table. Honestly, this is the route I'd recommend. For a table of people's names and their favorite foods, I think Excel is your huckleberry. Smartsheet is great for tracking projects and maybe a little bit of data, but when you're wanting to do nitty-gritty data analysis like this, I'm not sure it's the best tool for your use case.

But, when there's a will there's a way, right? So if this data HAS to be in Smartsheet...

Option 1:

Your org gets a license for Smartsheet Pivot: Get Started with Smartsheet Pivot App | Smartsheet Learning Center. I have not used Smartsheet Pivot before, but this appears like it could do what you're asking.

Option 2:

An epic API development. I'm not sure what all would be involved in it exactly, but from the top of my head, you'd need something that would scrape the data from the sheet (using the GET SHEET action and ?include=columns). The development would need to use the sheet data + put the existing column data in an array to turn around and do a hundred HTTP POST 'Apply to each' actions to add each new food Column. Then one epic "Add a row" HTTP POST for all the data to get imported. Also you'd have to build an array of usernames per favorite food and keep track of that for it to work. Sounds like the data is already there, so it could happen. Not sure how savvy you are in this regard, but it is something I could do. (but I do charge for this sort of thing)

Andrew S

Hi Brett,

Unfortunately I have to use Smartsheet, and my Smartsheet skills are rather rudimentary.

Out of curiosity, how much would you charge?

Thanks

Andrew

Andrée Starå

Hi @Andrew S

I hope you're well and safe!

To add to Brett's excellent advice/answer.

Another option could be to structure a solution with another sheet and cross-sheet formulas.

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

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!

Andrew S

https://community.smartsheet.com/discussion/comment/300827#Comment_300827

Thanks. That would be great. I have emailed you.