Discovery Gaming Community

Greetings. I need help with Excel 2007.

I have a table, which offers a wide variety of materials, in this case, pipes.
Right now it is being sorted by the sizes, but I have a small problem.
I need it to be sorted by the largest sizes first, but you can see the problem:





Right now it sorts by size pretty well, the top ones are 80*something*something
Then the 70 ones come, then the 50s, 40s, and so on. But once we reach 20s, we can see that it seems to sort by the first two numbers. So such sizes as 219 are sorted lower than 80.

Is there any way I can fix this without modifying anything in the text?

(01-28-2013, 08:43 AM)sindroms Wrote: [ -> ]Greetings. I need help with Excel 2007.

I have a table, which offers a wide variety of materials, in this case, pipes.
Right now it is being sorted by the sizes, but I have a small problem.
I need it to be sorted by the largest sizes first, but you can see the problem:





Right now it sorts by size pretty well, the top ones are 80*something*something
Then the 70 ones come, then the 50s, 40s, and so on. But once we reach 20s, we can see that it seems to sort by the first two numbers. So such sizes as 219 are sorted lower than 80.

Is there any way I can fix this without modifying anything in the text?

I don't think Excel can fix that problem for ya.

Excel sees the content of a cell either as TEXT or as a VALUE (or as an amount of money... or as a date.. etc.)

The column you show in the screenshot clearly contain TEXT values, and will be sorted as such. ( with TEXT 21,3 goes before 206 ). Maybe some freak dude can make you a macro that will help you out... but I doubt it...
He (or she) would have to evaluate the string in each cell of the column, let's say : SELECT all chars starting from the LEFT, untill you read "mm", then evaluate the VALUE of that substring... something like that.

Hence : Visual Basic - programmer wanted :-)

Good luck !

OK there is a simple way to solve this or coplicated way (but more sofisticated).

Since i dont have access to english version of excell i wont be able to explain the hard way so the easy way is:

Create an extra column.

In extra column use function LEFT (use excell creator to proper construct the function) extract first 3 symbols
Function shuld look like ths (a1 is cell where you store information ): LEFT(a1;3)
This will extract first 3 symbols counting from left from a1 cell

Now use Find and replace to eleminate unwanted symbols (* empty spaces)

And now you should have colmn with numbers only.

Hope this helps.

OK now iam at home and have access to open office with english version of formula names (they should be the same as excell).

I made two assumptions:

1. The value you looking for always end with * or <space>
If this is wrong then dont bother to read more....

2. The value you looking for is always in the beggining of text
Same as above....

3. The number you need to sorting is not longer then 3 digits
This can be adjusted.

Lets start then

You will need 3 extra columns for calculations (to avoid loops).
For my calculations i assumend that: column A contains raw data, column B first step of calculation, column C second, column D third.

Step one:
Use function FIND to determinate exat location of * symbol:
=FIND("*";A2)
Iam not sure but sometimes you need to put ~ before like this "~*" so the system will know that this is a symbol not a mask.

If this is correct then you will recive number when data conatins * symbol and error when not (thats ok)

Step two:

Use the IF function to determinate that B column contains number if not search for <space> symbol, if true use second IF to check the value: if equal or smaller 4 then copy value from B column, if bigger search for <space> symbol:

=IF(ISNUMBER(B2);IF(B2<=4;B2;FIND(" ";A2));FIND(" ";A2))

If the number is longer then 3 digits change bolded.

Step three:

Again use IF function to apply LEFT function based on number in C column or display info DATA ERR:

=IF(C2<=4;LEFT(A2;C2-1);"DATA ERR")

If the number is longer then 3 diggits change bolded.

Now you should have in column D raw numbers that you can use for sorting

As you can see you dont need to be a VB programmer

(01-28-2013, 08:30 PM)fader Wrote: [ -> ]As you can see you dont need to be a VB programmer

Hehe.. IF this works I will have to admit that indeed...