Mixing
How to rank and filter in Excel VBA?
I am new to VBA and each month I extract a data set of 5000 ratings of companies. I have managed to get the data into the below format. What I need to do is end up with a single 'rating' for each company.
For example, I have 'Market Weight', 'Underweight' and 'Overweight'. If the company has any market weight rating then I want to use that for the overall rating, but if thats not there then underweight then if not that overweight.
The issue is each company represented by a ticker, has several ratings and what I want to do is only take the one. so in the image below, take ARNC we should end up with one rating as Market Weight for ARNC. For BBDBCN is should be underweight as there isn't any market weight present.
Link to the picture
excel vba new-operator rank
edited Nov 19 '18 at 19:01
halfer
14.3k758109
asked Nov 19 '18 at 12:26
dsound
32
add a comment |
I am new to VBA and each month I extract a data set of 5000 ratings of companies. I have managed to get the data into the below format. What I need to do is end up with a single 'rating' for each company.
For example, I have 'Market Weight', 'Underweight' and 'Overweight'. If the company has any market weight rating then I want to use that for the overall rating, but if thats not there then underweight then if not that overweight.
The issue is each company represented by a ticker, has several ratings and what I want to do is only take the one. so in the image below, take ARNC we should end up with one rating as Market Weight for ARNC. For BBDBCN is should be underweight as there isn't any market weight present.
Link to the picture
excel vba new-operator rank
edited Nov 19 '18 at 19:01
halfer
14.3k758109
asked Nov 19 '18 at 12:26
dsound
32
1
What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 '18 at 12:57
Hi, I want help in determining the true rating to associate with a company as a company may have been rated underweight and overweight or market weight for its different subsidiaries. So far I have worked to create a script that gets me this far as the data was a mess before. With this I tried custom sorts etc. but had no luck... I am trying hard and appreciate help so I can be better going forward. I have been stuck on this for a week
– dsound
Nov 19 '18 at 18:40
add a comment |
I am new to VBA and each month I extract a data set of 5000 ratings of companies. I have managed to get the data into the below format. What I need to do is end up with a single 'rating' for each company.
For example, I have 'Market Weight', 'Underweight' and 'Overweight'. If the company has any market weight rating then I want to use that for the overall rating, but if thats not there then underweight then if not that overweight.
The issue is each company represented by a ticker, has several ratings and what I want to do is only take the one. so in the image below, take ARNC we should end up with one rating as Market Weight for ARNC. For BBDBCN is should be underweight as there isn't any market weight present.
Link to the picture
excel vba new-operator rank
edited Nov 19 '18 at 19:01
halfer
14.3k758109
asked Nov 19 '18 at 12:26
dsound
32
I am new to VBA and each month I extract a data set of 5000 ratings of companies. I have managed to get the data into the below format. What I need to do is end up with a single 'rating' for each company.
For example, I have 'Market Weight', 'Underweight' and 'Overweight'. If the company has any market weight rating then I want to use that for the overall rating, but if thats not there then underweight then if not that overweight.
The issue is each company represented by a ticker, has several ratings and what I want to do is only take the one. so in the image below, take ARNC we should end up with one rating as Market Weight for ARNC. For BBDBCN is should be underweight as there isn't any market weight present.
Link to the picture
excel vba new-operator rank
excel vba new-operator rank
edited Nov 19 '18 at 19:01
halfer
14.3k758109
asked Nov 19 '18 at 12:26
dsound
32
edited Nov 19 '18 at 19:01
halfer
14.3k758109
asked Nov 19 '18 at 12:26
dsound
32
edited Nov 19 '18 at 19:01
halfer
14.3k758109
edited Nov 19 '18 at 19:01
halfer
14.3k758109
edited Nov 19 '18 at 19:01
halfer
14.3k758109
14.3k758109
asked Nov 19 '18 at 12:26
dsound
32
asked Nov 19 '18 at 12:26
dsound
32
asked Nov 19 '18 at 12:26
dsound
32
32
1
What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 '18 at 12:57
Hi, I want help in determining the true rating to associate with a company as a company may have been rated underweight and overweight or market weight for its different subsidiaries. So far I have worked to create a script that gets me this far as the data was a mess before. With this I tried custom sorts etc. but had no luck... I am trying hard and appreciate help so I can be better going forward. I have been stuck on this for a week
– dsound
Nov 19 '18 at 18:40
add a comment |
1
What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 '18 at 12:57
Hi, I want help in determining the true rating to associate with a company as a company may have been rated underweight and overweight or market weight for its different subsidiaries. So far I have worked to create a script that gets me this far as the data was a mess before. With this I tried custom sorts etc. but had no luck... I am trying hard and appreciate help so I can be better going forward. I have been stuck on this for a week
– dsound
Nov 19 '18 at 18:40
1
1
What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 '18 at 12:57
What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 '18 at 12:57
Hi, I want help in determining the true rating to associate with a company as a company may have been rated underweight and overweight or market weight for its different subsidiaries. So far I have worked to create a script that gets me this far as the data was a mess before. With this I tried custom sorts etc. but had no luck... I am trying hard and appreciate help so I can be better going forward. I have been stuck on this for a week
– dsound
Nov 19 '18 at 18:40
Hi, I want help in determining the true rating to associate with a company as a company may have been rated underweight and overweight or market weight for its different subsidiaries. So far I have worked to create a script that gets me this far as the data was a mess before. With this I tried custom sorts etc. but had no luck... I am trying hard and appreciate help so I can be better going forward. I have been stuck on this for a week
– dsound
Nov 19 '18 at 18:40
add a comment |
1 Answer
1
active
oldest
votes
I have added a weight column and a helper table for the weights in order to solve your problem
Find below a screenshot
You can use the below formulae
Formulae are as below
Cell C2: =VLOOKUP(B2,$E$2:$F$4,2,0)
Cell I2: =MAX(IF($A$2:$A$11=H2,$C$2:$C$11))
Cell J2: =INDEX($E$2:$E$4,MATCH(I2,$F$2:$F$4,0))
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53374617%2fhow-to-rank-and-filter-in-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I have added a weight column and a helper table for the weights in order to solve your problem
Find below a screenshot
You can use the below formulae
Formulae are as below
Cell C2: =VLOOKUP(B2,$E$2:$F$4,2,0)
Cell I2: =MAX(IF($A$2:$A$11=H2,$C$2:$C$11))
Cell J2: =INDEX($E$2:$E$4,MATCH(I2,$F$2:$F$4,0))
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
add a comment |
I have added a weight column and a helper table for the weights in order to solve your problem
Find below a screenshot
You can use the below formulae
Formulae are as below
Cell C2: =VLOOKUP(B2,$E$2:$F$4,2,0)
Cell I2: =MAX(IF($A$2:$A$11=H2,$C$2:$C$11))
Cell J2: =INDEX($E$2:$E$4,MATCH(I2,$F$2:$F$4,0))
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
add a comment |
I have added a weight column and a helper table for the weights in order to solve your problem
Find below a screenshot
You can use the below formulae
Formulae are as below
Cell C2: =VLOOKUP(B2,$E$2:$F$4,2,0)
Cell I2: =MAX(IF($A$2:$A$11=H2,$C$2:$C$11))
Cell J2: =INDEX($E$2:$E$4,MATCH(I2,$F$2:$F$4,0))
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
I have added a weight column and a helper table for the weights in order to solve your problem
Find below a screenshot
You can use the below formulae
Formulae are as below
Cell C2: =VLOOKUP(B2,$E$2:$F$4,2,0)
Cell I2: =MAX(IF($A$2:$A$11=H2,$C$2:$C$11))
Cell J2: =INDEX($E$2:$E$4,MATCH(I2,$F$2:$F$4,0))
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
edited Nov 20 '18 at 2:01
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
answered Nov 19 '18 at 15:01
usmanhaq
1,123128
1,123128
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
add a comment |
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
thank you for your help. 2 points. 1) I am getting '0' and '#NA' for BBDBCN Column I and J when I copy the formula in exactly, any idea why? 2) Would this be suitable to use for thousands of tickers and companies etc. to analyse all at once?
– dsound
Nov 19 '18 at 18:37
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
Formula is Cell I2 is an an array formula, after entering or editing it you have to press Ctrl+Shift+Enter
– usmanhaq
Nov 20 '18 at 2:02
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53374617%2fhow-to-rank-and-filter-in-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 '18 at 12:57
Hi, I want help in determining the true rating to associate with a company as a company may have been rated underweight and overweight or market weight for its different subsidiaries. So far I have worked to create a script that gets me this far as the data was a mess before. With this I tried custom sorts etc. but had no luck... I am trying hard and appreciate help so I can be better going forward. I have been stuck on this for a week
– dsound
Nov 19 '18 at 18:40