New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Excel sorting, numeric and alphabetic

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Excel sorting, numeric and alphabetic

Postby Roseam30 » Wed May 25, 2016 4:31 pm

Hi, I would really appreciate some help with my sorting in Excel. I have two columns like the below that needs to be sorted numerically and then alphabetically. example: 49A, 49B, 49c, etc. It can be in Code or the sort function.

Thanks

49 A
51 E
55 D
59 C
63 B
49 C
53 B
49 B
51 A
55 B
59 A
51 B
49 C
  • 0

Roseam30
Rookie
 
Posts: 3
Joined: May 25, 2016
Reputation: 0
Excel Version: 2013

Re: Excel sorting, numeric and alphabetic

Postby ManxXL » Thu May 26, 2016 9:50 am

You can sort by more than one column with the sort feature.

Highlight the relevant data, select sort, chose the first column you want to sort by ie the number column, and set the criteria,
then click "Add level" and then select the letter column and set the criteria. then OK.

So your data will be sorted by number and letter.
  • 0

ManxXL
Regular
 
Posts: 47
Joined: May 16, 2013
Reputation: 12
Excel Version: 2010

Re: Excel sorting, numeric and alphabetic

Postby Roseam30 » Thu May 26, 2016 11:04 am

I've tried it that way, it comes out grouped as the following:

10A
10A
10A
11A
11A
11A
11A
11A
11A
12A
12A
12A
12A
12A
12A

I want it to group as: 11A, 11B, 11C, 11D, 11E.

Thank you for your reply.
  • 0

Roseam30
Rookie
 
Posts: 3
Joined: May 25, 2016
Reputation: 0
Excel Version: 2013

Re: Excel sorting, numeric and alphabetic

Postby pecoflyer » Fri May 27, 2016 9:45 am

Do you mean each string (like 10A) is contained in 1 cell ?
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Excel sorting, numeric and alphabetic

Postby Roseam30 » Fri May 27, 2016 12:54 pm

I have tried it both ways. First I tried it using two separate columns and then I joined them together to see If I would get a different result. Neither was successful. :(
  • 0

Roseam30
Rookie
 
Posts: 3
Joined: May 25, 2016
Reputation: 0
Excel Version: 2013

Re: Excel sorting, numeric and alphabetic

Postby vikas270880 » Sat May 28, 2016 2:50 am

Roseam30 wrote:Hi, I would really appreciate some help with my sorting in Excel. I have two columns like the below that needs to be sorted numerically and then alphabetically. example: 49A, 49B, 49c, etc. It can be in Code or the sort function.

Thanks

49 A
51 E
55 D
59 C
63 B
49 C
53 B
49 B
51 A
55 B
59 A
51 B
49 C



I put the above data in cell A1:B13 to resolve your problem. its quite simple. following are the steps:

select the whole data (A1:B13 as per this example)

right click on data\sort\custom sort

the sort window will appear. now sort by column A, Sort on Values and put the Order as Smallest to Largest.

then add another level by clicking on Add Level on top left. Then select column B to sort, sort on values and put the order as A-Z

Clink OK and done.


this is working fine and giving the output as per your need.
  • 0

vikas270880
Rookie
 
Posts: 5
Joined: May 28, 2016
Reputation: 0
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 65 guests