Hi,
There may well be an easy, obvious answer to this, but I cannot see it.
I have a list of numbers followed by a letter and would like them to be displayed in ascending order in a column. The full range - and the order I would like them to appear - is: 1A, 1B, 2A, 2B, 3A, 3B, 4A, 4B, 5A, 5B, 6A, 6B, 7A, 7B, 8A, 8B, 9A, 9B, 10A, 10B, 11A, 11B, 12A, 12B. What I am finding is that 10A/B, 11A/B and 12A/B are being placed above 1A/B in the list.
For a bit of background, for DJing purposes I am trying to produce a spreadsheet of all my music tracks, sorted by increasing tempo, then by increasing musical key (see http://www.harmonic-mixing
I'd be grateful for any ideas.
Thanks in advance.
Phil
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Phil,
From the sample you gave you need to put a leading zero in front of the value to have it sort correctly. The other way is to make a seperate column with a formula in it that achieves the right sort order. One way that allows for odd combinations such as 7A/6A is to use the formula:
=IF(ISNUMBER(VALUE(MID(E2,
starting in cell F2 and fill down with your values in column E. Then sort column F by the numbers.
Hi Phil,
Using Bert01's suggestion with the inclusion of 7A/6A & 10A/11A, after sorting ("Sort anything that looks like a number, as a number") the end result is...
1A
1B
2A
2B
3A
3B
4A
4B
5A
5B
6A
6B
7A
7B
7A/6A <- Is this position correct?
8A
8B
9A
9B
10A
10A/11A
10B
11A
11B
12A
12B
Should the 7A/6A entry be immediately after 7A?
If not, please let us know & I/we can offer alternate suggestion(s).
BFN,
fp.
(Saurabh: Your suggestion does not seem to cater for the 7A/6A & 10A/11A entries)
fp shamed me into producing a better formula, so you can try this one as well. It just puts a zero in front of the values that start with a one digit number which should solve the problem fp mentioned. Assuming your list is in column a put this formula in cell B2 and fill down:
=IF(NOT(ISNUMBER(VALUE(MID
Thank you everyone that posted here; I am really grateful for the time you have spent helping me to do this.
Yes, I did require the 7A/6A entry immediately after 6A, so the last formula works a treat (it does not matter that it displays as 7A/6A as opposed to 6A/7A, as the software that keys the music identifies and displays the primary key first - 7A in this case).
I've attached the original information from the keying software and the end result after implementing your formula. Would you mind explaining how it works to me, please? Rounding a column is about the limit of my understanding of formulas! Is there a way of getting the formulas to extend down the column whenever data is entered in subsequent rows? At the moment I've dragged them down to row 1000.
Many thanks all.
Best wishes
Phil
Phil,
Thank you for the points. There are definately different formulas that could be used to achieve the same result, but I know this one will work for you.
The best way to see what the formula does is to break down each part into it's own cell and see the result. To explain it:
A. MID - takes cell A1, starts reading at the 2nd position, for a length of 1 character.
B. VALUE - tries to convert the results of (A) to a number. Necessary because Exel reads the results of (A) as text even if it is a number.
C. ISNUMBER - checks to see if (B) is a number. If it is, it returns TRUE, otherwise it is FALSE.
D. NOT - I could have left this out if I constructed my IF statement differently (see below). As I did it, I wanted the recipical of TRUE or FALSE to get the proper value for my IF statement. So NOT turns TRUE to FALSE or FALSE to TRUE.
E. IF - If result of (A-D) is TRUE, 0 (zero), if FALSE, "" (nothing)
F. & - Concatenation sign - think of it as putting two words together. It either puts nothing and cell A2 together, or it puts 0 and cell A2 together.
G. A2 - cell A2.
Different way for formula without NOT - =IF(ISNUMBER(VALUE(MID(A2,
The best way to keep the cells in the row is to just drag down like you did. Excel does not automatically fill in formulas from above if you insert a line. This is a common question and it takes VBA programming to do this.
Bert
Business Accounts
Answer for Membership
by: saurabh726Posted on 2008-06-16 at 15:35:34ID: 21798125
Hi Phil...
You can do something like this in B Column and sort..and this will do what you are looking for...
HTH...
Saurabh...
Sort...