MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Filtering 2 separate columns

Posted by Chris Jones on May 18, 2001 12:12 PM

If anyone could help me here, it would be greatly appreciated.

I have 2 separate columns, and I want them both to display (non-blank) rows corresponding to each cell. Is there a way to filter it so that it takes into account both columns having data.

When I filter one column, it gets rid of the other column information that is needed before I can filter it.

If you don't understand what I trying to say, contact me and I'll try to explain it again

Thank you

Posted by Barrie Davidson on May 18, 2001 12:16 PM

Hi Chris, could concatenate the two values in another column and then filter on that column. That is, if your two data points are in cell A1 and B1 and your data table ends at column F, you could put the following formula in column G.
Then you filter on column G.

Hope this helps you out.


Posted by Mark W. on May 18, 2001 12:40 PM

Chris, it sounds like you want to filter your data
based on an OR condition (e.g., column A = {"X";"Y"}
OR column B = {1,2}) such that the records,
{"X",3;"Z",1;"Y",1} would be displayed.
This is supported using an Advanced AutoFilter
whereby you'd setup a Criteria using the field
names for column A ("Field1") and column B ("Field2").

It would look like...


When setting up an Excel Criteria remember that
AND-ing s accomplished with the addition of
columns and OR-ing is accomplished with the
addition of rows. The Excel Help Topic for
"Examples of advanced criteria" does an
exellent job of explaining this.

Posted by Dave Hawley on May 18, 2001 12:44 PM

If I have understood you correctly you will not be able to do this with Excels Auofilter. This is because it hides the entire row and not just the cell.

You could place the data in Column B underneath the data in Column A.

Or go to Window>New Window and filter them in seperate Widows.

OzGrid Business Applications

Posted by Mark W. on May 18, 2001 1:27 PM

Chris, trust me... directly above do the trick! : )

Posted by Mark W. on May 18, 2001 1:57 PM

Posted by Chris Jones on May 18, 2001 1:58 PM

Thanks alot, this worked