Sorting emails by domains, from org to edu (right char is the most significant)

Hello All
I need to sort the domains according their emails.

For example:

Before sorting:
john1@abc.edu
john3@abc.org
john5@abc.com
john4@bcd.org
john2@bcd.edu
john6@bcd.com

After sorting:
john3@abc.org
john4@bcd.org
john5@abc.com
john6@bcd.com
john1@abc.edu
john2@bcd.edu

That is, how to sort, according to the domain name ( the right is the
most significant )?

Thanks.


Z. D.

0
duzhidian (8)
2/16/2007 7:09:15 AM
excel 39879 articles. 2 followers. Follow

7 Replies
558 Views

Similar Articles

[PageSpeed] 9

On Feb 15, 11:09 pm, "duzhid...@gmail.com" <duzhid...@gmail.com>
wrote:
> That is, how to sort, according to the domain name ( the right is the
> most significant )?

you'll probably need to create some helper columns. use text parsing
functions like SEARCH, LEFT, MID, RIGHT to separate the different
fields you need. then do a multiple sort.

0
2/16/2007 9:37:00 AM
Insert an empty column to the right of your data
Select your column
Data|Text to columns
delimited by other (@)
and skip the first field

Then sort your range using that adjacent column as the primary key.  And the
original column as the secondary key.

"duzhidian@gmail.com" wrote:
> 
> Hello All
> I need to sort the domains according their emails.
> 
> For example:
> 
> Before sorting:
> john1@abc.edu
> john3@abc.org
> john5@abc.com
> john4@bcd.org
> john2@bcd.edu
> john6@bcd.com
> 
> After sorting:
> john3@abc.org
> john4@bcd.org
> john5@abc.com
> john6@bcd.com
> john1@abc.edu
> john2@bcd.edu
> 
> That is, how to sort, according to the domain name ( the right is the
> most significant )?
> 
> Thanks.
> 
> Z. D.

-- 

Dave Peterson
0
petersod (12005)
2/16/2007 2:32:54 PM
I didn't notice that portion.

I'd still use the data|text to columns, but then I'd use another column with
something like:

=if(right(b1,4)=".org",1,if(right(b1,4)=".com",2,if(right(b1,4)=".edu",3,4)))

And then use that numeric column as the primary key, the domain column for the
secondary key and the original column as the tertiary key.



Bruce Sinclair wrote:
> 
> In article <45D5C096.2A1ACF3E@verizonXSPAM.net>, petersod@verizonXSPAM.net wrote:
> >Insert an empty column to the right of your data
> >Select your column
> >Data|Text to columns
> >delimited by other (@)
> >and skip the first field
> >
> >Then sort your range using that adjacent column as the primary key.  And the
> >original column as the secondary key.
> 
> .. and if you want the org/com/edu sort order, you'll probably need a
> custom list as well.
> 
> >"duzhidian@gmail.com" wrote:
> >>
> >> Hello All
> >> I need to sort the domains according their emails.
> >>
> >> For example:
> >>
> >> Before sorting:
> >> john1@abc.edu
> >> john3@abc.org
> >> john5@abc.com
> >> john4@bcd.org
> >> john2@bcd.edu
> >> john6@bcd.com
> >>
> >> After sorting:
> >> john3@abc.org
> >> john4@bcd.org
> >> john5@abc.com
> >> john6@bcd.com
> >> john1@abc.edu
> >> john2@bcd.edu
> >>
> >> That is, how to sort, according to the domain name ( the right is the
> >> most significant )?
> >>
> >> Thanks.
> >>
> >> Z. D.
> >

-- 

Dave Peterson
0
petersod (12005)
2/18/2007 11:34:42 PM
In article <45D5C096.2A1ACF3E@verizonXSPAM.net>, petersod@verizonXSPAM.net wrote:
>Insert an empty column to the right of your data
>Select your column
>Data|Text to columns
>delimited by other (@)
>and skip the first field
>
>Then sort your range using that adjacent column as the primary key.  And the
>original column as the secondary key.

... and if you want the org/com/edu sort order, you'll probably need a 
custom list as well.

>"duzhidian@gmail.com" wrote:
>> 
>> Hello All
>> I need to sort the domains according their emails.
>> 
>> For example:
>> 
>> Before sorting:
>> john1@abc.edu
>> john3@abc.org
>> john5@abc.com
>> john4@bcd.org
>> john2@bcd.edu
>> john6@bcd.com
>> 
>> After sorting:
>> john3@abc.org
>> john4@bcd.org
>> john5@abc.com
>> john6@bcd.com
>> john1@abc.edu
>> john2@bcd.edu
>> 
>> That is, how to sort, according to the domain name ( the right is the
>> most significant )?
>> 
>> Thanks.
>> 
>> Z. D.
>
0
2/19/2007 12:26:01 AM
In article <45D8E292.4CC4962A@verizonXSPAM.net>, petersod@verizonXSPAM.net wrote:
>I didn't notice that portion.
>
>I'd still use the data|text to columns, but then I'd use another column with
>something like:
>
>=if(right(b1,4)=".org",1,if(right(b1,4)=".com",2,if(right(b1,4)=".edu",3,4)))
>
>And then use that numeric column as the primary key, the domain column for the
>secondary key and the original column as the tertiary key.

Code the helper column ... yep ... that will work ... and is probably a 
better long term solution. Custom lists can be a pain, popping up when they 
are least expected :)

Thanks


>Bruce Sinclair wrote:
>> 
>> In article <45D5C096.2A1ACF3E@verizonXSPAM.net>, petersod@verizonXSPAM.net
> wrote:
>> >Insert an empty column to the right of your data
>> >Select your column
>> >Data|Text to columns
>> >delimited by other (@)
>> >and skip the first field
>> >
>> >Then sort your range using that adjacent column as the primary key.  And the
>> >original column as the secondary key.
>> 
>> .. and if you want the org/com/edu sort order, you'll probably need a
>> custom list as well.
>> 
>> >"duzhidian@gmail.com" wrote:
>> >>
>> >> Hello All
>> >> I need to sort the domains according their emails.
>> >>
>> >> For example:
>> >>
>> >> Before sorting:
>> >> john1@abc.edu
>> >> john3@abc.org
>> >> john5@abc.com
>> >> john4@bcd.org
>> >> john2@bcd.edu
>> >> john6@bcd.com
>> >>
>> >> After sorting:
>> >> john3@abc.org
>> >> john4@bcd.org
>> >> john5@abc.com
>> >> john6@bcd.com
>> >> john1@abc.edu
>> >> john2@bcd.edu
>> >>
>> >> That is, how to sort, according to the domain name ( the right is the
>> >> most significant )?
>> >>
>> >> Thanks.
>> >>
>> >> Z. D.
>> >
>
0
2/19/2007 12:38:44 AM
On 15 Feb 2007 23:09:15 -0800, "duzhidian@gmail.com" <duzhidian@gmail.com>
wrote:

>Hello All
>I need to sort the domains according their emails.
>
>For example:
>
>Before sorting:
>john1@abc.edu
>john3@abc.org
>john5@abc.com
>john4@bcd.org
>john2@bcd.edu
>john6@bcd.com
>
>After sorting:
>john3@abc.org
>john4@bcd.org
>john5@abc.com
>john6@bcd.com
>john1@abc.edu
>john2@bcd.edu
>
>That is, how to sort, according to the domain name ( the right is the
>most significant )?
>
>Thanks.
>
>
>Z. D.

If you want to do this using a formula approach, you could download and install
Longre's free morefunc.xll add-in from  http://xcell05.free.fr/

Then use this **array** formula (enter the formula by holding down
<ctrl><shift> while hitting <enter>.  Excel will place braces {...} around the
formula).

=INDEX(VSORT(rng,MID(rng,FIND(".",rng,FIND("@",rng))
+1,255),1,MID(rng,FIND("@",rng)+1,-1+FIND(".",rng,
FIND("@",rng))-FIND("@",rng)),1),ROWS($1:1))

Copy/drag down as far as necessary.
--ron
0
ronrosenfeld (3122)
2/19/2007 1:41:23 AM
On Sun, 18 Feb 2007 20:41:23 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On 15 Feb 2007 23:09:15 -0800, "duzhidian@gmail.com" <duzhidian@gmail.com>
>wrote:
>
>>Hello All
>>I need to sort the domains according their emails.
>>
>>For example:
>>
>>Before sorting:
>>john1@abc.edu
>>john3@abc.org
>>john5@abc.com
>>john4@bcd.org
>>john2@bcd.edu
>>john6@bcd.com
>>
>>After sorting:
>>john3@abc.org
>>john4@bcd.org
>>john5@abc.com
>>john6@bcd.com
>>john1@abc.edu
>>john2@bcd.edu
>>
>>That is, how to sort, according to the domain name ( the right is the
>>most significant )?
>>
>>Thanks.
>>
>>
>>Z. D.
>
>If you want to do this using a formula approach, you could download and install
>Longre's free morefunc.xll add-in from  http://xcell05.free.fr/
>
>Then use this **array** formula (enter the formula by holding down
><ctrl><shift> while hitting <enter>.  Excel will place braces {...} around the
>formula).
>
>=INDEX(VSORT(rng,MID(rng,FIND(".",rng,FIND("@",rng))
>+1,255),1,MID(rng,FIND("@",rng)+1,-1+FIND(".",rng,
>FIND("@",rng))-FIND("@",rng)),1),ROWS($1:1))
>
>Copy/drag down as far as necessary.
>--ron


Oh, dummy me.  Since you'd be downloading morefunc anyway, the following is
more efficient and does NOT need to be entered as an array formula:

=INDEX(VSORT(rng,REGEX.MID(rng,"[^\.]+$"),1,REGEX.MID(rng,"@[^\.]+",1)),ROWS($1:1))

--ron
0
ronrosenfeld (3122)
2/19/2007 2:46:59 AM
Reply:

Similar Artilces:

CRM email router 04-04-04
I have the crm server,exchange and email router installed on one machine But it's not clear to me how the email router should function if i create an email activity in crm and send the mail to another crm user, everything seems to work fine, an activity is created in crm with a GUID and a mail is received on the recipients side When i reply to this mail (using simply outlook), nothing happens? Should the router not intercept this (my CRMEmailEnabled flag is set correctly), is this because i'm sending messages between two crm users? or because i'm not using the sales client for ou...

Message Options (Delayed Emails) #3
Hi all, I have a user on my network who uses the Message Options often to delay emails in the Outbox to a specific time. The problem is only with this user. Users OS is 2000 Pro/Office XP (Outlook 2002) and is not working offline. Server is Exchange server 5.5 LDAP not POP3. (For example, the user can write an email at 1pm and click send and the email will sit in the Outbox until the specified time the user has set in the Message Options dialog box.) The problem is that sometimes the messages work and sometimes they dont. Has anyone experienced this before? Thanks. ...

computer updated lost all old emails and files on outlook
recently had my computer cleaned of viruses and updated with new programs including a newer version of outlook .. seem to have lost all my files containing my emails - 6 years !!!.. Need to know if they are stored on computer or via a server ,.. How do iget them back .. please help !!!!! thanks Ashley "Ashley marshall" <Ashley marshall@discussions.microsoft.com> wrote in message news:BD5B4FAA-C552-44F7-B1F9-B89376F80D9A@microsoft.com... > recently had my computer cleaned of viruses and updated with new programs > including a newer version of outlook...

Shared email address
This is a multi-part message in MIME format. ------=_NextPart_000_006E_01C62CC6.258185F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a pretty common issue that I would like some advice on: I have a support email address and many people working in tech support. I'm sure many other companies have the same requirements. I read an article which suggested the following: 1) Create a public folder and assign it the support@ email addy. 2) Create an agent that converts incoming messages to a custom form with = a bit of scri...

Email Address Recognition 03-01-10
Good morning. Looking for help in getting Outlook to recognize names in network address book. Received help last week advising to make sure the "Auto Name Recognition" box is checked. In this case, it is. What is the next step? Devid "Devid" <Devid@discussions.microsoft.com> wrote in message news:BA8385DF-4229-4928-8199-F8E52A6BFF63@microsoft.com... > Looking for help in getting Outlook to recognize names in network address > book. Received help last week advising to make sure the "Auto Name > Recognition" box is checked. ...

"Open With" issue when right-clicking a file
When I right-click on a GIF image (or mp4 file--that's another example) and select "Open With," QuickTime Player appears twice! Listed as choices are Internet Explorer, QuickTime Player, Paint, MS Digital Image Suite, QuickTime Player (again!), Windows Picture and Fax Viewer, and Jasc Paint Shop Photo Album 5. What could be wrong, and how can I fix it? Thanks and Happy New Year. If QuickTime is the default in both cases, simply open QT and deselect these file types, as applicable. You'll find this option in the Preferences section. "saganized" &l...

how do you sort ascend two columns alphabetically
i am trying to sort ascend two columns in order at the same time. Hi, select both columns, as first level choose first column in ascending sorting and as 2nd level tell to sort in ascending order column 2 "sherryc525" wrote: > i am trying to sort ascend two columns in order at the same time. ...

Duplicate Emails #39
I have a user with Windows XP Pro, SP2, all updates, has office 2003 with SP3 who just recently installed Outlook 2003 and it's set his default mail reader. It's a POP3 account. AVG anti-virus, and the email scanner is *not* installed or active. Using the Windows firewall. He receives duplicate messages of all incoming email. When he closes it and goes to Outlook Express, he only receives the one copy of the mail. I would appreciate any thoughts you can give. Thanks, Tom On Feb 12, 2:59 pm, "Tom [Pepper] Willett" <t...@youreadaisyifyoudo.com> wrote: > He ...

Change the domain name
To give you some info you may need we have a domain server and exchange with AD on that as well (both servers are running 2000). Our company is changing our name from abc.com to xyz.com. So far what i have read you can't change your internal domain name without starting over from scratch. However when we email to people outside the company I need it to say it came from xxx@xyz.com not xxx@abc.com what is any easy way to do this without starting over from scratch. Set up xyz.com MX record in external DNS. In the recipient policies, you can assign an SMTP address of @xyz.com to...

Sending email not using Global Address List?
I want to be able to send email for a specific user in the Global Address list using an SMTP address. Everytime we send email to tha person, it goes directly to his inbox. We would like for this message to go to the .com address, not the X.400. How can I accomplish this? Thank you. Keith You are going to have to give us a bit more detail for us to understand what it is you are trying to do. What version of exchange? How is it set up? Do you have SMTP addresses for everyone? Is the SMTP address the default address, or the X.400? What app are you sending from? etc... "Keith ...

email purchase order check box on vendor card
Does anyone know what the email purchase order does on the vendor card? One of my coworkers discovered the check box and asked me what it did and I was at a loss. I didn't find anything on customer source that told me what module it tied into or what it did. I am assuming it will email the purchase order automatically if it is checked but want to make sure I know what it does before I experiment with it. Robert Fann Omni Packaging Could you describe exactly where you found this checkbox (under what field where)? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global B...

Problem Sending a Document as an Email
I work in Publisher 2003 and I understand I am able to send a document I've created as an email. However, I go to File-Send Email-only to find that the option Send Document as Email doesn't exist. The only option under Send Email is Send Document as Attachment. How do I fix this and send a single page document as an Email? Please help!!! Thank you! cpadams wrote: > How do I fix this and send a single page document as an Email? Please help!!! Do you have Outlook 2003 or Outlook Express 6 installed and set as your default email client? -- Ed Bennett - MVP Microsoft Publi...

It dosen't deliver messages for specified domains
We use MS-Exchange 2000 with service Pack 3 and no NDR Messages I read similar problems before ....but there was no solution No one can slove this problem? ------------------------------------------] Just stacked up in message queue folder Excluding some domains another messages has being delivered well post ur problem in deatail thanks "Adolf" <Adolf@discussions.microsoft.com> wrote in message news:6E69B505-4470-428D-9C1A-23C5FB96876A@microsoft.com... > We use MS-Exchange 2000 with service Pack 3 > and no NDR Messages > I read similar problems before ....but th...

New emails show beyond next month date
Hi guys As of yesterday, my new emails are showing a date of from 4 August 2010 and today they started showing a date of 9 August 2010. This is hapening to a few clients of my clients as well and Im not sure what is causing it. All the email acounts are on POP and I have checked the system date on all the machines affected and all the machines have the correct dates on them. What could be causing this? -- Poodle Did you check the date on the actual mail server? "Poodle" <Poodle@discussions.microsoft.com> wrote in message news:F58EDFCC-0503-410B-AA9B-27AC...

Exchange 2007
Hi there, please help me. I have Exchchange 2007 (final-tryout) and 4 accepted authoritative domain with 4 email address policies (each domain have one of email address policy). My users has 4 smtp addresses (user@domain1, user@domain2 ...). Default email address policy (priority 1) is domain1. So if I'm trying to an send email from user@domain1, everything is ok. But if I'm trying to send an email from user@domain2 (or domain3 or domain4) that outlook said me something like "You are not authorized to send email as this user". But if I change default email address po...

user shift-delete emails
A user shift-deleted some emails. What is the fastest way to recover this? On Tue, 6 Mar 2007 16:42:12 -0800, "mcp" <none@none.com> wrote: >A user shift-deleted some emails. What is the fastest way to recover this? > http://support.microsoft.com/kb/886205 This did not help. The emails do not appear in recover deleted items after shift-delete. "Andy David {MVP}" <adavid@pleasekeepinngcheesebucket.com> wrote in message news:7r4su21s7ug17dkfq7v4sippi6oiki61vn@4ax.com... > On Tue, 6 Mar 2007 16:42:12 -0800, "mcp" <none@none.com> wro...

Sort by one column then another.
I want to do two sorts one on column "B" and then by column "E" for a specific range. Below is the sort for the first sort. How do I do a nested sort along whith the below sort? ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range _ ("B6:B100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A6:E100") ...

What am I not doing right...?
I've created a simple database using Access 2002 tracking donations made by individuals and donations made by Corporations. These donations are being used to rebuild the old arena in town so I created two separate tables - one for Corporations and the other for Inidividuals using the 'Amount' field as the primary key in both tables. I can total up the donations made by corporations and I can total up the donations made by individuals using the =Sum([Amount]) expression in the Report Footer. When they asked me to total up ALL the donations made by both groups I can't fi...

a good algorithm for sorting texts?
Hi, I am trying to find a good sorting algorithm to sort words in a sentence alphabetically? can anyone give me some help? many thanks DAXU wrote: > Hi, > I am trying to find a good sorting algorithm to sort words in a > sentence alphabetically? can anyone give me some help? Array.Sort() is a good place to start. Then, you might look at List<T>.Sort(). http://msdn.microsoft.com/en-us/library/system.array.sort.aspx http://msdn.microsoft.com/en-us/library/3da4abas.aspx If you can define "words" better, then it's possible a suggestion can be pro...

how do i sort the colums in alphabetical order
how do i sort the colums in alphabetical order after the document has been created? I haven't tried this but it may work. Create the columns in Word and sort them. Then do a sort followed by copy/paste into Publisher columns (a text box for each column or create a table). -- Don Quid Pro Quo. "Barry Harper" <Barry Harper@discussions.microsoft.com> wrote in message news:CDC1414A-29D2-4C95-855E-0CA5E0B36677@microsoft.com... > how do i sort the colums in alphabetical order after the document has been > created? ...

Sorting data based on quarters of the year
Hi Not quite sure of the best way to approach this problem I need to filter the data in the table below into quarters Jan-Mar Apr-Jun Jul-Sept Oct-Dec For each quarter i need to find the change in values,i need to subtrac the open price in each quarter(Jan,Apr,Jul,Oct) from the closing pric at the end of each quarter(Mar,Jun,Sept,Dec) So for the latest quarter shown at the top of the table Apr-Jun(04), the open in April would be 4,385.70 which i would like to subtract fro the close in June 4,487 , 4,487-4,385.70 = 101.3 So for Apr-Jun(04) the market gained 101.3 Date-----Open-- Close...

Purging Multiple Exchange Domains from Single Exchange/Domain Network
Greetings, Hopefully someone will be able to shed some light on an issue that I have. I have a Windows 2003 Small Business Server with Exchange on it. I have a single Forest, single Domain - Domain A. Approx 6 months ago, Company A/Domain A decided to lease out some of their office space to another company. At that time, Domain A decided to host email for said Company, Company B. I configured the Exchange Server to host email for Domain A & Domain B. Users of Domain B used a Domain A Login Account to access their email. Recently, it was decided to separate the two companies. Company B we...

CRM 3.0 with AD in different domain
Is it possible to install CRM 3.0 with SQL Server 2000 in one domain, and have it pointing to an Active Directory in another domain? This is for a development environment so the setup does not need to be supported. No Exchange install is required. Tom ...

Outlook 2002 delayed emails
We have a user that is having problems. When she sends an email it takes longer then the rest of the users on the network to arrive and when she receives emails it takes long then the other users. Mainly she is the only one having the problem. Her time on her emails are different then the time on the computer also. Could someone point me in the right directions to help resolve her problem? Thanks in advance! On Tue, 03 May 2005 08:10:03 -1000, Niki Glore <wonika@hotmail.com> wrote: > We have a user that is having problems. When she sends an email it takes > longer t...

Legitimate email getting deleted!!
My boss receives many emails from the office manager. Suddenly, in the last week, all emails from her have gone into the deleted items folder. We have looked everywhere for a rule that might be doing this. He has a junk mail rule and an adult senders rule, and no others. He adds names to these as he gets them but we searched each list and her name is not there. So I created a rule that says when items are received form her, stop processing any more rules, and put this at the top. Email is still going to the deleted items folder. What is going on here? How can I fix this for him? Thanks, ...