SOLVED

# Filtering only certain columns of a row to another worksheet

Brass Contributor

# Filtering only certain columns of a row to another worksheet

I am using Filter to move a copy of the data from one worksheet to another, but want to remove certain columns.  Filter does not seem to allow that kind of manipulation.  Is this even possible.

This is the FILTER statement I am using in the other worksheet: =FILTER('PI Package'!\$A\$3:\$FA1600,('PI Package'!\$A\$3:\$A\$1600="BUNDLE IDENTIFIER"),"NO RECORDS")

which results in this output

5 Replies

# Re: Filtering only certain columns of a row to another worksheet

There may be a more elegant solution, but here's one way. It involves writing a formula (or formulas) that draw on the header in the target page. I don't think my solution is bug free but you clearly know your way around FILTER, so I'm confident you can build on this. But, of course, come back with further questions as needed.

best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Filtering only certain columns of a row to another worksheet

See the attached workbook. It demonstrates two solutions:

• Using a second FILTER function, with an array of 0s and 1s. For each column returned by the inner FILTER function, 0 means omit this column, 1 means include it.
• Using the CHOOSECOLS function. In this formula, you have to specify the index numbers of the columns you want to include.

# Re: Filtering only certain columns of a row to another worksheet

Not only was I correct--there was a "more elegant solution"--you've shown there are at least TWO more elegant solutions. And have added to my repertoire.

# Re: Filtering only certain columns of a row to another worksheet

I like your solution too, since it lets you specify the field names (column headers) to use.

As always, Excel has multiple ways to solve a problem.

# Re: Filtering only certain columns of a row to another worksheet

Perfect @Hans Vogelaar thanks so much.

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Filtering only certain columns of a row to another worksheet

See the attached workbook. It demonstrates two solutions:

• Using a second FILTER function, with an array of 0s and 1s. For each column returned by the inner FILTER function, 0 means omit this column, 1 means include it.
• Using the CHOOSECOLS function. In this formula, you have to specify the index numbers of the columns you want to include.