Pivot table - automatically hide rows that meet specific criteria

[CHG]

Hi - I'm trying to use pivot tables to check the integrity of some data. I've figured out a quasi-automatic way to do it, but I need help figuring out how to fully automate the process.

A simple example of the types of pivot tables I work with is below (sorry, I couldn't figure out how to show the table borders, so I used underlines instead-hopefully you can see them OK). The right-most column is one I added next to the pivot table to illustrate which rows I want to keep and which ones I want to hide/delete. In a perfect world, every entry in my master data file for a particular state would have the same region (eg, both entries from Arkansas reflect the Midwest as the region). Unfortunately, sometimes the State and Region don't match, illustrated by the fact that my pivot table has two entries for Connecticut - one with the Region set to Atlantic and one with the Region set to New England. Does anyone know how I could exclude or hide entries like Arkansas & California and only show entries like Connecticut? The actual files I'm working with have thousands of entries in the first column (it's actually customer accounts, not States), so deleting/hiding the rows that are OK is really useful before sending the questionable rows to the sales force so they can figure out if the "State" entry is wrong or if the "Region" is wrong - or if they should be different for some reason.

<table border="0" cellpadding="0" cellspacing="0" width="305"><tbody><tr style="height: 12.75pt;" height="17"><td class="xl24" style="height: 12.75pt; width: 68pt;" height="17" width="90">Count of State</td> <td class="xl25" style="width: 62pt;" width="83">
</td> <td class="xl29" style="width: 26pt;" width="35">
</td> <td class="xl29" style="border-left: medium none; width: 73pt;" width="97">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">State</td> <td class="xl24">Region</td> <td class="xl29" align="center">Total</td> <td class="xl33" style="border-left: medium none;">Desired Action:</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Arkansas</td> <td class="xl24">Midwest</td> <td class="xl29" align="center">2</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">California</td> <td class="xl24">Pacific</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Connecticut</td> <td class="xl24">Atlantic</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl27">New England</td> <td class="xl30" align="center">1</td> <td class="xl34" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Florida</td> <td class="xl24">Southeast</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Georgia</td> <td class="xl24">Midwest</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl27">Southeast</td> <td class="xl30" align="center">1</td> <td class="xl34" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Hawaii</td> <td class="xl24">Pacific</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Illinois</td> <td class="xl24">Midwest</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Montana</td> <td class="xl24">West</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">New York</td> <td class="xl24">Atlantic</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Pennsylvania</td> <td class="xl24">Atlantic</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt;" height="17">Grand Total</td> <td class="xl32">
</td> <td class="xl31" align="center">13</td> <td class="xl31" style="border-left: medium none;">
</td> </tr> </tbody></table>
The quasi-automatic work-around requires copying the entire pivot table using if/then statements to set rows like Arkansas to blanks, adding a new column that copies row headings like Connecticut into all of the Connecticut-related rows, and then running a new pivot table based on this new list of data. Unfortunately, this process is proving to be too cumbersome to teach to everyone who needs to scrub data files like this.

Finally, if you don't know how to answer my question but have suggestions for what phrases to use when I search forums, I'd appreciate those suggestions too. "Hide rows in pivot tables" isn't specific enough.

Oh, and I'm also happy to take suggestions on how to post a copy of a pivot table into the forum - I saw them posted in other threads, but couldn't figure out how to do that either.

 

[Fazza]

Hi. It seems that the question is how to select data only where the count of states is greater than 1. And you want to use a pivot table.

I suggest giving the source data a defined name, non-dynamic. I've used "MD". After saving the file, start from a separate file via menu ALT-D-P to start the pivot table wizard. At the first step choose 'external source data' and then follow the wizard - get data, Excel files, etc - to the end and take the option to edit in MS Query. MS Query will open, then use the 'SQL' button, replace what you see by the SQL below, OK to enter it, acknowledge any messages en route to seeing the results dataset, use the 'open door' button to exit MS Query & complete the pivot table. If you like, the resultant worksheet can be moved into the original source data file.

HTH, Fazza

SELECT state, region
FROM MD
WHERE state IN
(SELECT state
FROM MD
GROUP BY state
HAVING COUNT(*) > 1)

 

[CHG]

Thank you so much for your reply. This is really close...it kept Connecticut & Georgia (which I need), but it also kept Arkansas - perhaps because it's counting each of the Arkansas>Midwest entries as unique? Any thoughts on how to get the query to exclude Arkansas as well?

 

[Fazza]

It might be best if there is some sample data. I assumed the posted data was the sample data, which is obviously wrong: it is the result of a pivot table. Necessarily the SQL does a specific job.

I took this as the source data,
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=166 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" span=2 width=83><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" width=83 height=17>state</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff99" width=83>region</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Arkansas</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Midwest</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>California</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Pacific</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Connecticut</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Atlantic</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Connecticut</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">New England</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Florida</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Southeast</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Georgia</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Midwest</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Georgia</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Southeast</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hawaii</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Pacific</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Illinois</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Midwest</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Montana</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">West</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>New York</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Atlantic</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Pennsylvania</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Atlantic</TD></TR></TBODY></TABLE>

And aimed "to select data only where the count of states is greater than 1". Obviously Arskansas exists only once in the source data I used but it is different in your data.

 

[Fazza]

This is convoluted: it might be suitable, though. I can try to improve it if it works but is slow.

SELECT DISTINCT state, region
FROM MD
WHERE state IN
(SELECT state
FROM (SELECT DISTINCT state, region
FROM MD)
GROUP BY state
HAVING COUNT(*) >1)

 

[CHG]

Sorry about that - posting the original data would have been helpful. I converted the original data to state/regions, but I think a sample of the actual data might make it easier to understand the goal. Here's a sample of the actual data:

<table border="0" cellpadding="0" cellspacing="0" width="181"><col style="width: 63pt;" width="84"> <col style="width: 73pt;" width="97"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 63pt;" height="17" width="84">Domain</td> <td class="xl24" style="width: 73pt;" width="97">Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@abc.com</td> <td>ABC Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@abcd.com</td> <td>ABC Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@qrs.com</td> <td>QRS Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@qrs.com</td> <td>QRS Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@123.com</td> <td>123 Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@def.com</td> <td>DEF Compa ny</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@def.com</td> <td>DEF Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@yahoo.com</td> <td>ABC Company</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">@yahoo.com</td> <td>QRS Company</td> </tr> </tbody></table>
I'm trying to use the Domain names in Column A to find typos or incorrect Company names in Column B. If I run a pivot table based on this data, it looks like this:

<table border="0" cellpadding="0" cellspacing="0" width="313"><col style="width: 63pt;" width="84"> <col style="width: 73pt;" width="97"> <col style="width: 26pt;" width="35"> <col style="width: 73pt;" width="97"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 63pt;" height="17" width="84">Count of Company</td> <td class="xl25" style="width: 73pt;" width="97">
</td> <td class="xl29" style="width: 26pt;" width="35">
</td> <td class="xl29" style="border-left: medium none; width: 73pt;" width="97">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Domain</td> <td class="xl24">Company</td> <td class="xl29" align="center">Total</td> <td class="xl33" style="border-left: medium none;">Desired Action:</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">@123.com</td> <td class="xl24">123 Company</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">@abc.com</td> <td class="xl24">ABC Company</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">@abcd.com</td> <td class="xl24">ABC Company</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">hide or delete</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">@def.com</td> <td class="xl24">DEF Compa ny</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl27">DEF Company</td> <td class="xl30" align="center">1</td> <td class="xl33" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">@qrs.com</td> <td class="xl24">QRS Company</td> <td class="xl29" align="center">2</td> <td class="xl33" style="border-left: medium none;">hide or delete
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">@yahoo.com</td> <td class="xl24">ABC Company</td> <td class="xl29" align="center">1</td> <td class="xl33" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl27">QRS Company</td> <td class="xl30" align="center">1</td> <td class="xl34" style="border-left: medium none;">KEEP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td><td class="xl24">
</td> <td class="xl29" align="center">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt;" height="17">Grand Total</td> <td class="xl32">
</td> <td class="xl31" align="center">9</td> <td>
</td> </tr> </tbody></table>

I'd like to exclude @123.com, @abc.com, @abcd.com or @qrs.com because they are all associated with only 1 company (so they don't give me any information about potential issues with the data). I do want to look at @def.com and @yahoo.com because they are associated with multiple companies. After reviewing the two accounts with domains @def.com, I'd realize that the name was typo'd in one of the accounts & fix it. After reviewing @yahoo.com, I'd leave the accounts alone because those 2 accounts probably are related to different companies.

PS - thank you so much for your help with this. I'm sorry I didn't provide all of the relevant information in the first post.

 

[CHG]

Just saw the reply with the 2nd batch of code...I'll try it and let you know how it works.

 

[CHG]

That worked!!! Thank you so much!!

 

[Fazza]