autofilter table AND adjacent data

A

ajm

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

V
  • Question Question
autofilter
Replies
2
Views
304
vmjan02
V
R
  • Question Question
Help auto filtering dynamic data table
Replies
1
Views
160
russelldt
R
D
  • Question Question
Using formulas in autofilter
Replies
3
Views
169
RoryA
RoryA
Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
615
zero269
Z
B
  • Solved
VBA Copy adjacent AND non-adjacent named table columns
Replies
2
Views
227
Bleed_Air
B

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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