How to extract matching records

Shile

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

Genevieve P.

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:

• JOIN Function
• COLLECT Function
• Cross-sheet formulas

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

Genevieve P.

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:

• JOIN Function
• COLLECT Function
• Cross-sheet formulas

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

Shile

Excellent! It worked perfectly. I am thankful.