Sorting in reverse from right to left?

chick-in · May 11th, 2002, 12:54 AM

I have a simple although large list of email addresses in a single column. I need to sort them by their URL instead of alphabetically from the front of the address.

Can anyone tell me how I could sort the list evaluating each cell from the end of the text working inwards to the beginning?

Any help greatly appreciated.

Nimrod · May 11th, 2002, 01:24 AM

In what exact order would you want receive@ntceurope.com sorted.
moc.eporuecnt@eviecer ??

chick-in · May 11th, 2002, 02:30 AM

Well say you had the following in a column:

dave@abc.com
jim@bacon.ws
zippy@rainbow.net
kerry@cow.com
john@abc.com
chris@dog.nz
george@rainbow.net

After the sort macro/function the list would look like this:

dave@chicken.com
john@chicken.com
kerry@cow.com
jim@bacon.ws
george@rainbow.net
zippy@rainbow.net
chris@dog.nz

They are now in order evaluating from the right to left. This has the effect of bunching email addresses together with others of the same URL. Making it easy to see how many people are at the same site.

Quote:

On 2002-05-11 00:24, Nimrod wrote:
In what exact order would you want receive@ntceurope.com sorted.
moc.eporuecnt@eviecer ??

Nimrod · May 11th, 2002, 03:07 AM

If all your email addresses are this simple then you could do it with "text to columns" function and the "sort" function ( both found on the data menu).

If all data is in Row A:A

- copy data to B:B (leaving a copy in A:A as well)
- select B:B and do a "text to columns" with a period as your delimter.
- insert a column between the right column of text "i.e .com" and the left string "i.e. gary@fish"
- select column with left string and do another "text to columns" but this time @ is your delimiter
- select all rows (ie A:D) and use data...sort function
- sort order will be from right most column first ... ie. sort order colD, ColC, ColB

- ColumnA will now be sorted as well

[ This Message was edited by: Nimrod on 2002-05-11 02:08 ]

[ This Message was edited by: Nimrod on 2002-05-11 02:09 ]

Aladin Akyurek · May 11th, 2002, 03:36 AM

Lets say that column A houses the target e-mail addresses:

1. Copy all addresses to column B.
2. Select B, activate Data|Text to Columns with as delimiter "@".
3. Select C, activate Data|Text to Columns with as delimiter ".".
4. Apply VSORT to the data columns available in the Morefunc add-in, downloadable from:

http://longre.free.fr/english/index.html

This is what you get:

Microsoft Excel - aaVSORT.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
F1		=	=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)
*	A	B	C	D	E	F	G	H	I
1	dave@abc.com	dave	abc	com	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>dave@abc.com	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>dave	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>abc	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>com
2	jim@bacon.ws	jim	bacon	ws	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>john@abc.com	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>john	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>abc	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>com
3	zippy@rainbow.net	zippy	rainbow	net	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>kerry@cow.com	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>kerry	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>cow	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>com
4	kerry@cow.com	kerry	cow	com	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>george@rainbow.net	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>george	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>rainbow	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>net
5	john@abc.com	john	abc	com	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>zippy@rainbow.net	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>zippy	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>rainbow	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>net
6	chris@dog.nz	chris	dog	nz	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>chris@dog.nz	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>chris	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>dog	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>nz
7	george@rainbow.net	george	rainbow	net	*	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>jim@bacon.ws	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>jim	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>bacon	:alert('=VSORT(A1:D7,D1:D7,1,C1:C7,1,B1:B7,1,A1:A7,1)')>ws
Sheet1

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.

[ This Message was edited by: Aladin Akyurek on 2002-05-11 02:38 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 11th, 2002, 12:54 AM	#1
chick-in New Member Join Date: May 2002 Posts: 2	I have a simple although large list of email addresses in a single column. I need to sort them by their URL instead of alphabetically from the front of the address. Can anyone tell me how I could sort the list evaluating each cell from the end of the text working inwards to the beginning? Any help greatly appreciated.

May 11th, 2002, 01:24 AM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	In what exact order would you want receive@ntceurope.com sorted. moc.eporuecnt@eviecer ??

May 11th, 2002, 03:07 AM	#4
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	If all your email addresses are this simple then you could do it with "text to columns" function and the "sort" function ( both found on the data menu). If all data is in Row A:A - copy data to B:B (leaving a copy in A:A as well) - select B:B and do a "text to columns" with a period as your delimter. - insert a column between the right column of text "i.e .com" and the left string "i.e. gary@fish" - select column with left string and do another "text to columns" but this time @ is your delimiter - select all rows (ie A:D) and use data...sort function - sort order will be from right most column first ... ie. sort order colD, ColC, ColB - ColumnA will now be sorted as well [ This Message was edited by: Nimrod on 2002-05-11 02:08 ] [ This Message was edited by: Nimrod on 2002-05-11 02:09 ]