How to extract matching records

Hi, I need help with the sample data below. I want to extract records for the "Team name, Location and ID" into another sheet, using the the "Date column" to match "Drink type" that is alike.

For example:

I want to retrieve "Team name, Location and ID" of all "soda" sold on Jan 7, 2021

I want to retrieve "Team name, Location and ID" of all "water" sold on Jan 7, 2021

Same goes for other dates


Thanks

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Shile

    There are a couple ways to do this depending on how you want your data to be displayed in the destination sheet.

    For example, if you have the exact same set-up in the other sheet with each of these columns, you could use a JOIN(COLLECT( formula in each column to Join together all the data from the Team Name column into this other Team Name column if the date and drink matches.

    Ex:

    =JOIN(COLLECT({Team Name Source Sheet}, {Date Source Sheet}, Date@row, {Drink Type Source Sheet}, [Drink Type]@row), ", ")

    Then you would set up a second formula for the Location column:

    =JOIN(COLLECT({Location Source Sheet}, {Date Source Sheet}, Date@row, {Drink Type Source Sheet}, [Drink Type]@row), ", ")

    Here are some Help Articles that may help as you build this:

    Another option would be to create a Report where you Filter the Report by the Date, then Group it by the Drink Type to see the information parsed out into multiple rows.

    Let me know if either of this has helped!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Shile

    There are a couple ways to do this depending on how you want your data to be displayed in the destination sheet.

    For example, if you have the exact same set-up in the other sheet with each of these columns, you could use a JOIN(COLLECT( formula in each column to Join together all the data from the Team Name column into this other Team Name column if the date and drink matches.

    Ex:

    =JOIN(COLLECT({Team Name Source Sheet}, {Date Source Sheet}, Date@row, {Drink Type Source Sheet}, [Drink Type]@row), ", ")

    Then you would set up a second formula for the Location column:

    =JOIN(COLLECT({Location Source Sheet}, {Date Source Sheet}, Date@row, {Drink Type Source Sheet}, [Drink Type]@row), ", ")

    Here are some Help Articles that may help as you build this:

    Another option would be to create a Report where you Filter the Report by the Date, then Group it by the Drink Type to see the information parsed out into multiple rows.

    Let me know if either of this has helped!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Excellent! It worked perfectly. I am thankful.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!