Help finding and collecting matches

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

Answers

  • Brett Wyrick
    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)

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • 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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!