Join(collect()) - the collect column has multiple values.

C Ewers

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

Paul Newcome

Are you able to provide some screenshots for context?

C Ewers

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.