Sort by evaluated value and then by actual value

[MishaJames1972]

I have an table with part numbers and quantity numbers.

The quantity numbers may be positive, indicating the quantity in stock, or the quantity may be negative, indicating the amount needed to be ordered, or the quantity number may be zero, indicating both none in stock and none needed.

I need to sort the table by part numbers of those with positive quantity numbers and then sort the part numbers of those with negative part numbers.

Unsorted:

Part# Quantity
1 -50
5 80
9 -7
3 10
7 -100
2 200

Desired Sort:

Part# Quantity
2 200
3 10
5 80
1 -50
7 -100
9 -7

All the Part#s that have a positive quantity are in order by part number and then all the Part#s that have a negative quantity are in order by part number.

And I wish to perform this through VBA.

I think one solution might involve performing some sort of conditional formatting to use as a sort key but I do not want something that will be visible to the users.

By the same token, I think another solution might involve an additional column that contains a formula to evaluate whether the quantity is positive or negative but, again, the column should not be visible or accessible to the user.

Any ideas?

[Bernie Deitrick]

Do a three part sort - assumes that your data is in A:B and headers are in row 1. This does not include error checking in case there are all positive or all negative values, and it treats 0 as negative (you never say)

Please Login or Register  to view this content.