I have an autofiltered list of data, that currently allows the user to
select the client from the list, then there are subtotals at the bottom,
which I use as the basis for further analysis. So my boss can see each
clients data but only one at a time.
What I want to be able to do, is via code select each possible client from
the list, apply that to the autofilter, then do the existing calcs, and move
on to the next client, do the calcs, store the data one column over, etc to
the bottom of the list.

The bit I can't figure out is how to get the list of unique clients that the
autofilter shows when one uses it manually.
Granted I could sort the data by client, create an array, run down the list
adding the next client, and create my own list; but surely the list Excel
shows must be accessable somehow.

TIA

Graham,

What you are describing can be done automatically using pivot tables.

OR

To get the list of unique values, use data / filter... / advanced filter / unique values only, copy
to another location.

HTH,
Bernie
MS Excel MVP


"Graham Y" <(E-Mail Removed)> wrote in message
news:8C30C82B-8DD8-4BC3-8CAB-(E-Mail Removed)...
>I have an autofiltered list of data, that currently allows the user to
> select the client from the list, then there are subtotals at the bottom,
> which I use as the basis for further analysis. So my boss can see each
> clients data but only one at a time.
> What I want to be able to do, is via code select each possible client from
> the list, apply that to the autofilter, then do the existing calcs, and move
> on to the next client, do the calcs, store the data one column over, etc to
> the bottom of the list.
>
> The bit I can't figure out is how to get the list of unique clients that the
> autofilter shows when one uses it manually.
> Granted I could sort the data by client, create an array, run down the list
> adding the next client, and create my own list; but surely the list Excel
> shows must be accessable somehow.
>
> TIA

Thanks Bernie, I'm not sure I could do it with pivot tables, as in my result
space I have to allow for some 'extra' income to be added manually. But that
is an interesting idea.
But the advanced filter tip would work, Thanks.

"Bernie Deitrick" wrote:

> Graham,
>
> What you are describing can be done automatically using pivot tables.
>
> OR
>
> To get the list of unique values, use data / filter... / advanced filter / unique values only, copy
> to another location.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Graham Y" <(E-Mail Removed)> wrote in message
> news:8C30C82B-8DD8-4BC3-8CAB-(E-Mail Removed)...
> >I have an autofiltered list of data, that currently allows the user to
> > select the client from the list, then there are subtotals at the bottom,
> > which I use as the basis for further analysis. So my boss can see each
> > clients data but only one at a time.
> > What I want to be able to do, is via code select each possible client from
> > the list, apply that to the autofilter, then do the existing calcs, and move
> > on to the next client, do the calcs, store the data one column over, etc to
> > the bottom of the list.
> >
> > The bit I can't figure out is how to get the list of unique clients that the
> > autofilter shows when one uses it manually.
> > Granted I could sort the data by client, create an array, run down the list
> > adding the next client, and create my own list; but surely the list Excel
> > shows must be accessable somehow.
> >
> > TIA
>
>
>