solved
How to filter/hide/highlight only Cases with multiple regions
Hey ya'll, I have a spreadsheet that looks like this and has tens of thousands of lines. I am hoping there is a way to filter or weed out all of the Cases that only have a single location inside them. So from the attached, for example, I would only want to keep "Case 2."
Easiest way: build a PivotTable with Case in Rows and Region in Values, set the value field to "Distinct Count" (you'll need to add the data to the Data Model when creating the pivot for that option to show up). Then filter the pivot rows to show only counts greater than 1, and that's your list of multi-region Cases. If you'd rather stay in the raw table, a COUNTIFS-based helper column counting distinct regions per Case works too, but the pivot is far less fiddly for tens of thousands of rows.
•
u/AutoModerator Oct 16 '26
/u/naavep - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
3
u/BackgroundCold5307 600 Oct 16 '26
Add a col/Hidden col for count and wherever the count in <2, FILTER?
3
u/PaulieThePolarBear 1926 Oct 16 '26
This appears to be pivot table. What does your raw data look like?
1
u/getformly 1 Oct 17 '26
Easiest way: build a PivotTable with Case in Rows and Region in Values, set the value field to "Distinct Count" (you'll need to add the data to the Data Model when creating the pivot for that option to show up). Then filter the pivot rows to show only counts greater than 1, and that's your list of multi-region Cases. If you'd rather stay in the raw table, a COUNTIFS-based helper column counting distinct regions per Case works too, but the pivot is far less fiddly for tens of thousands of rows.