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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Excellent! It worked perfectly. I am thankful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!