Dynamic array filter formula

On the attached spreadsheet, I am trying to filter the "Database" worksheet by using criteria held in the "Summary" worksheet cell B1. The criteria range is "Database!B:B".

I want the results to return into the "Summary" worksheet. I have populated the results I'm trying achieve and highlighted them yellow.

I know I can simply use a column filter but I need to have the results return into a separate worksheet.

Hopefully all that makes sense.
Thanks in advance.

Originally Posted by JohnTopley

In A5

=IFERROR(INDEX(Database!$A$2:$A$100,SMALL(IF(Database!$B$2:$B$100=$B$1,ROW(Database!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

in B5

=IFERROR(INDEX(Database!C$2:C$100,SMALL(IF(Database!$B$2:$B$100=$B$1,ROW(Database!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy across and down

One more thing with the attached spreadsheet. Can I add an additional criteria so that it only returns results for Drivers that have hours data.

i.e. I can have a fixed driver list on the "Derby Database" worksheet but some drivers may not have any data, I don't want to see these on the "Derby Summary" worksheet.

some drivers may not have any data

.. define precisely what this means in terms of data (completely blank row? or preferably a single column entry)

and add sample to your file

Yea if there's any data in the columns C to J, then populate the summary with the whole row of data.

If there's no data at all in columns C to J, then return nothing.

I used Helper in Column K of "Derby Database":

=COUNT(C2:J2)

copy dowm

in "Derby Summary"

in A6

=IFERROR(INDEX('Derby database'!A$2:A$100,SMALL(IF(('Derby database'!$B$2:$B$100=$B$2)*('Derby database'!$K$2:$K$100<>0),ROW('Derby database'!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in B6

=IFERROR(INDEX('Derby database'!C$2:C$100,SMALL(IF(('Derby database'!$B$2:$B$100=$B$2)*('Derby database'!$K$2:$K$100<>0),ROW('Derby database'!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

copy across

.

Originally Posted by JohnTopley

I used Helper in Column K of "Derby Database":

=COUNT(C2:J2)

copy dowm

in "Derby Summary"

in A6

=IFERROR(INDEX('Derby database'!A$2:A$100,SMALL(IF(('Derby database'!$B$2:$B$100=$B$2)*('Derby database'!$K$2:$K$100<>0),ROW('Derby database'!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in B6

=IFERROR(INDEX('Derby database'!C$2:C$100,SMALL(IF(('Derby database'!$B$2:$B$100=$B$2)*('Derby database'!$K$2:$K$100<>0),ROW('Derby database'!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

copy across

.

JohnTopley thank you once again! Much appreciated.

For future reference:

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.