Issue with Filtering

[fv1399]

Hello forum,

Basically I have a questionnaire (Button) that allows me to filter the room and it shows everyone in the room. Is there a way that once I filter the room and then filter the people in the room without loosing the room filter.

Example:
If I filter by Room 2, I will see Designer, Engineer and Doctor.
Then if I filter by Doctor, I will see doctors from all rooms. What I need is keeping the filter of room 2 and filtering doctors out of room 2 only. I want to be able to filter both of them at the same time. Please help!!

 

[DanteAmor]

Welcome to the board!

It is not possible to do it at the same time.
You could run a macro, the macro filters through one column and then through the other column, but even for the macro it would have to be done in 2 steps, you would see only one step.
The other option is to create an auxiliary column, concatenate the 2 columns and filter in that auxiliary column.

 

[lrobbo314]

How about this.

Sub FILTERROOM()
With Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=1, Criteria1:="2"
    .AutoFilter Field:=2, Criteria1:="Doctor"
End With
End Sub

 

[fv1399]

Welcome to the board!

It is not possible to do it at the same time.
You could run a macro, the macro filters through one column and then through the other column, but even for the macro it would have to be done in 2 steps, you would see only one step.
The other option is to create an auxiliary column, concatenate the 2 columns and filter in that auxiliary column.

I didn't understand the auxiliary column part. Can you please elaborate it?

 

[fv1399]

How about this.

Sub FILTERROOM()
With Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=1, Criteria1:="2"
    .AutoFilter Field:=2, Criteria1:="Doctor"
End With
End Sub

Hi

Sub FILTERROOM()

With Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)

    .AutoFilter Field:=1, Criteria1:="2"

    .AutoFilter Field:=2, Criteria1:="Doctor"

End With

End Sub

How do I generalize this code for the entire sheet.
How do I say if I filter by room number and if I filter by who is in the room, how can I lock my 1st filter (Filter by room number)??

 

[Pumperkin]

Hello forum,

Basically I have a questionnaire (Button) that allows me to filter the room and it shows everyone in the room. Is there a way that once I filter the room and then filter the people in the room without loosing the room filter.

Example:
If I filter by Room 2, I will see Designer, Engineer and Doctor.
Then if I filter by Doctor, I will see doctors from all rooms. What I need is keeping the filter of room 2 and filtering doctors out of room 2 only. I want to be able to filter both of them at the same time. Please help!!
View attachment 2361

Hi,

Not sure if I understand you correctly but I think I use exactly the same thing on my spreadsheet for phone make, colour and network.

First highlight the row with your headings. Then click filter. Then you can drop down so that Room # is ticked "2". Once you have filtered to this, you can then click the arrow by "WHO" and click Doctor. This will then just keep the data for doctors in room 2 (I presume this is what you're after).

Apologies if I've misunderstood!

 

[fv1399]

Hi,

Not sure if I understand you correctly but I think I use exactly the same thing on my spreadsheet for phone make, colour and network.

First highlight the row with your headings. Then click filter. Then you can drop down so that Room # is ticked "2". Once you have filtered to this, you can then click the arrow by "WHO" and click Doctor. This will then just keep the data for doctors in room 2 (I presume this is what you're after).

Apologies if I've misunderstood!

I may have explained the issue incorrectly.

I have a questionnare that lets me choose between rooms. If I chose room 1 , it will show me all the doctors and engineers in room 1.
I want to be able to filter between doctors, engineers and designers without the room getting changed.

So I choose room 1, it will show me all the doctors and engineers in room 1. Next, if I filter by Doctors, the sheet would show me doctors in all of the rooms. not just room 2.

Does this give a better picture of my problem?

 

[Pumperkin]

Hi, I don't think I fully understand I'm sorry. If the questionnaire is purely within Excel and you can answer by clicking the drop downs then my solution should work (I have built your data into a worksheet and can filter so it shows: Room #2, WHO Doctor, Hidden 1.)

 

[DanteAmor]

I didn't understand the auxiliary column part. Can you please elaborate it?

First, in a column you put the following formula and copies it down.

Book1
	A	B	C
1	ROOM	WHO	AUX
2	1	Designer	1Designer
3	1	Enginer	1Enginer
4	1	Doctor	1Doctor
5	2	Designer	2Designer
6	2	Enginer	2Enginer
7	2	Doctor	2Doctor
8	3	Doctor	3Doctor
9	3	Doctor	3Doctor
10	3	Designer	3Designer
Sheet

Cell Formulas
Range	Formula
C2:C10	C2	=A2&B2

Then you filter in the auxiliary column for the data you need: 2Doctor:

 

[Fluff]

@fv1399
first you say

I want to be able to filter between doctors, engineers and designers without the room getting changed.

Then you say

Next, if I filter by Doctors, the sheet would show me doctors in all of the rooms. not just room 2.

These seem (to me at least) to contradict each other.