I'm still working with the various county spreadsheets I've mentioned
here in other threads. I have another problem. One of the things I've
done for the users is to create the various spreadsheet with
buttons/macros to filter down to the 10 largest counties and the 21
largest, as this is something they need but, in the past, have just
broken the list up into 2 or 3 separate lists on the same spreadsheet,
which causes other problems when they'd like to see all the counties in
one straight list.
My problem is this. I can get the Top 10 and Top 21 lists by creating
"marker columns" in which the appropriate counties have markers to
identify them. Then create macros with buttons to apply, and remove,
the filters. However, every time I get the data for a new spreadsheet,
I have to manually mark the Top 10 and Top 21 counties because every
list contains only some of the counties, but not always the same ones.
This is because any county with all zero values in that spreadsheet's
area of interest are eliminated by SPSS before being downloaded to me.
So the list of counties varies by a few nearly every time.
I am wondering if there is a way to create an array of the 10 counties
and another for the 21. Then create a macro that allows only those 10,
or 21, to display temporarily. Like an in-place filter. Whether or not
it acutally uses a filter, I don't care. Then I wouldn't have to
manually mark those counties everytime I do a new spreadsheet. Of
course, it has to be just as easy to "remove" the filter and display
all the counties when that is needed.
I should add that the Top 10 and Top 21 are always in the spreadsheets.
It's only the very small counties that are sometimes not shown in the
list.
I'm pretty sure someone here has done something similar before. Any
ideas how to do this?
Thanks in advance for the help.

SPSS... Should I assume that the data is coming from Essbase. If so ask the
person creating the raw data for you to uncheck suppress missing rows /
Suppress Zero Rows and you will get all of the data, even where there is no
data. The way in which the data comes to you. Is it possible to pivot the
data. Then you can use the advanced options to show Top 10, Top 21... If this
is Essbase (which I am very familiar with) and you would like to take this
off line let me know and we can exchange e-mails and really get into it.

HTH

"davegb" wrote:

I'm still working with the various county spreadsheets I've mentioned
here in other threads. I have another problem. One of the things I've
done for the users is to create the various spreadsheet with
buttons/macros to filter down to the 10 largest counties and the 21
largest, as this is something they need but, in the past, have just
broken the list up into 2 or 3 separate lists on the same spreadsheet,
which causes other problems when they'd like to see all the counties in
one straight list.
My problem is this. I can get the Top 10 and Top 21 lists by creating
"marker columns" in which the appropriate counties have markers to
identify them. Then create macros with buttons to apply, and remove,
the filters. However, every time I get the data for a new spreadsheet,
I have to manually mark the Top 10 and Top 21 counties because every
list contains only some of the counties, but not always the same ones.
This is because any county with all zero values in that spreadsheet's
area of interest are eliminated by SPSS before being downloaded to me.
So the list of counties varies by a few nearly every time.
I am wondering if there is a way to create an array of the 10 counties
and another for the 21. Then create a macro that allows only those 10,
or 21, to display temporarily. Like an in-place filter. Whether or not
it acutally uses a filter, I don't care. Then I wouldn't have to
manually mark those counties everytime I do a new spreadsheet. Of
course, it has to be just as easy to "remove" the filter and display
all the counties when that is needed.
I should add that the Top 10 and Top 21 are always in the spreadsheets.
It's only the very small counties that are sometimes not shown in the
list.
I'm pretty sure someone here has done something similar before. Any
ideas how to do this?
Thanks in advance for the help.

Jim,
Thanks for your reply. I will have to check with the person sending me
the data, they may or may not want to change how they do it.
I'm interested in finding out how to use a Pivot Table to filter data.
I've used them a little, but always to change the data display, never
to "hide" some of the data. Can you point me in the right direction on
how they can be used that way?