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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!