Do you find it frustrating that you can easily shift or move a column in a Filter formula when dragging across, but not when dragging down in Google Sheets? Don’t worry, my friend. I’ve got a secret trick up my sleeve that will solve this problem!
Imagine you have a table with student names in the first column and their exam results in the adjacent columns. The order goes like this: “Student Name,” “Maths,” “Chemistry,” “Physics,” and “General Knowledge.” Your goal is to filter the poor performers in each subject, starting with maths. Easy peasy, right?
Normally, we would use a Filter formula to get the names of students who scored less than 20 out of 50 in maths. But when we drag the formula down, we want the names of the poor performers in chemistry to appear in the next row. How do we achieve this magic in Google Sheets?
The only way to make this happen is by shifting the criterion column from maths to chemistry when dragging down. Let me show you how it’s done, step by step.
How to Shift a Column in a Filter Formula When Dragging It Down
Let’s assume that our data is in cells A1:E10, where A2:A10 contains the names and B2:E10 contains the marks in different subjects.
First, we can use the following FILTER formula to get the names of students who scored less than 20 in maths.
=ifna(filter($A$2:$A$10,B2:B10<20,B2:B10<>""))Easy so far, right? But here’s where the magic happens. Instead of copying and pasting the formula to the right to get the names of students in chemistry, we’re going to make a few changes.
First, let’s transpose the output using the TRANSPOSE function. Here’s the modified formula:
=transpose(ifna(filter($A$2:$A$10,B2:B10<20,B2:B10<>"")))But we’re not done yet. We need to replace B2:B10 with index($B$2:$E$10,0,row(A1)) to shift the columns in the Filter formula when we drag it down. Are you still with me?
Finally, here’s the ultimate formula that will blow your mind:
=transpose(ifna(filter($A$2:$A$10,index($B$2:$E$10,0,row(A1))<20,index($B$2:$E$10,0,row(A1))<>"")))Voila! You can now filter the poor performers in each subject by dragging this formula down. Pure magic!
But wait, there’s more! I promised you a bonus, didn’t I? If you want to automate the copy-pasting down process without all this dragging, I’ve got just the thing for you.
Automate Copy-Pasting Down Using the Bycol Lambda Function
With the BYCOL function, we can automate the shift column in a filter formula without the need to drag it down manually. Isn’t that amazing?
All you have to do is empty the column range G2:J and insert the following Bycol formula in cell G2:
=transpose(bycol(B2:E10,lambda(c,ifna(filter($A$2:$A$10,c<20,c<>"")))))This formula is pure genius! It groups the array B2:E10 by columns using the Bycol lambda function and applies a LAMBDA function to each column. It then transposes the output, and voila! You have the names of the poor performers in each subject, row by row.
Now, my friend, you’re armed with the knowledge to shift columns in a Filter formula when dragging down. Say goodbye to the limitations and embrace the magic of Google Sheets!
If you want to learn more tips and tricks like this, head over to Crawlan.com and unlock the full potential of Google Sheets. Happy filtering!
