Creating Spreadsheet that pull Names that have a pop. of 10+

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonathan - 01 Feb 2008 21:26 GMT

Hi all,

I have a spreadsheet that I need to set up but I can't figure out if what I
want to do is possible...

The spreadsheet tracks clients that have 10+ deals outstanding with us and
tracks which person covers the client, how many trades there are, etc.

I was wondering if there is automated way/formula to pull ONLY the clients
with ten or more trades out of a list of thousands of clients.

For example, say I have 280 different clients in my data pool, but only 15
of them have more than 10 lines. How can I write a formula to "search" the
big pool and only pull out the stats for the clients with 10 or more?

Thanks in advance.

Reply to this Message

FSt1 - 01 Feb 2008 21:28 GMT

hi
you might try the built in filter.
on the menu bar..
data>filter
once auto filter is set, you can select the 'top ten'.
also there is the advanced filter where you can set value greater than 10.
play with it and see if it will work for you. if not post back.

Rergards
FSt1

> Hi all,
>
[quoted text clipped - 12 lines]
>
> Thanks in advance.

Reply to this Message

Jonathan - 01 Feb 2008 21:40 GMT

Hi there,

This would be a good start, but it's not what I'm looking for--I want
something that all I have to do is plug in the new day's data, and it'll
automatically update the entire sheet without filtering. I don't know if
this is possible because the number of clients will change on a day to day
basis depending on how many outstanding items we have with them. It could be
that one day, there are 15 clients, and the next there are 20 more, only 5 of
which were from the day before.

> hi
> you might try the built in filter.
[quoted text clipped - 23 lines]
> >
> > Thanks in advance.

Reply to this Message

Jim - 01 Feb 2008 21:46 GMT

Jonathan,

Here's how I'd do it.

1. Create a dynamic range name for the names field.
2. Write an advanced filter to extract the unique names like this:
Range("Names").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("NamesCrit"), CopyToRange:=Range("NamesOut"), Unique:=True
3. Use countif next to the extracted list of unique names
4. Create a dynamic range name for the unique names and counts
5. Write an advanced filter to extract the unique names with countif >10.

I did it with trial data in just a few minutes.

Jim

> Hi all,
>
[quoted text clipped - 12 lines]
>
> Thanks in advance.

Reply to this Message