How can you set up several sorting rules in Excel to sort a column?
Clash Royale CLAN TAG#URR8PPP
up vote
3
down vote
favorite
I got a list of mail addresses, let's say we have the list
thisisanexample@home.com
anotherthing@whatever.com
hello.world@idontknow.com
default@idk.com
youleave@whatever.com
nomoreideas@sorry.com
example@g.com
another_option@home.com
efg@idk.com
In Excel, how can I set up 2 rules of sorting? I like the first sort rule say that we sort alphabetically after the @ symbol.
The second sorting rule says that if the part after the @ symbol is same, then sort the part before the @ alphabetically.
So basically, the list above would look like that if we applied these 2 sorting rules:
example@g.com
another_option@home.com
thisisanexample@home.com
default@idk.com
efg@idk.com
hello.world@idontknow.com
nomoreideas@sorry.com
anotherthing@whatever.com
youleave@whatever.com
I know how to set up the first rule in Excel at least. It's done by selecting the column to be sorted, copying it twice to another two columns, then in the second column select all mail addresses, press "Text in Columns" the seperate the mails when you see the symbol @. Then you have in the very first column the actual mail address, the second column you have the part before the @, the third column you have the part after the @. Now just let the column of the after @ part sort and you are done.
But I have no idea how to take both rules into account at the same time...? :S
I really hope my question is clear because else I will have a very long day tomorrow at work :P
microsoft-excel worksheet-function sorting
add a comment |Â
up vote
3
down vote
favorite
I got a list of mail addresses, let's say we have the list
thisisanexample@home.com
anotherthing@whatever.com
hello.world@idontknow.com
default@idk.com
youleave@whatever.com
nomoreideas@sorry.com
example@g.com
another_option@home.com
efg@idk.com
In Excel, how can I set up 2 rules of sorting? I like the first sort rule say that we sort alphabetically after the @ symbol.
The second sorting rule says that if the part after the @ symbol is same, then sort the part before the @ alphabetically.
So basically, the list above would look like that if we applied these 2 sorting rules:
example@g.com
another_option@home.com
thisisanexample@home.com
default@idk.com
efg@idk.com
hello.world@idontknow.com
nomoreideas@sorry.com
anotherthing@whatever.com
youleave@whatever.com
I know how to set up the first rule in Excel at least. It's done by selecting the column to be sorted, copying it twice to another two columns, then in the second column select all mail addresses, press "Text in Columns" the seperate the mails when you see the symbol @. Then you have in the very first column the actual mail address, the second column you have the part before the @, the third column you have the part after the @. Now just let the column of the after @ part sort and you are done.
But I have no idea how to take both rules into account at the same time...? :S
I really hope my question is clear because else I will have a very long day tomorrow at work :P
microsoft-excel worksheet-function sorting
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I got a list of mail addresses, let's say we have the list
thisisanexample@home.com
anotherthing@whatever.com
hello.world@idontknow.com
default@idk.com
youleave@whatever.com
nomoreideas@sorry.com
example@g.com
another_option@home.com
efg@idk.com
In Excel, how can I set up 2 rules of sorting? I like the first sort rule say that we sort alphabetically after the @ symbol.
The second sorting rule says that if the part after the @ symbol is same, then sort the part before the @ alphabetically.
So basically, the list above would look like that if we applied these 2 sorting rules:
example@g.com
another_option@home.com
thisisanexample@home.com
default@idk.com
efg@idk.com
hello.world@idontknow.com
nomoreideas@sorry.com
anotherthing@whatever.com
youleave@whatever.com
I know how to set up the first rule in Excel at least. It's done by selecting the column to be sorted, copying it twice to another two columns, then in the second column select all mail addresses, press "Text in Columns" the seperate the mails when you see the symbol @. Then you have in the very first column the actual mail address, the second column you have the part before the @, the third column you have the part after the @. Now just let the column of the after @ part sort and you are done.
But I have no idea how to take both rules into account at the same time...? :S
I really hope my question is clear because else I will have a very long day tomorrow at work :P
microsoft-excel worksheet-function sorting
I got a list of mail addresses, let's say we have the list
thisisanexample@home.com
anotherthing@whatever.com
hello.world@idontknow.com
default@idk.com
youleave@whatever.com
nomoreideas@sorry.com
example@g.com
another_option@home.com
efg@idk.com
In Excel, how can I set up 2 rules of sorting? I like the first sort rule say that we sort alphabetically after the @ symbol.
The second sorting rule says that if the part after the @ symbol is same, then sort the part before the @ alphabetically.
So basically, the list above would look like that if we applied these 2 sorting rules:
example@g.com
another_option@home.com
thisisanexample@home.com
default@idk.com
efg@idk.com
hello.world@idontknow.com
nomoreideas@sorry.com
anotherthing@whatever.com
youleave@whatever.com
I know how to set up the first rule in Excel at least. It's done by selecting the column to be sorted, copying it twice to another two columns, then in the second column select all mail addresses, press "Text in Columns" the seperate the mails when you see the symbol @. Then you have in the very first column the actual mail address, the second column you have the part before the @, the third column you have the part after the @. Now just let the column of the after @ part sort and you are done.
But I have no idea how to take both rules into account at the same time...? :S
I really hope my question is clear because else I will have a very long day tomorrow at work :P
microsoft-excel worksheet-function sorting
microsoft-excel worksheet-function sorting
asked Aug 28 at 19:16
cnmesr
142119
142119
add a comment |Â
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
5
down vote
accepted
You're almost there, you need to setup a second level of sorting (by clicking on "Add level":
The sorted e-mails:
Note that each level's sort order is independent of the other levels
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
add a comment |Â
up vote
1
down vote
You can set up cell B1 as Mid(A1, Pos(A1, '@') + 1, Len(A1) - Pos(A1, '@') and then copy it down the column. Then copy column B and paste-by-value onto itself. Then sort using column B (email domains) as top level and column A as second level.
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
5
down vote
accepted
You're almost there, you need to setup a second level of sorting (by clicking on "Add level":
The sorted e-mails:
Note that each level's sort order is independent of the other levels
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
add a comment |Â
up vote
5
down vote
accepted
You're almost there, you need to setup a second level of sorting (by clicking on "Add level":
The sorted e-mails:
Note that each level's sort order is independent of the other levels
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
add a comment |Â
up vote
5
down vote
accepted
up vote
5
down vote
accepted
You're almost there, you need to setup a second level of sorting (by clicking on "Add level":
The sorted e-mails:
Note that each level's sort order is independent of the other levels
You're almost there, you need to setup a second level of sorting (by clicking on "Add level":
The sorted e-mails:
Note that each level's sort order is independent of the other levels
answered Aug 28 at 19:34
cybernetic.nomad
810110
810110
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
add a comment |Â
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :)
â cnmesr
Aug 28 at 20:47
add a comment |Â
up vote
1
down vote
You can set up cell B1 as Mid(A1, Pos(A1, '@') + 1, Len(A1) - Pos(A1, '@') and then copy it down the column. Then copy column B and paste-by-value onto itself. Then sort using column B (email domains) as top level and column A as second level.
add a comment |Â
up vote
1
down vote
You can set up cell B1 as Mid(A1, Pos(A1, '@') + 1, Len(A1) - Pos(A1, '@') and then copy it down the column. Then copy column B and paste-by-value onto itself. Then sort using column B (email domains) as top level and column A as second level.
add a comment |Â
up vote
1
down vote
up vote
1
down vote
You can set up cell B1 as Mid(A1, Pos(A1, '@') + 1, Len(A1) - Pos(A1, '@') and then copy it down the column. Then copy column B and paste-by-value onto itself. Then sort using column B (email domains) as top level and column A as second level.
You can set up cell B1 as Mid(A1, Pos(A1, '@') + 1, Len(A1) - Pos(A1, '@') and then copy it down the column. Then copy column B and paste-by-value onto itself. Then sort using column B (email domains) as top level and column A as second level.
answered Aug 29 at 1:10
Ralph
111
111
add a comment |Â
add a comment |Â
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1353183%2fhow-can-you-set-up-several-sorting-rules-in-excel-to-sort-a-column%23new-answer', 'question_page');
);
Post as a guest
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
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
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