Software

Ask something
Question Locked

Problem kicking my ass - EXCEL sort issue

0 helpful
12 replies

by laura.giacchino

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I cannot find any way to fix this problem. I have hundreds of numbers in an 'outline' type format and I cannot get a correct sort no matter what I try. I have tried every format possible. Does anyone know a work around?? Here is what I want to achieve:
1.1
1.1.1
1.2
1.3
1.3.1
1.4.2
1.5
2.1
8.5
8.5.1
8.5.10
12.1
12.3.10
Here is what I get:
3.10
4.20
8.40
10.10
12.20
10.2.2
12.1.1
12.3.10
3.6.1
3.6.10
3.6.2
8.5.10
8.5.15
8.5.7
9.3.2

12 replies

by thumbsup2

The numbers with only one decimal are auto-formatted as a number. The ones with two decimal places are auto-formatted as text. Since a sort will always place numbers before text and will always sort text beginning with 10 before 12 and those before text beginning with 3, your sort turns out looking a bit strange.

There is only one work around that I am aware of and it's going to be time consuming to set it up.

First, create a new column to the left of where your numbers are now located. Now, strip your existing numbers out into 3 different columns where those numbers with only one decimal place will have a 3rd column containing a 0 (zero). For example using your first number, put a 3 in one column, a 10 in the next and a zero in the third. You can put these three columns way to the right of your data for now and hide them later. Now delete the entire column with your original numbers in it.

In that new column you created, type the following formula (change the cell reference to match the 3 columns you put your numbers into).

=CONCATENATE(D18,".",E18,".",F18)

Copy that formula down to the bottom of the data.

Now, highlight the entire area of the spreadsheet which has any data in it, including that new column and the 3 columns containing your numbers.

Sort on the first, second and third column of where you stripped the numbers out to. Using my example above of the concatenate formula, you can see that I would sort on column D first, E second and F third.

Now, highlight the 3 columns (D, E and F), right click and choose HIDE. They're no longer visible, the concatenated data appears correct according to your number scheme and your data is sorted by outline numbering.

:D

by laura.giacchino

I wanted to take it one step further and add an if statement so if there are only two digits it wont append an additional . I can't find any documentation other than describing IF used in any other way than attaching text values to the cell.

=IF(ISBLANK(F236),CONCATENATE(D236,".",E236".",F236),CONCATENATE(D236,".",E236))

by laura.giacchino

As soon as I hit submit post, I realized what cols I should be displaying and sorting on. Works great.

by thumbsup2

Sometimes ya gotta do whatcha gotta do just to make Excel behave itself. I work with it a lot, sorting other people's data which isn't always input in the format I would like it to be in. So, I've found ways to get around the sort restrictions. :D

by laura.giacchino

in implementation, I am running into a problem. if my number is 6.6 and I enter
6.6|6|6| |6.6| Concatenate the positions 2&3 if 4 is blank or 2,3,4 if not blank. This displays properly. However, sorting on positions 2,3,4 gives me the same bad sort. If I add a 0 in column 4, then it sorts right but displays incorrectly.
6.6.0|6|6|0|6.6|
Seems I am back at square one. ??

by thumbsup2

Sorts of blanks or spaces will always sort before any number. Or is it after? I can't remember for sure. In any case, what you can do to make it sort correctly is use a zero in the correct place, but change your IF statement to include another check for equal to zero and simply display a blank where needed. And remember, you're always going to sort on those added columns.

Make sense?

I can write out the entire formula for you, or I can let you try to figure it out on your own. Since most people learn better by doing it themselves, I'll give you the logic and let you write the actual formula.

remember, the if statement goes something like this:

If(test, value if true, value if false)

You can nest another if statement in either value.

if(isblank, do something, if(equal zero, do somethingelse, do anotherthing))

If you need further help, just let us know. :D

by thumbsup2

First, your formula is cut off (on my screen). I can't see past the 2nd occurance of the word concatenate. So, if I get this wrong, please excuse me.

You have written, if F236 is blank, concatenate D236.E236.F236 ... don't you mean concatenate D236.E236 (forget about the 2nd dot and F236, by the way, you're missing a comma). Remember, the first statement after the test is what to do if the test is TRUE. It's the second one that tells it what to do if the test is false.

Now, to your question. The test for an IF statement can test for just about anything you want it to test for. Using ISBLANK is but one way to test. You could also write:

=if(f236="", concatenate(perameter if true), concatenate(perameter if false))
shortened for display purposes

or, you could use:

=if(f236<>"",concatenate(perameter if true), concatenate(perameter if false))
shortened for display purposes

There are many other ways to test the contents of a cell and it doesn't have to be a test for text as in ISBLANK. You could test for length. You could test for less than or greater than zero. Etc... etc... etc...

Hope that made sense.

This discussion is closed to new replies.