CODEWITHSUNDEEP
excelexcel-formula
Gangula
Gangula

Reputation: 7294

Excel Filter Function - choose certain columns as output

I would like to apply a Filter function on multiple columns ranging from A:G and only have columns B,D in the output. How can I do it?

For Example =FILTER($A$1:$G$7,$K$1:$K$7=$K$1) results in a spilled array of rows that match the condition, but the output will still have 7 columns(A:G). Can I choose to only output Column B & D?

Upvotes: 23

Views: 106372

Answers (7)

HigoChumbo
HigoChumbo

Reputation: 1112

Something to keep in mind in addition to the other answers: your formulas might not update if you add new columns to the target matrix to filter that change the position of the output filtered columns. This happens, for instance if you use numeric column references (like in =CHOOSECOLS(filtered_array, {2,4})).

One way to prevent this is giving the target dataset dynamic table format and using dynamic table (named) column references together with the COLUMN formula, as COLUMN will look for the title of the column and always return the same numeric column reference even if you add new columns:

=CHOOSECOLS(FILTER(A:W,F:F="YourTargetFilterValue"),
   COLUMN([Name]),
   COLUMN([Surname]))

Upvotes: 0

Greedo
Greedo

Reputation: 5533

Try the new CHOOSECOLS function (beta channel at time of writing):

=CHOOSECOLS(filtered_array, {2,4})

Update 2024: Now widely available, see docs

Upvotes: 20

lara dumaran
lara dumaran

Reputation: 1

Yes you can, create 2 separate columns for B =FILTER($B$1:$B$7,$K$1:$K$7=$K$1) and for D =FILTER($D$1:$D$7,$K$1:$K$7=$K$1)

Upvotes: 0

AsUsual
AsUsual

Reputation: 524

By far the easiest way of doing this.

Apply range, your selection of columns in the preferred order and with the option to work your way in from the right side with -

=CHOOSECOLS(FilteredRange,2,4)

=CHOOSECOLS(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),2,4)

=CHOOSECOLS(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),4,2) 'Custom order

=CHOOSECOLS(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),-6,-4)  'From the right side

Upvotes: 2

Frank Ball
Frank Ball

Reputation: 1126

I use option #2 exclusively these days, but using a range of cells instead of the array.

=FILTER(INDEX(tblZero,SEQUENCE(ROWS(tblZero)),A2:M2),tblZero[TakenBy]=A1)

Using the [#Headers] of my Table gives me column headings as well.

=FILTER(INDEX(tblZero[#Headers],SEQUENCE(ROWS(tblZero[#Headers])),A2:M2),'Zero Dollar Review Data'!A1<>"")

I use the row directly above the column headings to hold the column numbers. This way, I simply have to enter a column number and I get both the column heading and the data. I can also use some simple formulas in my column number cells to create outputs that are custom to the criteria used. So if I enter "TBT" as my TakenBy value I can display a set of columns unique for that particular Rep., then if I enter "DXD", I can display a different set of columns.

Upvotes: 1

Gangula
Gangula

Reputation: 7294

TL;DR

  1. Option1:

    =FILTER(FILTER(A1:G7,K1:K7=K1),{0,1,0,1,0,0,0})

  2. Option2: - Reference

    =FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

  3. Option3: - Answered by Rory

    =FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)

  4. Option4: - Commented by P.b

    =FILTER(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),(COLUMN(A:G)=COLUMN(B:B))+(COLUMN(A:G)=COLUMN(D:D)))

Explanation

Option 1

You can nest the original FILTER function inside another FILTER function and specify an array of 1's and 0's mentioning which column you need and which you don't.

For Example, in the above question if I want only Column B & D, I can do this:

=FILTER(FILTER(A1:G7,K1:K7=K1),{0,1,0,1,0,0,0})

Since B & D are the 2nd & 4th columns, you need to specify a 1 at that position in the array

Similarly if you want to filter columns from C:K and only output columns C, D & G, then your formula would be:

=FILTER(FILTER(C1:K7,M1:M7=M1),{1,1,0,0,1,0,0,0,0})

Pros & Cons - Option1

  • This formula is the simplest of all and easy to understand
  • You can NOT change the order of output. You can only hide/unhide in the original sequence
  • You can apply this on a Range of multiple columns without much change

Option2

Another way to do this which is complex looking is this:
Note that this method allows you to change the order of output columns. You can refer to following site for detailed explanation on how this works.

=FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

Pros & Cons - Option2

  • This formula looks complex, but is straight-forward once you understand the logic
  • You can change the order of output columns as required
  • You can apply this on a Range of multiple columns without much change

Options 3

This is actually the answer provided by Rory

=FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)

Pros & Cons - Option3

  • This formula is complex, especially for returning a range of continuous columns
  • You need to explicitly mention each output column individually
  • You can change the order of output columns as required
  • Applying this on to output multiple continuous ranges gets tricky (For Example you cannot replace B1:B7 with B1:C7 in above formula)

Options 4

Based on comment from P.b below

=FILTER(FILTER($A$1:$G$7,$K$1:$K$7=$K$1),(COLUMN(A:G)=COLUMN(B:B))+(COLUMN(A:G)=COLUMN(D:D)))

Pros & Cons - Option4

  • This formula is the simple and somewhat similar to option 1.
  • You can NOT change the order of output. You can only hide/unhide in the original sequence
  • You can apply this on a Range of multiple columns without much change

There's a similar question that's asked in reference to Google Sheet. But Google Sheet also has the Query function which explicitly supports choosing specific columns

Upvotes: 39

Rory
Rory

Reputation: 34045

You could also use CHOOSE like this:

=FILTER(CHOOSE({1,2},B1:B7,D1:D7),$K$1:$K$7=$K$1)

This also allows you to reorder columns in the output by changing their order in the CHOOSE function.

Upvotes: 9

Related Questions