creating filtered views

[janetts]

I have about 500 records belonging to the 50 states and about 20 or so regions. A state can below to more than one region. I need to make it easy for the regions to see the data for their states. I could create separate worksheets for each region, but that would be very inefficient to update. In SAS, I could define subsets using if statements, along the lines of "if state='tx' or state='ok' then region=1". I have much less experience with VBA, and I can't find a good example of how to do this in any of the VBA code that I've looked at. I would like to set up the worksheet so that users could select the region of interest (either by selecting from a list or inputting a region identified) and filter the data accordingly. Any suggestions?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

A relatively easy way to do that would be to use AutoFilters.
If you haven't used them before, there is an explanation in Excel Help.

Just ask if you get stuck trying that approach.

 

[janetts]

I tried that, but I don't know how to get around the fact that some states are in more than one region. I have my region identifiers stored in one column. So, some states might have, let's say, region "7" and others might have region "7, 8, 9". Is there a way to autofilter without having to replicate all of the latter state's information once for each region that it belongs to?

 

[Jerry Sullivan]

Oh... From your original post, I didn't understand that states could be assigned to more than one region. Populating the region field with multiple items in a single cell like "7, 8, 9" makes handling of the dataset more complicated. On the other hand I understand your desire to not have to replicate a single state's data on multiple rows. For larger databases this would typically be handled by having one table with a record for each state's information, and another table used just to map the states and regions. When data for a specific state or region is needed, a query is done to join matching records.

With your current data, you could use autofilters to filter for all records of Region 20, buy filtering for "Contains" "20".
The problem arises when you want to filter to show only Region 2. Filtering for "Contains" "2" will show states that contain 2,12,20,21...

If you modify your entries to all be 2 characters, "07, 08, 09" that would allow you to filter for Contains "07".
Similarly, if you modify your entries with a leading comma ", 7, 8, 9" you can filter by Contains ", 7" although this is more error prone.

If neither of those options appeal to you, alternatives would include using Advanced Filter (with formula based criteria), or a VBA macro.
I'd be glad to help with either of those options if you like.

 

[janetts]

Thanks. That was a good idea. I've got my region column converted to all two-digit identifiers. Now I can probably figure out how to create a button or link for each region so they can click it to see just their data. I greatly appreciate your help.

Oh... From your original post, I didn't understand that states could be assigned to more than one region. Populating the region field with multiple items in a single cell like "7, 8, 9" makes handling of the dataset more complicated. On the other hand I understand your desire to not have to replicate a single state's data on multiple rows. For larger databases this would typically be handled by having one table with a record for each state's information, and another table used just to map the states and regions. When data for a specific state or region is needed, a query is done to join matching records.

With your current data, you could use autofilters to filter for all records of Region 20, buy filtering for "Contains" "20".
The problem arises when you want to filter to show only Region 2. Filtering for "Contains" "2" will show states that contain 2,12,20,21...

If you modify your entries to all be 2 characters, "07, 08, 09" that would allow you to filter for Contains "07".
Similarly, if you modify your entries with a leading comma ", 7, 8, 9" you can filter by Contains ", 7" although this is more error prone.

If neither of those options appeal to you, alternatives would include using Advanced Filter (with formula based criteria), or a VBA macro.
I'd be glad to help with either of those options if you like.