Sort list into tuples in Excel
Is there any way to sort a list into tuples as defined by a priority list in Excel?
For example I would do a draw to get priorities:
Banana -> 1
Apple -> 2
Tomato -> 3
And I have sheet of randomly ordered data:
Tomato
Banana
Banana
Tomato
Apple
Tomato
Banana
Apple
Banana
Banana
Now I want to sort into tuples by the priority list to get this result:
Banana
Apple
Tomato
Banana
Apple
Tomato
Banana
Tomato
Banana
Banana
The tuples are complete to the extent values exist in the data list.
microsoft-excel sorting
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
add a comment |
Is there any way to sort a list into tuples as defined by a priority list in Excel?
For example I would do a draw to get priorities:
Banana -> 1
Apple -> 2
Tomato -> 3
And I have sheet of randomly ordered data:
Tomato
Banana
Banana
Tomato
Apple
Tomato
Banana
Apple
Banana
Banana
Now I want to sort into tuples by the priority list to get this result:
Banana
Apple
Tomato
Banana
Apple
Tomato
Banana
Tomato
Banana
Banana
The tuples are complete to the extent values exist in the data list.
microsoft-excel sorting
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
Welcome to Super User! It's not clear to me how these priorities are being applied. Can you explain how the priorities lead to that output?
– Excellll
Nov 1 '15 at 1:59
With or without VBA?
– Raystafarian
Nov 2 '15 at 16:02
add a comment |
Is there any way to sort a list into tuples as defined by a priority list in Excel?
For example I would do a draw to get priorities:
Banana -> 1
Apple -> 2
Tomato -> 3
And I have sheet of randomly ordered data:
Tomato
Banana
Banana
Tomato
Apple
Tomato
Banana
Apple
Banana
Banana
Now I want to sort into tuples by the priority list to get this result:
Banana
Apple
Tomato
Banana
Apple
Tomato
Banana
Tomato
Banana
Banana
The tuples are complete to the extent values exist in the data list.
microsoft-excel sorting
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
Is there any way to sort a list into tuples as defined by a priority list in Excel?
For example I would do a draw to get priorities:
Banana -> 1
Apple -> 2
Tomato -> 3
And I have sheet of randomly ordered data:
Tomato
Banana
Banana
Tomato
Apple
Tomato
Banana
Apple
Banana
Banana
Now I want to sort into tuples by the priority list to get this result:
Banana
Apple
Tomato
Banana
Apple
Tomato
Banana
Tomato
Banana
Banana
The tuples are complete to the extent values exist in the data list.
microsoft-excel sorting
microsoft-excel sorting
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
edited Nov 1 '15 at 2:11
fixer1234
18.6k144782
18.6k144782
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
asked Nov 1 '15 at 1:54
uuuubbbbuuuubbbb
61
61
Welcome to Super User! It's not clear to me how these priorities are being applied. Can you explain how the priorities lead to that output?
– Excellll
Nov 1 '15 at 1:59
With or without VBA?
– Raystafarian
Nov 2 '15 at 16:02
add a comment |
Welcome to Super User! It's not clear to me how these priorities are being applied. Can you explain how the priorities lead to that output?
– Excellll
Nov 1 '15 at 1:59
With or without VBA?
– Raystafarian
Nov 2 '15 at 16:02
Welcome to Super User! It's not clear to me how these priorities are being applied. Can you explain how the priorities lead to that output?
– Excellll
Nov 1 '15 at 1:59
Welcome to Super User! It's not clear to me how these priorities are being applied. Can you explain how the priorities lead to that output?
– Excellll
Nov 1 '15 at 1:59
With or without VBA?
– Raystafarian
Nov 2 '15 at 16:02
With or without VBA?
– Raystafarian
Nov 2 '15 at 16:02
add a comment |
1 Answer
1
active
oldest
votes
Assuming your "randomly ordered data" started at B1, put these formulas :
C1 ----> =COUNTIF($B$1:B1,B1)+INDEX({1,2,3},MATCH(B1,{"Banana","Apple","Tomato"},0))/10
D1 ----> =RANK(C1,C:C,1)
F1 ----> =ROW()
G1 ----> =INDEX(B:B,MATCH(F1,D:D,0))
and drag downwards. done.
idea : use rank() with countif() & index-match to generate the intended major & minor sequence(s).
Hope it solves. ( :
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f994505%2fsort-list-into-tuples-in-excel%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
Assuming your "randomly ordered data" started at B1, put these formulas :
C1 ----> =COUNTIF($B$1:B1,B1)+INDEX({1,2,3},MATCH(B1,{"Banana","Apple","Tomato"},0))/10
D1 ----> =RANK(C1,C:C,1)
F1 ----> =ROW()
G1 ----> =INDEX(B:B,MATCH(F1,D:D,0))
and drag downwards. done.
idea : use rank() with countif() & index-match to generate the intended major & minor sequence(s).
Hope it solves. ( :
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
add a comment |
Assuming your "randomly ordered data" started at B1, put these formulas :
C1 ----> =COUNTIF($B$1:B1,B1)+INDEX({1,2,3},MATCH(B1,{"Banana","Apple","Tomato"},0))/10
D1 ----> =RANK(C1,C:C,1)
F1 ----> =ROW()
G1 ----> =INDEX(B:B,MATCH(F1,D:D,0))
and drag downwards. done.
idea : use rank() with countif() & index-match to generate the intended major & minor sequence(s).
Hope it solves. ( :
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
add a comment |
Assuming your "randomly ordered data" started at B1, put these formulas :
C1 ----> =COUNTIF($B$1:B1,B1)+INDEX({1,2,3},MATCH(B1,{"Banana","Apple","Tomato"},0))/10
D1 ----> =RANK(C1,C:C,1)
F1 ----> =ROW()
G1 ----> =INDEX(B:B,MATCH(F1,D:D,0))
and drag downwards. done.
idea : use rank() with countif() & index-match to generate the intended major & minor sequence(s).
Hope it solves. ( :
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
Assuming your "randomly ordered data" started at B1, put these formulas :
C1 ----> =COUNTIF($B$1:B1,B1)+INDEX({1,2,3},MATCH(B1,{"Banana","Apple","Tomato"},0))/10
D1 ----> =RANK(C1,C:C,1)
F1 ----> =ROW()
G1 ----> =INDEX(B:B,MATCH(F1,D:D,0))
and drag downwards. done.
idea : use rank() with countif() & index-match to generate the intended major & minor sequence(s).
Hope it solves. ( :
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
answered Jan 16 at 3:58
p._phidot_p._phidot_
633312
633312
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f994505%2fsort-list-into-tuples-in-excel%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
Welcome to Super User! It's not clear to me how these priorities are being applied. Can you explain how the priorities lead to that output?
– Excellll
Nov 1 '15 at 1:59
With or without VBA?
– Raystafarian
Nov 2 '15 at 16:02