Creating a dynamic list based on a minimum common variable.

I want to create a lits based on a minimum common variable.

I use a program that generates a data table in a specific order. In
'COL A' are names of clients. Related data (a percentage) is in 'Col
B' .

I want to create a dynamic list that identifies the first 15 names of
that appear in COL A that have a value over a given amount in the COL
B.

One way ..

Assume the names in A1 down, corresponding percentages in B1 down
The cut-off value (%) will be entered in say, D1, eg in D1: 15%

Put in E1:
=IF($D$1="","",IF(B1$D$1,ROW(),""))
Copy E1 down to the max expected extent of data in cols A and B
(Hide away col E if desired)

Put in F1:
=IF(OR(ROW()COUNT($E:$E),ROW()15),"",INDEX(A:A,S MALL($E:$E,ROW())))
Copy F1 to G1. Format G1 as percentage. Select F1:G1, fill down by 15 rows
to G15. Col F returns the list of the 1st 15 names (or less, as the case may
be) whose percentages exceed the cut-off value entered in D1. Col G returns
the corresponding percentages for the names.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
I want to create a lits based on a minimum common variable.

I use a program that generates a data table in a specific order. In
'COL A' are names of clients. Related data (a percentage) is in 'Col
B' .

I want to create a dynamic list that identifies the first 15 names of
that appear in COL A that have a value over a given amount in the COL
B.

in order to optimize the data, my goal is to be able to change that
percentage (and alter the list of stocks) dynamically.

Any help, even pointing me in the right direction would be much
appreciated.

Dan

Try Pivot Table.
No formulas required.
I simplified the requirement to:
Find the top 5 names with the highes value.
Assume your list looks like this:

Cust Value
Ad 61
Be 50
Cf 5
Dg 96
Eh 33
Fi 1
Gj 20
Hk 2
In 11
Jm 62
Kn 21
Lo 20
Mp 3
Nq 83
Or 30

Data Pivot Table
Layout: ROW = Cust
DATA = Sum of Value
Options: Uncheck/Hide Grand Totals
The Pivot Table might look like this:

Sum of Value
Cust Total
Dg 96
Nq 83
Jm 62
Ad 61
Be 50

Right click on Cust and
Field Setting Advanced AutoSort Options
Descending Using Field Sum of Value
Top 10 AutoShow On Show Top 5
Using Field Sum of Value
A more complex criterion can be used by
creating a new field.
Refresh PT after updating the original list.

Reply from OP:

From: db_murray
To: "Max" demechanik..
Subject: Creating a dynamic list based on a minimum common variable.
Date: Tue, 20 Feb 2007 09:18:20 -0800

Thanks Max,
That worked great and is very elegant.
Dan
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---