Sorting problem with negative value (excel 2000)

Topic

New Reply

Hello guys …long time no see..:-))

I need your guys help once more..

I got a sheet that count marks for my student

the problem is when i sort the student grades..it will

separate them to 2 part..i mean theres a gap between the list..

i think this ocuured becaise i give negative value to student who get D

and E grades..

i want the names to be continous like when i sort them by their names..thanks

please refer at the attachment

Reply | Quote

Replies

The problem is that sort column can contain 4 types of values. They can contain numbers: people can have scores >0, scores =0, and scores >0. All of these will sort as desired. The problem is the “blank names” will all have a score of 0 so the blanks will always come before negative scores (not what you want).

The easisest fix is to not include the blank names in the sort, but to only include the scores with names. In your macros you select the entire range:

Range("b6:ah48").Select

If you, instead, only select the range with names:

Range(Range("AH6"), Range("B65536").End(xlUp)).Select

You will not have the problem.

This range selects AH6 as one corner, and the corner from the last occupied cell in col B.

Steve

Reply | Quote

The problem is that sort column can contain 4 types of values. They can contain numbers: people can have scores >0, scores =0, and scores >0. All of these will sort as desired. The problem is the “blank names” will all have a score of 0 so the blanks will always come before negative scores (not what you want).

The easisest fix is to not include the blank names in the sort, but to only include the scores with names. In your macros you select the entire range:

Range("b6:ah48").Select

If you, instead, only select the range with names:

Range(Range("AH6"), Range("B65536").End(xlUp)).Select

You will not have the problem.

This range selects AH6 as one corner, and the corner from the last occupied cell in col B.

Steve

Reply | Quote

Hi NiuB,

You are correct that the reason is the negative values.

The ‘blank’ cells are sorted as zeroes, so you need to exclude them from the sort. You can do this by changing the Range you Select for the Sort.

Instead of ..

Range(“b6:ah48”).Select

.. try using something like ..

Range(“b6”, Columns(“ah:ah”).Find(“*”, , xlValues, , , xlPrevious)).Select

Enjoy,
Tony

Reply | Quote

(Edited by sdckapr on 18-Apr-04 06:43. Thought of another failure to add)

One comment on this method.

It works because the spreadsheet has the options to not display any zero values at all (tools – options – View – “zero values”) which “effectively” turns all zeroes in the spreadsheet as “non-values” and does not display them.

If the zeroes had been “hidden” in another way (eg with formatting color) it would not work, since the formulas will otherwise yield a value (0) that is just not displayed.

Steve
PS the method will also fail if the person if the last name in the list has a zero score, since the zero will not be listed and will not be included in the sort since the so Mr or Ms “Z..” will be last in the Grade sort as well as the name sort since he/she will not be included in the sort.

Reply | Quote

Right on both counts, Steve

Reply | Quote

Thanks to you guys both..sdckapr and TonyJollans ..

So far so good..but I’ve still not tested it with multiple condition yet..

If any problem occured again..I will post again..cheers

Reply | Quote

Welcome back NiuB,

Try changing your code to the following :

Sub SusunGredBerlian()

Dim strPwd As String
Dim intCustList As Integer

strPwd = “”

With Application
.ScreenUpdating = False
.AddCustomList ListArray:=Array(“1”, “-1”, “0”)
intCustList = .CustomListCount
End With

With Sheets(“berlian”)
.Unprotect Password:=strPwd
.Range(“b6:ah48”).Sort _
Key1:=Range(“ah6”), _
Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=intCustList + 1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Protect Password:=strPwd
End With

With Application
.DeleteCustomList ListNum:=intCustList
.ScreenUpdating = True
End With

End Sub

Andrew C

Reply | Quote

Hiii back andrew..its been quite sometime since i come here..

thanks andrew..the code works like charm..:-))

Reply | Quote

Hiii back andrew..its been quite sometime since i come here..

thanks andrew..the code works like charm..:-))

Reply | Quote

Welcome back NiuB,

Try changing your code to the following :

Sub SusunGredBerlian()

Dim strPwd As String
Dim intCustList As Integer

strPwd = “”

With Application
.ScreenUpdating = False
.AddCustomList ListArray:=Array(“1”, “-1”, “0”)
intCustList = .CustomListCount
End With

With Sheets(“berlian”)
.Unprotect Password:=strPwd
.Range(“b6:ah48”).Sort _
Key1:=Range(“ah6”), _
Order1:=xlDescending, _
Header:=xlYes, _
OrderCustom:=intCustList + 1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Protect Password:=strPwd
End With

With Application
.DeleteCustomList ListNum:=intCustList
.ScreenUpdating = True
End With

End Sub

Andrew C

Reply | Quote

Thanks to you guys both..sdckapr and TonyJollans ..

So far so good..but I’ve still not tested it with multiple condition yet..

If any problem occured again..I will post again..cheers

Reply | Quote

Right on both counts, Steve

Reply | Quote

(Edited by sdckapr on 18-Apr-04 06:43. Thought of another failure to add)

One comment on this method.

It works because the spreadsheet has the options to not display any zero values at all (tools – options – View – “zero values”) which “effectively” turns all zeroes in the spreadsheet as “non-values” and does not display them.

If the zeroes had been “hidden” in another way (eg with formatting color) it would not work, since the formulas will otherwise yield a value (0) that is just not displayed.

Steve
PS the method will also fail if the person if the last name in the list has a zero score, since the zero will not be listed and will not be included in the sort since the so Mr or Ms “Z..” will be last in the Grade sort as well as the name sort since he/she will not be included in the sort.

Reply | Quote

Hi NiuB,

You are correct that the reason is the negative values.

The ‘blank’ cells are sorted as zeroes, so you need to exclude them from the sort. You can do this by changing the Range you Select for the Sort.

Instead of ..

Range(“b6:ah48”).Select

.. try using something like ..

Range(“b6”, Columns(“ah:ah”).Find(“*”, , xlValues, , , xlPrevious)).Select

Enjoy,
Tony

Reply | Quote