Link to home
Start Free TrialLog in
Avatar of Ed Gorth
Ed Gorth

asked on

Need an Excel macro to compare ratings of two columns and list results in third column based on rating improvements in column B.

I am in need of an Excel macro that will perform the following:

Compare two columns of stock symbols that are numerically listed and the results of the comparison displayed in a third column.  I’m looking for stocks in column B that have improved in their numerical order (one being the best) from their listing in column A.  The results would be displayed in new column C, and the entries would be listed in order of those making the best improvement at the top.

Considerations:
•      Columns A and B contain 200 entries (rows) each.  No blank cells in either column A or B.
•      Column B may have symbols that are not contained in column A.  
•      Symbols in column B not listed in column A would receive the highest ratings in column C.
•      If there are two or more symbols in column B that are not in column A, the symbols in column B would be ranked accordingly in column C.

Example of conditions for column C listing using attached example file: Two columns of Symbols.xlsx.
•      Symbol ARDM is listed in column B but not column A, so it goes to the top of the list in column C and is listed alphabetically (preferred, but not necessary).
•      Symbol FCRE also listed in column B but not column A, so it goes to the top of the list in column C and is listed alphabetically (preferred, but not necessary).
•      ATNM was 151 in column A, and is 8 in column B, a gain of 143, so it would go above CADC, which was 91 in column A and is 10 in column B for a gain of 81.
•      CADC would go above NEPT because of its gain of 81 versus a gain of 20 for NEPT.

I have attached an example file, Two Columns of Symbols.xlsx.

Thanks,

Ed
Avatar of Ed Covney
Ed Covney
Flag of United States of America image

It sounds like you need one or more "service" columns. Consider adding all rows of Col A and all Rows of Col B to a new sheet, Col A. Sort A and delete all duplicates. Are you with me?
Now copy your sheet's new concatenated Cols to a new Col "X", of your original sheet. Align rows in Cols A & B with X - you end up with empty cells wherever Col X contains an item not in A or B.
Each row will now have 3 possible col contents:
 A       B        X      
 1       1        1     - both cols A and B contain an Identical symbol
 2                 2     - only col A contains the symbol
          3        3     - only col B contains the symbol

You'll need to do more work but much more manageable. Instead of working with Cols A & B, work only in column X.

I didn't see an attachment.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.