software

There is probably a very simple solution to this problem, but I can't figure
out how to address it.

I'm ranking a list of stock symbols, best to worst, by 2 different criteria
through a separate program. The list consists of about 7000 symbols.

I want to dump the list (based on external software rankings) into 2 Excel
columns, based on the rank from each of the 2 criteria (this ranking will be
performed by external software). Then, I wish to instruct Excel to rank the
symbols based on the combined scores of the two column, from best to worst,
in a third column (or however many columns it takes to accomplish this).

Additionally, I'm hoping that Excel can be instructed to list the stocks
from best to worst, based on the combined scores.

Can somebody help me with this? I'm sure this is basic and I'm happy to pay
for any assistance.Your data will occupy columns A, B and C (assume it starts on row 1).
In D1 you can add this formula:

= B1 C1

to add the ranks together. To copy this formula down 7000 rows in one
simple operation, click on the cell then double-click the fill-handle
(the small black square in the bottom right corner of the cursor). You
might want to fix these values - click lt;copygt; as the range will still
be highlighted, then Edit | Paste Special | Values (check) OK and
lt;Escgt;.

You can then highlight all the data in columns A to D and Data | Sort,
choosing to sort on column D in ascending order.

Hope this helps - no need to pay!

Pete
xldynamic.com/source/xld.RANK.html

go here--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=517494Dave,

the OP stressed that the ranking would be done externally.

PeteThank you very much for taking the time to respond, but this solution does
not seem to work. Shouldn't I only need 2 columns? Also, the formula
yields quot;#VALUE?quot; when I paste the stock symbols into the spreadsheet.

Perhaps I should have been clearer that each of the two Excel columns will
consist of STOCK SYMBOLS (not number rankings).

So, for example, the first column will be pasted into Excel from an external
ranking program as:

LECT
MFBC
CTIG
FBEI
MHJ
WRLT
.....etc.for 6994 more rows

And then the second column, using a different formula (but consisiting of
ALL the SAME symbols) will be pasted, for example, as follows:

PLFE
WTU
BLKB
EPIC
AFBA
MTG
....etc. (all the same as above, ultimately, as above but in a different
order) for another 6994 rows.

I'd like to take these two columns, as rank them from highest to lowest
based upon each symbol's positions in BOTH columns. So, if a symbol ranks
232 in column 1, and 153 in column 2, the combined rank is 385.

Hi!

If you want as the last step to sort on the rank then you'll need a third
column of the symbols (in any order).

Assume the symbols are in one order in column A, A1:A7000, and are in
another order in column B, B1:B7000.

Copy one of the columns of symbols (either one, doesn't matter) to another
column, say, column F, F1:F7000.

Enter this formula in G1:

=MATCH(F1,A$1:A$7000,0) MATCH(F1,B$1:B$7000,0)

Copy down to G7000. For quick copying just double click the fill handle.

Now, sort on column G.

You're probably going to end up with lots of ties!

For example:

249 1
1 249
125 125
10 240
150 100

Biff

quot;Manfredquot; gt; wrote in message
...
gt; Thank you very much for taking the time to respond, but this solution does
gt; not seem to work. Shouldn't I only need 2 columns? Also, the formula
gt; yields quot;#VALUE?quot; when I paste the stock symbols into the spreadsheet.
gt;
gt; Perhaps I should have been clearer that each of the two Excel columns will
gt; consist of STOCK SYMBOLS (not number rankings).
gt;
gt; So, for example, the first column will be pasted into Excel from an
gt; external ranking program as:
gt;
gt; LECT
gt; MFBC
gt; CTIG
gt; FBEI
gt; MHJ
gt; WRLT
gt; ....etc.for 6994 more rows
gt;
gt; And then the second column, using a different formula (but consisiting of
gt; ALL the SAME symbols) will be pasted, for example, as follows:
gt;
gt; PLFE
gt; WTU
gt; BLKB
gt; EPIC
gt; AFBA
gt; MTG
gt; ...etc. (all the same as above, ultimately, as above but in a different
gt; order) for another 6994 rows.
gt;
gt; I'd like to take these two columns, as rank them from highest to lowest
gt; based upon each symbol's positions in BOTH columns. So, if a symbol ranks
gt; 232 in column 1, and 153 in column 2, the combined rank is 385.
gt;
gt;
gt;
gt;Now, sort on column G.

Before you sort, select BOTH columns F AND G, then sort on column G.

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; If you want as the last step to sort on the rank then you'll need a third
gt; column of the symbols (in any order).
gt;
gt; Assume the symbols are in one order in column A, A1:A7000, and are in
gt; another order in column B, B1:B7000.
gt;
gt; Copy one of the columns of symbols (either one, doesn't matter) to another
gt; column, say, column F, F1:F7000.
gt;
gt; Enter this formula in G1:
gt;
gt; =MATCH(F1,A$1:A$7000,0) MATCH(F1,B$1:B$7000,0)
gt;
gt; Copy down to G7000. For quick copying just double click the fill handle.
gt;
gt; Now, sort on column G.
gt;
gt; You're probably going to end up with lots of ties!
gt;
gt; For example:
gt;
gt; 249 1
gt; 1 249
gt; 125 125
gt; 10 240
gt; 150 100
gt;
gt; Biff
gt;
gt; quot;Manfredquot; gt; wrote in message
gt; ...
gt;gt; Thank you very much for taking the time to respond, but this solution
gt;gt; does not seem to work. Shouldn't I only need 2 columns? Also, the
gt;gt; formula yields quot;#VALUE?quot; when I paste the stock symbols into the
gt;gt; spreadsheet.
gt;gt;
gt;gt; Perhaps I should have been clearer that each of the two Excel columns
gt;gt; will consist of STOCK SYMBOLS (not number rankings).
gt;gt;
gt;gt; So, for example, the first column will be pasted into Excel from an
gt;gt; external ranking program as:
gt;gt;
gt;gt; LECT
gt;gt; MFBC
gt;gt; CTIG
gt;gt; FBEI
gt;gt; MHJ
gt;gt; WRLT
gt;gt; ....etc.for 6994 more rows
gt;gt;
gt;gt; And then the second column, using a different formula (but consisiting of
gt;gt; ALL the SAME symbols) will be pasted, for example, as follows:
gt;gt;
gt;gt; PLFE
gt;gt; WTU
gt;gt; BLKB
gt;gt; EPIC
gt;gt; AFBA
gt;gt; MTG
gt;gt; ...etc. (all the same as above, ultimately, as above but in a different
gt;gt; order) for another 6994 rows.
gt;gt;
gt;gt; I'd like to take these two columns, as rank them from highest to lowest
gt;gt; based upon each symbol's positions in BOTH columns. So, if a symbol
gt;gt; ranks 232 in column 1, and 153 in column 2, the combined rank is 385.
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
That works! You've helped me out immensely. Great thanks to both of you
for helping me with this.