CODEWITHSUNDEEP
excel-2007
monu
monu

Reputation: 211

Sorting Excel sheet based on names

I have an Excel sheet which contains of name, address, and contact number etc. Below is how my Excel sheet looks like:

1. murali
   H.No:xxx
   xxxx street
   xxxx nagar
   xxxx layout delhi
   contact no:xxxx

2. bali
   H.No:yyy
   yyyy nagar
   lucknow
   contact no:yyyy

3. lali
   H.No:zzzz
   zzzz complex
   zzzz street
   besides zzzz
   zzzz nagar
   assam
   contact no:zzzz

How can I sort it based on the names while my address and the rest of the details remain unchanged respectively.

Upvotes: 0

Views: 246

Answers (1)

teylyn
teylyn

Reputation: 35970

[edit]: Wow, the layout after the edit by perror changed the scope of the question completely.

Just make sure there is a header row with column titles. Then turn on Autofilter and sort by the name column.

[end of edit]

An approach without VBA:

Make sure row 1 is empty. Data starts in row 2. From your example above it looks as if the number is in a different column. In my example, the number is in column A, the data in column B. Enter this formula into cell C2 and copy down to the last row of data:

=IF(B1="",LEFT(B2,1)&TEXT(LEFT(A2,FIND(".",A2&".")-1),"00000"),C1)

I have added another data set with the name "murali" for illustration purposes, so there are two names that start with murali.

enter image description here

Now copy column C and paste it back into column C using Paste Special > Values.

Add column headers into cells A1 to C1, then fire up the autofilter with Alt-d-f-f or the ribbon command and sort A to Z by column C.

The result is:

enter image description here

Now you can delete column C.

Upvotes: 1

Related Questions