Join(collect()) - the collect column has multiple values.
I want to filter what is collected (in the collect range, each cell may have multiple years : e.g. 2020,2021,2022,2023,2024).
2 criteria are separate columns. Criteria range 1 vs criteria 1 (wood species), criteria range 2 vs criteria 2 (specified in product).
Both criteria 1 and criteria 2 match, so I get all the years collected from the cell.
Filter: I want to only collect the year if if is 2022 or less. I tried to use the collect range as a criteria range 3 and criteria 3 < = 2022, but I still get all 5 years.
Thanks
Answers
-
Are you able to provide some screenshots for context?
-
First Sheet. Stock years are years available in inventory.
2nd sheet. DOM is year manufactured. I was trying to find what possible years of stock could have been used to be shown in the 4th column. I used =join(collect({1st sheet-stock years}, criteria range 1 {1st sheet -Timber}, criteria 1 *not shown*, criteria Range 2 *not shown*, criteria 2 *not shown*)). But I kept getting all the years in the the Stock years cell. 2021, 2022, 2023, 2024
So I tried
=Join(collect({1st sheet-stock years}, criteria range 1 {1st sheet -Timber}, criteria 1 *not shown*, criteria Range 2 *not shown*, criteria 2 *not shown*, {1st sheet-stock years} ,< =[DOM (year)])). Still same. 2021, 2022, 2023, 2024
I am looking to get 2021, 2022, 2023.
I did find a work around in this situation by adding rows and years, changing the collect formula in the 2nd sheet to collect the first column in sheet 1 and changing the 3rd criteria to {1st sheet-stock years} ,contains([DOM (year),@cell)].
But I have 100's of single rows that would all need to add individual rows for each year.
I seems from research in the community that you cannot filter a multiple select cell when it is used as the 'collect' part of the collect function. I have other sheets also where I want to collect from a multi select column and filter the values to a criteria.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!