sorting excel data

L

leonliev

hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp
 
G

Guest

one method would be to copy the column of data paste it to the right of data
set with common rows. select the new column and data-text to column and use
"-" as your delimiter.
select all and sort on the two new columns

you could also write a macro if you have to do it very often
 
G

Guest

You might be interested in resolving the basic issue....

Select the range of cells
[Ctrl]+F............shortcut for <edit><find>
Find what: *-?...asterisk then a dash then a question mark
Check: Match entire cell contents
Click [Find All]
[Ctrl]+A........To select ALL matched cells

While those cells are selected...
Click the Replace tab
Find what: -........That's just a single dash (-)
Replace with: -0...Dash followed by a zero
UNcheck: Match entire cell contents
Click [Replace All]

Now your list looks like this (and sorts properly)
60000-10
60000-11
60000-12
60000-02
60000-03
60000-04
60000-05
60000-06
60000-07
60000-08
60000-09

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
G

Guest

I like your method It would be easily expandable to greater than -100

additionally after the sort, it could go backwards to initial format by
find -0 repace by -


Ron Coderre said:
You might be interested in resolving the basic issue....

Select the range of cells
[Ctrl]+F............shortcut for <edit><find>
Find what: *-?...asterisk then a dash then a question mark
Check: Match entire cell contents
Click [Find All]
[Ctrl]+A........To select ALL matched cells

While those cells are selected...
Click the Replace tab
Find what: -........That's just a single dash (-)
Replace with: -0...Dash followed by a zero
UNcheck: Match entire cell contents
Click [Replace All]

Now your list looks like this (and sorts properly)
60000-10
60000-11
60000-12
60000-02
60000-03
60000-04
60000-05
60000-06
60000-07
60000-08
60000-09

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


[email protected] said:
hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp
Click to expand...
Click to expand...
 
G

Guest

Thanks, BJ....I'm glad you liked it.


***********
Best Regards,
Ron

XL2002, WinXP


bj said:
I like your method It would be easily expandable to greater than -100

additionally after the sort, it could go backwards to initial format by
find -0 repace by -


Ron Coderre said:
You might be interested in resolving the basic issue....

Select the range of cells
[Ctrl]+F............shortcut for <edit><find>
Find what: *-?...asterisk then a dash then a question mark
Check: Match entire cell contents
Click [Find All]
[Ctrl]+A........To select ALL matched cells

While those cells are selected...
Click the Replace tab
Find what: -........That's just a single dash (-)
Replace with: -0...Dash followed by a zero
UNcheck: Match entire cell contents
Click [Replace All]

Now your list looks like this (and sorts properly)
60000-10
60000-11
60000-12
60000-02
60000-03
60000-04
60000-05
60000-06
60000-07
60000-08
60000-09

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


[email protected] said:
hi everybody,

my question is as follows, i'll try to explain it using a current
situation I've had.

I've created a semi-numerological list (numbers with other
characters), consisting of the following data:

60000-1
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9
60000-10
60000-11
60000-12

If you let excel sort this dataset (even if you format the cells as
numbers), it sorts as follows:

60000-1
60000-10
60000-11
60000-12
60000-2
60000-3
60000-4
60000-5
60000-6
60000-7
60000-8
60000-9

Is there a way to let excel sort this dataset correctly (as the top
list, that means)?

thanks in advance,
Leon van Gurp
Click to expand...
Click to expand...
Click to expand...
 
L

leonliev

Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon
 
G

Guest

what does your macro say?

[email protected] said:
Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron
Click to expand...
Click to expand...
 
L

leonliev

The visual basic code is:

Selection.Replace What:="-", Replacement:="-0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

I can see what's going wrong, but I have no idea how I can correct
this...
Any help is welcome

Thnx,
Leon
 
G

Guest

Don't look for Excel to record *that* macro properly <g>

Try this:

'--------Start of Code-------
Option Explicit

Sub FixDashNums()
Dim rngSelection As Range
Dim cCell As Range

Set rngSelection = Selection

With rngSelection
If .Cells.Count = 1 Then
If MsgBox( _
Title:="Please Confirm", _
Prompt:="Only 1 cell selected...." _
& "OK to fix ALL cells in the worksheet?", _
Buttons:=vbQuestion + vbYesNo) _
= vbNo Then
Exit Sub
End If
Else
For Each cCell In .Cells
If cCell.Value Like "*-?" Then
cCell.Replace What:="-", Replacement:="-0"
End If
Next cCell
End If
End With

End Sub
'--------End of Code-------

To run it....Select the range to be impacted...
Then.....[Alt]+[F8]....Select "FixDashNums"....Click [Run]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


[email protected] said:
Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron
Click to expand...
Click to expand...
 
L

leonliev

Ron,

for today: you're my hero!

it works just fine. Thanks a lot, this saves me tons of time\work!

best regards,
Leon
 
G

Guest

Well!....I sure munged up that code!
I started out intending to use Find/Replace....then changed my mind and with
with iterative code. (sorry)

This is what I SHOULD have posted:

'--------Start of Code-------
Option Explicit

Sub FixDashNums()
Dim rngSelection As Range
Dim cCell As Range

Set rngSelection = Selection

With rngSelection
For Each cCell In .Cells
If cCell.Value Like "*-?" Then
cCell.Replace What:="-", Replacement:="-0"
End If
Next cCell
End With

End Sub
'--------End of Code-------

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Ron Coderre said:
Don't look for Excel to record *that* macro properly <g>

Try this:

'--------Start of Code-------
Option Explicit

Sub FixDashNums()
Dim rngSelection As Range
Dim cCell As Range

Set rngSelection = Selection

With rngSelection
If .Cells.Count = 1 Then
If MsgBox( _
Title:="Please Confirm", _
Prompt:="Only 1 cell selected...." _
& "OK to fix ALL cells in the worksheet?", _
Buttons:=vbQuestion + vbYesNo) _
= vbNo Then
Exit Sub
End If
Else
For Each cCell In .Cells
If cCell.Value Like "*-?" Then
cCell.Replace What:="-", Replacement:="-0"
End If
Next cCell
End If
End With

End Sub
'--------End of Code-------

To run it....Select the range to be impacted...
Then.....[Alt]+[F8]....Select "FixDashNums"....Click [Run]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


[email protected] said:
Thanks Ron!

this seems to work nicely. Only problem I have now, is that if i try
to record this method into a macro, that _all_ my data receive their
extra '0' after the dash, so in some way the selection method for
numbers with only 1 digit behind the dash is not recorded into the
macro (ie: 60000-12 becomes 60000-012, which is not supposed to
happen). Can anybody help me out with that?

thanks in advance,
Leon

You might be interested in resolving the basic issue....

[snip]

Is that something you can work with?
***********
Regards,
Ron
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

the problem is earlier in the code, in that the selection must have all
selected, not just the ones with one character after the dash
 
L

leonliev

As I said, I have no idea how to write these things, but I can
somewhat read them. The latter looks more logical, but both work just
as well in the few small tests I ran them through.

well, thanks again!
Leon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sorting logical lists 3
Index, Match 1
Help to write the macros given a set of data 2
sumif with multiple conditions 1
Summing all rows with certain specification 2
Problem with if statement 12
acess sum 7
help with error 1

Top