Sort problem - decimated number, alphas, AND parentheses

[EKinevel]

Hi:

I have a spreadsheet with a LONG column with values that look like this:

119.49
119.49
119.43(a)
119.43(a)
119.43(a)
119.43(a)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)

That I basically need to sort like this:

119.43
119.43(a)
119.43(a)
119.43(a)
119.43(a)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.47
119.47
119.47

I've tried doing this with Text-to-columns, concatenating values and then re-merging, etc. But I can't get it to work. Essentially the hierarchy should go like this:

Decimated number, no parenthetical value first: 123.45
Followed by: 123.45(a)
Followed by: 123.45(a)(1)
Followed by: 123.45(a)(2)
Followed by: 123.46
Followed by: 123.45(a)
Followed by: 123.45(a)(1)
Followed by: 123.45(b)

And so on. This is proving to be a lot harder than I anticipated. Any help would be greatly appreciated.

Thanks,

-P

[martindwilson]

try in another column =text(a2,"@"), drag down then use that col to sort by

[EKinevel]

First of all, thanks for responding.

That's better, but it's treating my parenthetical values as secondary, like this:

119.43
119.47
119.47
119.47
119.47
119.47
119.47
119.47
119.47
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.43(a)
119.43(a)
119.43(a)
119.43(a)
119.43(b)
119.43(b)

See how it's sorting the 'pure' number values first, then starting again at .43(a). I need it to sort like this:

119.43
119.43(a)
119.43(a)(1)
119.43(b)
119.43(b)(1)
119.47

and so on. Any ideas? Thanks again.

- P

[shg]

Martin's suggestion works perfectly for me:

Please Login or Register  to view this content.

When prompted, select Sort numbers and numbers stored as text separately

[NBVC]

I think, if Martin's suggestion worked, then TEXT to COLUMNs should work too...

i.e. select the column and go to Data|Text to columns.. skip to 3rd window and choose TEXT from the column data format area.

Then sort and when prompted choose to sort numbers and numbers stored as text separately.

[EKinevel]

SHG:

Not sure what's different about the way you're using Martin's formula and the way I'm using it, but this is what I'm getting:

119.43
119.47
119.47
119.47
119.47
119.47
119.47
119.47
119.47
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.43(a)
119.43(a)
119.43(a)
119.43(a)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.43(b)(1)
119.47(a)
119.47(a)
119.47(a)
119.47(a)
119.47(a)
119.47(a)
119.47(a)
119.47(a)
119.47(a)
119.47(b)
119.47(b)
119.47(b)
119.47(b)
119.47(b)
119.47(b)
119.47(b)
119.49(a)
119.49(a)
119.49(a)
119.49(a)
119.49(a)
119.49(a)(1)
119.49(a)(1)
119.49(a)(10)
119.49(a)(10)
119.49(a)(10)
119.49(a)(11)
119.49(a)(12)
119.49(a)(12)
119.49(a)(12)
119.49(a)(12)
119.49(a)(13)
119.49(a)(2)
119.49(a)(2)
119.49(a)(4)
119.49(a)(4)
119.49(a)(5)
119.49(a)(5)
119.49(a)(5)
119.49(a)(6)
119.49(a)(6)
119.49(a)(6)
119.49(a)(6)
119.49(a)(6)
119.49(a)(8)
119.49(a)(8)
119.49(a)(8)
119.49(a)(9)
119.49(a)(9)
119.49(a)(9)
119.49(a)(9)
119.49(a)(9)

As you can see it jumps from 119.43 to 119.47 before going to 119.43(a). I am using the formula and then sorting numbers and numbers stored as text separately.


NBVC - This ALMOST works. The problem is that I'm getting 119.43(a) before I'm getting 119.43:

119.43 (a)
119.43 (a)
119.43 (a)
119.43 (a)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43

Since the (a) is now in the second column after doing text-to-columns, it's not recognizing the null cell as coming first in the order before the (a). Same with the 3rd column.

I appreciate everyone's help. if you have any more suggestions, I'm pretty stumped here, as you can see.

P

[shg]

I'm not sure there's more than one interpretation of Martin's suggestion. See attached.

[NBVC]

NBVC - This ALMOST works. The problem is that I'm getting 119.43(a) before I'm getting 119.43:

119.43 (a)
119.43 (a)
119.43 (a)
119.43 (a)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b) (1)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43 (b)
119.43

Since the (a) is now in the second column after doing text-to-columns, it's not recognizing the null cell as coming first in the order before the (a). Same with the 3rd column.

P

When doing text to columns, in first window, select delimited, then next, next again, then Text.. it should all stay in one column

I always get 119.43 at top.... (ascending order sort)

[EKinevel]

SHG:

Unfortunately, the example in your scratch.xls is also not the way I'm looking for it. Your second column looks like this:

119.43
119.47
119.47
119.47
119.47
119.47
119.47
119.47
119.47
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.49
119.43(a)
119.43(a)

I need the 119.43(a) to appear BEFORE it increments to the next root number (in this case 119.47). Thanks though.

[EKinevel]

NBVC:

OK, that actually works a little better. My problem now becomes the numeric values in the second set of parentheses:

119.49(a)(1)
119.49(a)(1)
119.49(a)(10)
119.49(a)(10)
119.49(a)(10)
119.49(a)(11)
119.49(a)(12)
119.49(a)(12)
119.49(a)(12)
119.49(a)(12)
119.49(a)(13)
119.49(a)(2)
119.49(a)(2)
119.49(a)(4)
119.49(a)(4)
119.49(a)(5)
119.49(a)(5)
119.49(a)(5)
119.49(a)(6)

Since it's formatted as text, it's going to increment them as 1, 10, 11, 2, 20, 22, etc. instead of 1,2,3,4,etc. Any way around that you can think of? In other words, instead of the above, I need it to look like this:


119.49(a)(1)
119.49(a)(2)
119.49(a)(3)
119.49(a)(3)
119.49(a)(4)
119.49(a)(5)
119.49(a)(6)
119.49(a)(7)

and so on. Thanks again.

[shg]

Here's how it sorts when I do it:

Please Login or Register  to view this content.

[martindwilson]

and me same result