Is it possible to filter data into groups that contain a common data point using Pivot tables?

mothboy

New Member
Joined
Nov 2, 2012
Messages
16
Hi,

Please see example below. I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area, is this possible?

As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. Please see example below - hopefully that makes my requirements a bit easier to understand :confused:. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.

Cheers in advance(y)


Before filtering:
Study IDStudy Short TitleStudy Patient IDArea
1346LLP90126Northwest
1346LLP91749Southwest
1346LLP92554Southeast
2346PLP92569South
2346PLP92803Northwest
2346PLP92821East
3346RLP92825West
3346RLP92874South
3346RLP92891Northwest
4346TLP92909Southwest
4346TLP93151PCRN-NW
4346TLP91736SouthEast
5346VLP91737Southwest
5346VLP91738Bortheast
5346VLP91767West
6346XLP91783Northwest
6346XLP91791South
6346XLP91801East

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>


After filtering:
Study IDStudy Short TitleStudy Patient IDArea
2346PLP92569South
2346PLP92803Northwest
2346PLP92821East
3346RLP92825West
3346RLP92874South
3346RLP92891Northwest
6346XLP91783Northwest
6346XLP91791South
6346XLP91801East

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi mothboy,

You could add an extra field to your source data, and another helper field so that an array formula isn't necessary.
Once setup, the user just filters the "Matches" field for "Show".
Excel Workbook
ABCDEFGH
1Study IDStudy Short TitleStudy Patient IDAreaMatchesMatch CodeSouth
21346LLP90126NorthwestHide1346|Northwest
31346LLP91749SouthwestHide1346|Southwest
41346LLP92554SoutheastHide1346|Southeast
52346PLP92569SouthShow2346|South
62346PLP92803NorthwestShow2346|Northwest
72346PLP92821EastShow2346|East
83346RLP92825WestShow3346|West
93346RLP92874SouthShow3346|South
103346RLP92891NorthwestShow3346|Northwest
114346TLP92909SouthwestHide4346|Southwest
124346TLP93151PCRN-NWHide4346|PCRN-NW
134346TLP91736SouthEastHide4346|SouthEast
145346VLP91737SouthwestHide5346|Southwest
155346VLP91738BortheastHide5346|Bortheast
Sheet
#VALUE!
</td></tr></table></td></tr></table>


Using a reference to a named range on the same sheet as the PivotTable instead of explicitly having "South" in your formula could make it more dynamic.
 

mothboy

New Member
Joined
Nov 2, 2012
Messages
16
Jerry, thanks for this suggestion.

It's obviously much more efficient than the array solution however still takes about 15 mins to calculate on our standard issue laptops.

The manager I'm developing this for would use a 5 min manual method rather than wait for this to run.

Any other ideas on a 'no wait' solution?

Cheers

Tim
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi, Tim

Have SQL do the hard work.
Code:
SELECT [Study ID], [Study Short Title], [Study Patient ID], Area
FROM MyData
WHERE [Study ID] IN (
SELECT DISTINCT [Study ID]
FROM MyData
WHERE Area = 'South')

I've assumed defined name "MyData" for the source data table, headers per sample. Though this is not essential, by the way. You can use a worksheet reference.

Steps are: give source data the normal defined name; save the file. From a new file start the pivot table wizard (ALT-D-P) choosing external data source at the first step. Follow the wizard. As the specific steps vary by Excel version I won't spell them out. Basically choose Excel source data, browse for file, select file, select MyData data source, continue to last step of wizard and choose option to edit in MS Query. Via the SQL button change the SQL from whatever is there to what I posted above - copy & paste from above to replace whatever is there - enter it, 'open door' icon to exit MS Query & complete the pivot table.

regards
 

mothboy

New Member
Joined
Nov 2, 2012
Messages
16

ADVERTISEMENT

:)
 
Last edited:

mothboy

New Member
Joined
Nov 2, 2012
Messages
16
Many thanks Fazza, that works nicely.

I've been asked to change the 'Area' column to 'National Area', but the SQL code doesn't seem to like it.

Can you advise how to amend the code to accept more than one word column titles for that part of the code?

Cheers
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Sure, Tim. In fact you can see it in the SQL already.

[field name with a space is enclosed in brackets]
`alternative not recommended`
field_name_without_space_does_not_need_brackets
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Thanks Fazza, I suspected there must be an SQL query that could work, but that was beyond my SQL skills.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
A little SQL can do a lot of work. A little more practice and you'll be there, Jerry.

Keep Excelling. Regards, Fazza
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

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
Top