Jul 23 2022 06:10 PM
I have programmed a formula using the Filter function to bring a short list of items from a large spread sheet and I managed to make it work. But I need to somehow limit the list to a set of rows instead of having it to bring a long list. I would highly appreciated your help.
In the following sample the formula brings a list of 10 rows, and I want to limit it at 5 Max
Jul 23 2022 09:11 PM
@Eduardo_Mata Try it like this:
=INDEX(<your current formula>,SEQUENCE(5),SEQUENCE(,6))
where you replace <your current formula> with what you have now, excluding the =-sign.
If you happen to be an Insider (beta) user, you can use:
=TAKE(<your current formula>,5)
The first SEQUENCE is to keep the top 5 rows, The next SEQUENCE is to keep all 6 columns from the data set.
Jul 23 2022 11:56 PM
If you do not have access to TAKE, you could always try a second filter applied to the sorted dataset
= LET(
selected, SORT(FILTER(Table1,Table1[crit]=crit),6,-1),
k, SEQUENCE(ROWS(selected)),
FILTER(selected,k<=5)
)
Jul 25 2022 10:06 AM
I highly appreciate your time and help. I was able to bring a 5 rows of information utlizing the formula that you kindly shared with me.
Thank you so much. Have a great day.
Jul 25 2022 10:07 AM