How to filter data with a dynamic array, with multiple conditions and with more than one column to display

Question

This is my starting information.
I want to filter these four columns of data into a new worksheet based on if the value in the gaps column is:
Green, Amber, Red.
I also want to display the data associated with the particular row so I want each row to stay together.
I also want the result to be dynamic so that if someone comes after me and adds an unknown (potentially infinite) amount of new lines of data, the result of the filtered data can grow with it.

The function I used was FILTER.
FILTER will act as a dynamic array.
Below is my result.

The FILTER function is filtering these 4 columns based on the 3 different criteria.

The generic formula would look like.

=FILTER('SHEET1'!J:M,('SHEET1'!J:J="Green")+('SHEET1'!J:J="Amber")+('SHEET1'!J:J="Red"))

Filter acts as a dynamic array, expending as you add more data to SHEET1.

The + allow a user to add more verification terms for FILTER to check with, acting like an OR.

This took me a long time to create and get right and I thought I could post it here and save some time for others looking to do something similar.

Answer 1

Hi there

According to your requirements,

The best way to do it is using Power Query.

The following videos will help you.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

https://www.youtube.com/watch?v=dq3UvGkGhts&t=116s

https://www.youtube.com/watch?v=oVZW1gCObHY&t=374s

Answer 2

Based on the given data, here's just another idea:

=FILTER( Table, Gap <> "No Gaps" )

Answer 3

This solved my problem. I had no idea power queries existed.

Answer 4

That's a great idea. I didn't think of that.