Scoring/Ranking 2 Columns of Stock Symbols

[Bohica]

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.

 

[Pete_UK]

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 <copy> as the range will still
be highlighted, then Edit | Paste Special | Values (check) OK and
<Esc>.

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

 

[davesexcel]

http://xldynamic.com/source/xld.RANK.html

go here

 

[Pete_UK]

Dave,

the OP stressed that the ranking would be done externally.

Pete

 

[Manfred]

Thank 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 "#VALUE?" 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.

 

[Biff]

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

 

[Biff]

Now, sort on column G.

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

Biff

 

[Manfred]

That works! You've helped me out immensely. Great thanks to both of you
for helping me with this.