How to rank and filter in Excel VBA?

up vote
-4
down vote

favorite

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

edited Nov 19 at 19:01

halfer

14.2k757106

asked Nov 19 at 12:26

dsound

New contributor

1

What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 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 at 18:40

add a comment |

up vote
-4
down vote

favorite

Link to the picture

edited Nov 19 at 19:01

halfer

14.2k757106

asked Nov 19 at 12:26

dsound

New contributor

1

What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 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 at 18:40

add a comment |

up vote
-4
down vote

favorite

Link to the picture

edited Nov 19 at 19:01

halfer

14.2k757106

asked Nov 19 at 12:26

dsound

New contributor

Link to the picture

excel vba new-operator rank

edited Nov 19 at 19:01

halfer

14.2k757106

asked Nov 19 at 12:26

dsound

New contributor

edited Nov 19 at 19:01

halfer

14.2k757106

asked Nov 19 at 12:26

dsound

New contributor

edited Nov 19 at 19:01

halfer

14.2k757106

edited Nov 19 at 19:01

halfer

14.2k757106

edited Nov 19 at 19:01

halfer

14.2k757106

asked Nov 19 at 12:26

dsound

New contributor

asked Nov 19 at 12:26

dsound

asked Nov 19 at 12:26

dsound

New contributor

dsound is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

1

What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 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 at 18:40

add a comment |

1

What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 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 at 18:40

What is your question? What have you tried? Where are you stuck?
– FunThomas
Nov 19 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 at 18:40

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

I have added a weight column and a helper table for the weights in order to solve your problem

Find below a screenshot

enter image description here
You can use the below formulae

enter image description here

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

edited Nov 20 at 2:01

answered Nov 19 at 15:01

usmanhaq

791127

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 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 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',
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
});

}
});

dsound is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

I have added a weight column and a helper table for the weights in order to solve your problem

Find below a screenshot

enter image description here
You can use the below formulae

enter image description here

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

edited Nov 20 at 2:01

answered Nov 19 at 15:01

usmanhaq

791127

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 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 at 2:02

add a comment |

up vote
0
down vote

accepted

I have added a weight column and a helper table for the weights in order to solve your problem

Find below a screenshot

enter image description here
You can use the below formulae

enter image description here

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

edited Nov 20 at 2:01

answered Nov 19 at 15:01

usmanhaq

791127

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 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 at 2:02

add a comment |

up vote
0
down vote

accepted

I have added a weight column and a helper table for the weights in order to solve your problem

Find below a screenshot

enter image description here
You can use the below formulae

enter image description here

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

edited Nov 20 at 2:01

answered Nov 19 at 15:01

usmanhaq

791127

I have added a weight column and a helper table for the weights in order to solve your problem

Find below a screenshot

enter image description here
You can use the below formulae

enter image description here

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

edited Nov 20 at 2:01

answered Nov 19 at 15:01

usmanhaq

791127

edited Nov 20 at 2:01

answered Nov 19 at 15:01

usmanhaq

791127

answered Nov 19 at 15:01

usmanhaq

791127

answered Nov 19 at 15:01

usmanhaq

791127

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 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 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 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 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 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 at 2:02

add a comment |

dsound is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

dsound is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk