autofilter table AND adjacent data

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
Is it possible to autofilter a data table and the adjacent data using the one filter? I'm after a similar result to inserting a column before a pivot table and autofiltering that column, which then applies the filter to all used columns, including adjacent data.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Not clear enough to me. Can you give more specific information/examples about where the table is, what you mean by 'adjacent data', what column you want to filter on etc?

You mention the HTML makers in your signature - perhaps you could put one of them to good use to give us some sample data &/or expected results?
 
Upvote 0
Not clear enough to me. Can you give more specific information/examples about where the table is, what you mean by 'adjacent data', what column you want to filter on etc?

You mention the HTML makers in your signature - perhaps you could put one of them to good use to give us some sample data &/or expected results?

Ouch! fair call though.

so, I have a table in Column F to J, with two helper columns preceding it. if you have a filter set on the first column, NR_Type, which is adjacent to the table but not part of it, lets say for "N" and then want to set a filter within the table for Patient Type, use "Public", the filter previously set on NR Type is overridden.

The unfiltered data:


NR_TYPESD[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR]
OBAUESM000000000000000019507333000000000000000270479337NC19-JUL-2019
OBAUESM000000000000000019507333000000000000000270479337NC05-JUL-2019
OBAUESM00000000000000016803147100000000000000001191649210-JUL-2019
OBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
OBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
OBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019513639000000000000000011916817NC09-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019513639000000000000000011916817NC10-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019507319000000000000000011916412NC09-JUL-2019

<tbody>
</tbody>


setting the filter for N:

NR_TYPESD[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR]
NBAUESM000000000000000019513639000000000000000011916817NC09-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019513639000000000000000011916817NC10-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Private
NBAUESM000000000000000019507319000000000000000011916412NC09-JUL-2019

<tbody>
</tbody>


setting the in table filter to find all New (N) Public patients:

NR_TYPESD[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR]
OBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
OBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC09-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC10-JUL-2019Public

<tbody>
</tbody>

Notice how the filter set to N in the first column has been overridden displaying Old(O) patient data as well as the New (N) because both old and new are public.

the desired result:

NR_TYPESD[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR]
NBAUESM000000000000000019513639000000000000000011916817NC09-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC12-JUL-2019Public
NBAUESM000000000000000019513639000000000000000011916817NC10-JUL-2019Public

<tbody>
</tbody>

I want to be able to filter the data as though the table was just a normal range.
 
Last edited:
Upvote 0
Why not just include those 2 extra columns on the left in the table?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top