autofilter table AND adjacent data

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,056
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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
Peter_SSs said:
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?
Click to expand...

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
OBAUESMNC19-JUL-2019
OBAUESMNC05-JUL-2019
OBAUESM10-JUL-2019
OBAUESMNC12-JUL-2019Public
OBAUESMNC12-JUL-2019Public
OBAUESMNC12-JUL-2019Private
NBAUESMNC09-JUL-2019Public
NBAUESMNC12-JUL-2019Private
NBAUESMNC12-JUL-2019Public
NBAUESMNC12-JUL-2019Private
NBAUESMNC10-JUL-2019Public
NBAUESMNC12-JUL-2019Private
NBAUESMNC09-JUL-2019

<tbody>
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR] [/TD]

[TD="align: right"] 000000000000000019507333000000000000000270479337 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 000000000000000019507333000000000000000270479337 [/TD]

[TD="align: right"][/TD]

[TD="align: right"] 000000000000000168031471000000000000000011916492 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019507319000000000000000011916412 [/TD]

[TD="align: right"][/TD]

</tbody>


setting the filter for N:

NR_TYPESD
NBAUESMNC09-JUL-2019Public
NBAUESMNC12-JUL-2019Private
NBAUESMNC12-JUL-2019Public
NBAUESMNC12-JUL-2019Private
NBAUESMNC10-JUL-2019Public
NBAUESMNC12-JUL-2019Private
NBAUESMNC09-JUL-2019

<tbody>
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR] [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019507319000000000000000011916412 [/TD]

[TD="align: right"][/TD]

</tbody>


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

NR_TYPESD
OBAUESMNC12-JUL-2019Public
OBAUESMNC12-JUL-2019Public
NBAUESMNC09-JUL-2019Public
NBAUESMNC12-JUL-2019Public
NBAUESMNC10-JUL-2019Public

<tbody>
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR] [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

</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
NBAUESMNC09-JUL-2019Public
NBAUESMNC12-JUL-2019Public
NBAUESMNC10-JUL-2019Public

<tbody>
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]SOURCE2[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ESM_APPT_LOC_ID3[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]CONTRACT_SER4[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]APPT_DATE5[/COLOR] [/TD]
[TD="bgcolor: #4472C4"] [COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]PATIENT_TYPE6[/COLOR] [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

[TD="align: right"] 000000000000000019513639000000000000000011916817 [/TD]

</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
You must log in or register to reply here.

Similar threads

S
  • Solved
Return error if "Filter" doesn't return the required entry
Replies
2
Views
172
skittlz
S
V
  • Question Question
autofilter
Replies
2
Views
397
vmjan02
V
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
411
Peter_SSs
Peter_SSs
T
  • Question Question
Userform - Date Autofilter
Replies
3
Views
86
dmt32
D
R
  • Question Question
Help auto filtering dynamic data table
Replies
1
Views
200
russelldt
R

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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