How to filter multiple columns with single fliter drop-down arrow

**dinosaur143** · 08-29-2013, 12:58 PM

Hi, I'll post example pictures to explain better, but I'm getting very frustrated... It seems like such a simple thing to do but I cannot figure it out.
I want to be able to filter for specific values in all the columns underneath a merged cell group. I used a simplified example of just "Letters", because my data is proprietary and large, but the format is the same.

capture1.PNG

When I use the filter, it only filters the first column. As shown below, it doesn't even recognize that there are 'c' and 'd' values.

Capture3.jpg

And when I do filter for just 'a' values. It only recognizes the first column, and column C isn't acknowledged for having 'a' values in it.

Capture2.PNG

So what I want to happen is with that single filter, I can filter the data for any value contained in all the columns (in this example columns A - D), and it will find that value in any row. In this example, filtering for 'a' should have given me all the rows back because they each had an 'a' value in them.

Thanks for any help you can give me.

**mikeTRON** · 08-29-2013, 02:27 PM

First off welcome to the forum.

Second off, it is proper annoying to open three pictures of a data set

so next time include a sample workbook (which is also MUCH more likely to draw help from the community).

Third, you could concatenate all of the fields into one field to the left or right then filter on the concatenated field.

But If it were me I would try to restructure the data to make it easier to work with. I prefer to have things in ONE column each so you can do everything much easier, avoiding the more complex logic needed.

**mikeTRON** · 08-29-2013, 02:29 PM

On a side note, I think it might be possible to use advanced filters with "contains" A.

http://office.microsoft.com/en-us/ex...005200178.aspx
http://www.mrexcel.com/forum/excel-q...same-time.html

**dinosaur143** · 08-29-2013, 03:13 PM

Hah, yeah sorry about that. When I was trying to insert just an image it kept giving me errors, so I had to put attachments in... about to lose my mind here. I'll attach a workbook so you have a better idea here.

So it's not possible for me to restructure the data. I can't put it all in to one column. If you open the example workbook, you'll see I have names associated with the data.
For example, I need that filter in the Letters box to search the sheet for any person that is associated with value 'a'. I can't just filter an individual column A or B..etc because that would leave out people that have 'a' in another column.

If I'm understanding right, concentrating all the fields into one column to the side I don't think would achieve what I'm trying to do.

excelHelpExample.xlsx

**newdoverman** · 08-29-2013, 04:43 PM

Maybe something like this will work for you.

Sometimes it pays to use Format Cells, Alignment, Horizontal, Center across selection instead of Merge and Center.

**mikeTRON** · 08-29-2013, 07:03 PM

Haha, well does Newdoverman's solution help you?

This is what I was talking about as a simple simple solution:

excelHelpExample-W_concat.xlsx

**newdoverman** · 08-29-2013, 07:10 PM

mikeTRON, concatenation would certainly be easy but the asker said something about not being able to restructure the data so, I really don't know what would be a suitable solution. The question to me isn't exactly clear.

**mikeTRON** · 08-29-2013, 07:11 PM

Yeah I saw that, but I wanted to show him anyways since it was a super simple solution.

Can you do this type of search with an advanced filter? I have only used it once and my google searches are not coming back with the results I was hoping for.

**newdoverman** · 08-29-2013, 07:20 PM

One can do a lot with the Advanced Filter but I haven't used it enough to know for sure. Logical criteria are used in the Criteria range so I suppose that anything might be possible.

It's a good thought. Maybe someone here knows AF well enough to give a definitive answer.

**Roger Govier** · 08-29-2013, 07:29 PM

Hi

mikeTRON is absolutely correct.
If you want to consider all 4 columns at the same time, then a Concatenation column as he has shown will solve your problem.
With Autofilter, just set the criterion to "contains a" and you will have all of the names selected that have an "a" in any of the 4 columns.

**mikeTRON** · 08-29-2013, 07:49 PM

It is a pretty neat feature:

http://blogs.office.com/b/microsoft-...d-filters.aspx
http://chandoo.org/wp/2011/10/10/how...anced-filters/
http://www.contextures.com/xladvfilter01.html
http://www.contextures.com/xladvfilter02.html#List

**dinosaur143** · 08-30-2013, 01:01 PM

I appreciate the responses. Yeah my Google searches came up with nothing either, surprised me.
Your solutions are helpful, still not exactly what I'm trying to do. The concatenated column could work, but I'm not sure if I'll be able to add that to the worksheet.
I'll look into advanced filtering more and do some research.

Thanks again.

How to filter multiple columns with single fliter drop-down arrow

LinkBack

Thread Tools

Rate This Thread

Display

How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Re: How to filter multiple columns with single fliter drop-down arrow

Thread Information

Users Browsing this Thread

Similar Threads

Transpose copy paste multiple columns of a single row to a multiple rows in a single colum

Multiple drop dependant drop downs in single cell

Generate single drop down from items in four columns

[SOLVED] How to change double arrow to single arrow (older version)

[SOLVED] How can I change the color of the drop down arrow on a filter?

Bookmarks

Bookmarks

Posting Permissions