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
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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers 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.
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.