# Scoring/Ranking 2 Columns of Stock Symbols

1. ## Scoring/Ranking 2 Columns of Stock Symbols

There is probably a very simple solution to this problem, but I can't figure

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.

2. ## Re: Scoring/Ranking 2 Columns of Stock Symbols

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

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

go here

4. ## Re: Scoring/Ranking 2 Columns of Stock Symbols

Dave,

the OP stressed that the ranking would be done externally.

Pete

5. ## Re: Scoring/Ranking 2 Columns of Stock Symbols

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.

6. ## Re: Scoring/Ranking 2 Columns of Stock Symbols

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

"Manfred" <nothanks@yahoo.com> wrote in message
news:du32g2\$ksj\$1@news.xmission.com...
> 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.
>
>
>

7. ## Re: Scoring/Ranking 2 Columns of Stock Symbols

>Now, sort on column G.

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

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:ejBiSxOPGHA.2624@TK2MSFTNGP12.phx.gbl...
> 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
>
> "Manfred" <nothanks@yahoo.com> wrote in message
> news:du32g2\$ksj\$1@news.xmission.com...
>> 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
>>
>> 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.
>>
>>
>>

>
>

8. ## Re: Scoring/Ranking 2 Columns of Stock Symbols

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1