• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting doubt

S

shyjuu

New Member
I want to sort column according to second word of my cell

for example if I have two cell values Mr SHYJU and Mr Helper, how to sort this values based on second word
 
Good day shyjuu


I think a little bit more of how your data is set out and how you want the data sort ,highest,lowest, word, would be of help
 
Thanks for showing interest in solving my Query bobhc

I want to sort it in alphabetic order, for example Mr Helper should be first and then Mr SHYJU, because H comes before S, how to do this
 
Shyjuu


Select all the column data and then while your mouse is in the selection right click and from the drop down choose sort and then in the list choose A-Z
 
bobhc


If suppose we have Mr and Miss as our first word than this wont work, I want to know how to sort according to second word
 
shyjuu


You could go to filter, custom and then create a custom list via the options button such as, "Mr,Mrs,Miss" and then use that as your filter,
 
Hi Shyjuu,


Do you mind if I use a Helper Column.. :)

[pre]
Code: 
LIST			        SNDWORD  		SORTED
Mrs. shyjuu			shyjuu			Sri aann
Mrs. bobhc			bobhc			Mrs. bhanjic
Mrs. bhanjic			bhanjic			Mrs. bobhc
Mr. shrivallabha		shrivallabha		Mrs. Faseeh
Mrs. Faseeh			Faseeh			Mrs. Faseeh
Sri jberwind			jberwind		Sri Hui
Sri sachinbizboy		sachinbizboy		Sri jberwind
Sri shyjuu			shyjuu			Sri Jediantman
Mrs. sachinbizboy		sachinbizboy		Mr. Linwe
Sri Faseeh			Faseeh			Mr. NARAYANK991
Sri aann			aann			Sri sachinbizboy
Sri Hui			        Hui			Sri sachinbizboy
Mr. NARAYANK991			NARAYANK991		Mr. shrivallabha
Sri Jediantman			Jediantman		Mrs. shyjuu
Mr. Linwe			Linwe			Mrs. shyjuu[/pre]

Col B: create a Helper Column [code]=MID(A2,FIND(" ",A2)+1,255)
Just to fetch the second Word..

In Col C : use formula as

=INDEX(List, MATCH(SMALL(COUNTIF(SndWord, "<"&SndWord), ROW(1:1)), COUNTIF(SndWord, "<"&SndWord), 0))[/code]

Confirm formula by Ctrl + Shift + Enter.. Not just Enter


Please let us know.. if you have any doubt


Courtesy : http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Regards,

Deb


PS: Guys..Mr.. Mrs & Sri.. are used by Choose(Randbetween.. so please dont post any comment regarding Gender.. :)
 
Good evening Debraj Roy


Helper columns did cross my mind but I thought that with a custom filter it would all ways be there in filter.


PS. you left "Master" out of your gender list :)
 
Hye.. Bob..


I have one more trick..

* Copy List Column > Paste in Ms-WORD..

* If not pasted as TABLE then convert to Table.. and Table Tool > Layout > Data > Sort..

or Table Menu > Sort (as per Word Edition)

* In the Sort Page > Go to Option > Separate Field as " "(Space) OK

*
[list type=decimal]
[*]Sort By : LIST
[*] Type : Text
Usig : Second Word
[/list type=decimal]
* OK


Now Copy the Sorted List and paste into EXCEL.. give a try.. you will like it..


Regards,

Deb
 
@b(ut)ob(ut)hc, Debraj Roy

Hi, my friends!

Good afternoon for both.

I was reading the topic title and suddenly and unexpectedly a thought crossed by my mind. I'm always doing what the title says, sorting doubts. But I don't happen to pass them by, just ordered either in ascending or descending orders.

What a deep thought, isn't it?

Regards!
 
Debraj Roy


I am using 2013 and there is no way I could get it to accept the “ “ in the sort options. But I pasted the list in to excel and used the text to columns function then sorted on column A which now had the salutation’s in, worked as required. Sort by B if the sort should be by Surname


PS> I see SirJB7 passed by............but no Carlsbeg
 
@b(ut)ob(ut)hc

Hi, old dog!

Today I switched slightly to...

https://dl.dropbox.com/u/60558749/Nesquik.jpg

https://dl.dropbox.com/u/60558749/Cindor.jpg

https://dl.dropbox.com/u/60558749/Cindor%20royal.png

Anybody'd like to join me?

Regards!
 
Hi Shyjuu,


Thanks for feedback..


BTW.. will you please confirm, which one is helpful.. Excel or the Word one.. :)


Reards,

Deb
 
You must log in or register to reply here.
Back
Top