I want to index specific values in a specific range
I have a list of students that I have the following mark criteria,
- from
0to-500
- from
-501to-1000 - from
-1001to-5000 - from
-5001to-10000000000
The list of students and the categorization were on sheet 1. On sheet 2 I have the criteria as headers.
I want a formula that I can use under lets say criteria 1
and it automatically fills all the account numbers that met the criteria under the column that has that criteria as a header.
Sheet 2:
excel vba excel-vba excel-formula
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
|
show 1 more comment
I have a list of students that I have the following mark criteria,
- from
0to-500
- from
-501to-1000 - from
-1001to-5000 - from
-5001to-10000000000
The list of students and the categorization were on sheet 1. On sheet 2 I have the criteria as headers.
I want a formula that I can use under lets say criteria 1
and it automatically fills all the account numbers that met the criteria under the column that has that criteria as a header.
Sheet 2:
excel vba excel-vba excel-formula
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
Have you tried a PivotTable?
– Pᴇʜ
Nov 21 '18 at 8:06
I have but the people im working with cannot keep updating the tablet data source every time they add a new account that's why I'm looking for a formula
– Thaer J
Nov 21 '18 at 8:18
Have a look at countifs(), sumifs(), there are examples around on here...
– Solar Mike
Nov 21 '18 at 8:40
A formula won't work you will need VBA, but because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 21 '18 at 8:44
ive tried index with match but the formula only takes the first account number that fits the criteria and ignores the rest, I've tired pivot tables but the people im working for would like it dynamic rather than them wanting to go and change the data source every time, so im pretty much stuck and im not that strong with VBA that's why i came here to ask
– Thaer J
Nov 21 '18 at 9:28
|
show 1 more comment
I have a list of students that I have the following mark criteria,
- from
0to-500
- from
-501to-1000 - from
-1001to-5000 - from
-5001to-10000000000
The list of students and the categorization were on sheet 1. On sheet 2 I have the criteria as headers.
I want a formula that I can use under lets say criteria 1
and it automatically fills all the account numbers that met the criteria under the column that has that criteria as a header.
Sheet 2:
excel vba excel-vba excel-formula
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
I have a list of students that I have the following mark criteria,
- from
0to-500
- from
-501to-1000 - from
-1001to-5000 - from
-5001to-10000000000
The list of students and the categorization were on sheet 1. On sheet 2 I have the criteria as headers.
I want a formula that I can use under lets say criteria 1
and it automatically fills all the account numbers that met the criteria under the column that has that criteria as a header.
Sheet 2:
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
edited Nov 21 '18 at 9:52
Pᴇʜ
23.6k62952
23.6k62952
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
asked Nov 21 '18 at 7:53
Thaer JThaer J
133
133
Have you tried a PivotTable?
– Pᴇʜ
Nov 21 '18 at 8:06
I have but the people im working with cannot keep updating the tablet data source every time they add a new account that's why I'm looking for a formula
– Thaer J
Nov 21 '18 at 8:18
Have a look at countifs(), sumifs(), there are examples around on here...
– Solar Mike
Nov 21 '18 at 8:40
A formula won't work you will need VBA, but because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 21 '18 at 8:44
ive tried index with match but the formula only takes the first account number that fits the criteria and ignores the rest, I've tired pivot tables but the people im working for would like it dynamic rather than them wanting to go and change the data source every time, so im pretty much stuck and im not that strong with VBA that's why i came here to ask
– Thaer J
Nov 21 '18 at 9:28
|
show 1 more comment
Have you tried a PivotTable?
– Pᴇʜ
Nov 21 '18 at 8:06
I have but the people im working with cannot keep updating the tablet data source every time they add a new account that's why I'm looking for a formula
– Thaer J
Nov 21 '18 at 8:18
Have a look at countifs(), sumifs(), there are examples around on here...
– Solar Mike
Nov 21 '18 at 8:40
A formula won't work you will need VBA, but because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 21 '18 at 8:44
ive tried index with match but the formula only takes the first account number that fits the criteria and ignores the rest, I've tired pivot tables but the people im working for would like it dynamic rather than them wanting to go and change the data source every time, so im pretty much stuck and im not that strong with VBA that's why i came here to ask
– Thaer J
Nov 21 '18 at 9:28
Have you tried a PivotTable?
– Pᴇʜ
Nov 21 '18 at 8:06
Have you tried a PivotTable?
– Pᴇʜ
Nov 21 '18 at 8:06
I have but the people im working with cannot keep updating the tablet data source every time they add a new account that's why I'm looking for a formula
– Thaer J
Nov 21 '18 at 8:18
I have but the people im working with cannot keep updating the tablet data source every time they add a new account that's why I'm looking for a formula
– Thaer J
Nov 21 '18 at 8:18
Have a look at countifs(), sumifs(), there are examples around on here...
– Solar Mike
Nov 21 '18 at 8:40
Have a look at countifs(), sumifs(), there are examples around on here...
– Solar Mike
Nov 21 '18 at 8:40
A formula won't work you will need VBA, but because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 21 '18 at 8:44
A formula won't work you will need VBA, but because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 21 '18 at 8:44
ive tried index with match but the formula only takes the first account number that fits the criteria and ignores the rest, I've tired pivot tables but the people im working for would like it dynamic rather than them wanting to go and change the data source every time, so im pretty much stuck and im not that strong with VBA that's why i came here to ask
– Thaer J
Nov 21 '18 at 9:28
ive tried index with match but the formula only takes the first account number that fits the criteria and ignores the rest, I've tired pivot tables but the people im working for would like it dynamic rather than them wanting to go and change the data source every time, so im pretty much stuck and im not that strong with VBA that's why i came here to ask
– Thaer J
Nov 21 '18 at 9:28
|
show 1 more comment
2 Answers
2
active
oldest
votes
You can use this array formula (ctrl+shift+enter !!) :{=IFERROR(INDEX(A$1:A$5;SMALL(IF((B$1:B$5>-1000)*($B$1:$B$5<=-500);ROW(A$1:A$5)-ROW(A$1)+1);ROWS(A$1:A1)));"")}
I hard coded the cut-off values, but this would be better of course with a reference to the cells above where you put the cut-off values (so you not only can copy down, but also copy right). In A$1:A$5 are the students/accounts, and in B$1:B$5 their scores. You can copy this formula down in each target column, ideally as far down as there are rows in your original list (just to cover the --probably unlikely-- case that all students' scores are in the same bracket...)
Of course change the ; delimiter with , if needed according to your regional settings.
Credit to this answer on SU : https://superuser.com/a/692580/681891
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
add a comment |
A quick solution would be to enter all your data into each column, and then sort by your criteria. Each column would contain all the data, but only display the desired fields.
No VBA necessary.
answered Nov 21 '18 at 8:59
David GDavid G
8321515
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
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%2f53407436%2fi-want-to-index-specific-values-in-a-specific-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use this array formula (ctrl+shift+enter !!) :{=IFERROR(INDEX(A$1:A$5;SMALL(IF((B$1:B$5>-1000)*($B$1:$B$5<=-500);ROW(A$1:A$5)-ROW(A$1)+1);ROWS(A$1:A1)));"")}
I hard coded the cut-off values, but this would be better of course with a reference to the cells above where you put the cut-off values (so you not only can copy down, but also copy right). In A$1:A$5 are the students/accounts, and in B$1:B$5 their scores. You can copy this formula down in each target column, ideally as far down as there are rows in your original list (just to cover the --probably unlikely-- case that all students' scores are in the same bracket...)
Of course change the ; delimiter with , if needed according to your regional settings.
Credit to this answer on SU : https://superuser.com/a/692580/681891
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
add a comment |
You can use this array formula (ctrl+shift+enter !!) :{=IFERROR(INDEX(A$1:A$5;SMALL(IF((B$1:B$5>-1000)*($B$1:$B$5<=-500);ROW(A$1:A$5)-ROW(A$1)+1);ROWS(A$1:A1)));"")}
I hard coded the cut-off values, but this would be better of course with a reference to the cells above where you put the cut-off values (so you not only can copy down, but also copy right). In A$1:A$5 are the students/accounts, and in B$1:B$5 their scores. You can copy this formula down in each target column, ideally as far down as there are rows in your original list (just to cover the --probably unlikely-- case that all students' scores are in the same bracket...)
Of course change the ; delimiter with , if needed according to your regional settings.
Credit to this answer on SU : https://superuser.com/a/692580/681891
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
add a comment |
You can use this array formula (ctrl+shift+enter !!) :{=IFERROR(INDEX(A$1:A$5;SMALL(IF((B$1:B$5>-1000)*($B$1:$B$5<=-500);ROW(A$1:A$5)-ROW(A$1)+1);ROWS(A$1:A1)));"")}
I hard coded the cut-off values, but this would be better of course with a reference to the cells above where you put the cut-off values (so you not only can copy down, but also copy right). In A$1:A$5 are the students/accounts, and in B$1:B$5 their scores. You can copy this formula down in each target column, ideally as far down as there are rows in your original list (just to cover the --probably unlikely-- case that all students' scores are in the same bracket...)
Of course change the ; delimiter with , if needed according to your regional settings.
Credit to this answer on SU : https://superuser.com/a/692580/681891
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
You can use this array formula (ctrl+shift+enter !!) :{=IFERROR(INDEX(A$1:A$5;SMALL(IF((B$1:B$5>-1000)*($B$1:$B$5<=-500);ROW(A$1:A$5)-ROW(A$1)+1);ROWS(A$1:A1)));"")}
I hard coded the cut-off values, but this would be better of course with a reference to the cells above where you put the cut-off values (so you not only can copy down, but also copy right). In A$1:A$5 are the students/accounts, and in B$1:B$5 their scores. You can copy this formula down in each target column, ideally as far down as there are rows in your original list (just to cover the --probably unlikely-- case that all students' scores are in the same bracket...)
Of course change the ; delimiter with , if needed according to your regional settings.
Credit to this answer on SU : https://superuser.com/a/692580/681891
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
answered Nov 21 '18 at 9:46
Peter K.Peter K.
763212
763212
add a comment |
add a comment |
A quick solution would be to enter all your data into each column, and then sort by your criteria. Each column would contain all the data, but only display the desired fields.
No VBA necessary.
answered Nov 21 '18 at 8:59
David GDavid G
8321515
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
add a comment |
A quick solution would be to enter all your data into each column, and then sort by your criteria. Each column would contain all the data, but only display the desired fields.
No VBA necessary.
answered Nov 21 '18 at 8:59
David GDavid G
8321515
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
add a comment |
A quick solution would be to enter all your data into each column, and then sort by your criteria. Each column would contain all the data, but only display the desired fields.
No VBA necessary.
answered Nov 21 '18 at 8:59
David GDavid G
8321515
A quick solution would be to enter all your data into each column, and then sort by your criteria. Each column would contain all the data, but only display the desired fields.
No VBA necessary.
answered Nov 21 '18 at 8:59
David GDavid G
8321515
answered Nov 21 '18 at 8:59
David GDavid G
8321515
answered Nov 21 '18 at 8:59
David GDavid G
8321515
answered Nov 21 '18 at 8:59
David GDavid G
8321515
8321515
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
add a comment |
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
i understand but that means it will have to be done manually each time, which is why they assigned this project for me, i have to make it dynamic (to sort all results based on the criteria without manual intervention) ive tried index with match but it only takes the first result that applies to the criteria
– Thaer J
Nov 21 '18 at 9:29
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.
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%2f53407436%2fi-want-to-index-specific-values-in-a-specific-range%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
Have you tried a PivotTable?
– Pᴇʜ
Nov 21 '18 at 8:06
I have but the people im working with cannot keep updating the tablet data source every time they add a new account that's why I'm looking for a formula
– Thaer J
Nov 21 '18 at 8:18
Have a look at countifs(), sumifs(), there are examples around on here...
– Solar Mike
Nov 21 '18 at 8:40
A formula won't work you will need VBA, but because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 21 '18 at 8:44
ive tried index with match but the formula only takes the first account number that fits the criteria and ignores the rest, I've tired pivot tables but the people im working for would like it dynamic rather than them wanting to go and change the data source every time, so im pretty much stuck and im not that strong with VBA that's why i came here to ask
– Thaer J
Nov 21 '18 at 9:28