I copy and paste numerical order numbers from the web onto a spreadsheet
and sort them.

I usually use a Macro to do this but recently the numbers don't sort
properly anymore.

It seems like some of these numbers are actually text but even after I
format the cells to 'Number' they still do not sort.

I believe I've copied over some format or other with the number but
even copy / paste special values doesn't seem to help.

Can anyone tell me what the score is here.

Below is an example of the numbers I'm trying to sort:


90721
90728
90731
90826
90851
90872
1002894
90709
90738
90851
90878
90903


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=530501

Try the following to convert text to number. Enter 1 in some
empty cell and copy it. Then select your range of data. Go to the
Edit menu, choose Paste Special and select the Multiply
operation. Click OK.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Daminc"
wrote in message
...

I copy and paste numerical order numbers from the web onto a
spreadsheet
and sort them.

I usually use a Macro to do this but recently the numbers don't
sort
properly anymore.

It seems like some of these numbers are actually text but even
after I
format the cells to 'Number' they still do not sort.

I believe I've copied over some format or other with the number
but
even copy / paste special values doesn't seem to help.

Can anyone tell me what the score is here.

Below is an example of the numbers I'm trying to sort:


90721
90728
90731
90826
90851
90872
1002894
90709
90738
90851
90878
90903


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread:
http://www.excelforum.com/showthread...hreadid=530501

I found this:


Code:
--------------------
Sub ConvertToNumbers()
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 1).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
With Selection
.VerticalAlignment = xlTop
.WrapText = False
End With
Selection.EntireColumn.AutoFit
End Sub
--------------------


which seems like it clears it up but I don't know why.

Any advice?


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=530501

Hi Chip, thanks for the advice.

your solution worked. For me to use it in a macro I'll have to put in
the value 1 in a cell somewhere and then clear it after the
transformation has been completed.

After looking at your solution the one I posted makes a little more
sense but it failed (my one) when I tried to run it a second time which
is odd.


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=530501

I thought I'd put in the completed solution here in case it might help
someone else:



Code:
--------------------
Sub find_duplicates()
'
' find_duplicates Macro
' Macro recorded 07/07/2005 by PreeceJ
'

'
Application.Run "'Validation sheet.xls'!sort_for_duplicates"
Range("K1").Select
Application.Run "'Validation sheet.xls'!FindDups"
End Sub



Sub sort_for_duplicates()
'
' sort_for_duplicates Macro
' Macro recorded 07/07/2005 by PreeceJ
'

Range("K:K").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("'Validation sheet.xls'!saleID").Select

'The saleID range I've put in as a dynamic range on the excel sheet
'InsertNameDefine
'Type in the name of the range (in this case saleID)
'In the box "Refers to" type in
' =OFFSET(worksheetname!$K$1,0,0,COUNTA(worksheetnam e!$K$1:$K$65),1)
'(change the name and range to suit your needs)

Application.Run "'Validation sheet.xls'!ConvertToNumbers"

Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub


Sub ConvertToNumbers()

Range("n1") = 1
Range("n1").Copy
Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("n1") = ""
With Selection
.VerticalAlignment = xlTop
.WrapText = False
End With

End Sub


Sub FindDups()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell < ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub
--------------------


hope this helps ;)


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=530501