?
Solved

Sort serverlist in excel

Posted on 2013-01-31
7
Medium Priority
?
210 Views
Last Modified: 2013-02-15
Please advise howto sort a serverlist in excel, f.e. Server01, server02, ... server11 ....
Now it sorts server01, server11...
Then duplicates should also be removed.

Thanks.
J
0
Comment
Question by:janhoedt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38839244
In Excel 2007+, you can use /Data/Remove Duplicates to get a distinct list.  In Excel 2003, use the Advanced Filter.

For the sorting, if the naming convention is ALWAYS "Server##", with a two digit number at the end, then use formulas to separate the parts:

=LEFT(A2,LEN(A2)-2)                   <--- this is the base server name
=VALUE(RIGHT(A2,2))                 <--- this is the server number

Then sort your list based on those new columns.

If that naming convention is not always followed, then you need to be more specific about your requirements.
0
 

Author Comment

by:janhoedt
ID: 38839429
Thanks. Servernames have sometimes 3 digits, f.e. server01, server100. Please advise on that.
0
 

Author Comment

by:janhoedt
ID: 38839470
And also please advise howto seperate within excel.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Accepted Solution

by:
karunamoorthy earned 2000 total points
ID: 38839669
separate server10 as server + 10 and server100 as server+100, use like this

suppose a1 cell contains server10 then in A2 cell write
=mid(a1,7,10) you will get 10
a2 cell contains server100 then you will get 100
Hope you got it.
0
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 38848223
Pl post you comments here for further assistance!
0
 

Author Comment

by:janhoedt
ID: 38848552
No, sorry, don't get it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question