Sort issue

  • Thread starter Thread starter Patty
  • Start date Start date
P

Patty

I have a sorting issue that I need help with, I do not
know if it is possible to do this and I have tried a ton
of different things, so I decided to see if anyone out
here has an idea.

I have 5 Random numbers (ie.(1-5-4-6-6, 5-5-5-3-6)) I want
to put these in order from high to low (sort decending),
but if the number is displayed more than once i want to
push it to the bottom or the string to look like this (6-5-
4-6-1, 6-5-3-5-5)???

Any ideas if this is possible?
Patty
 
Hi

See if this helps

Assuming the first of your numbers is in the cell D4

Dim rngend
rngend = Range("D4").End(xlDown).Address
Range("D4:" & rngend).Sort Key1:=Range("D4"), _
Order1:=xlDescending

Dim i As Integer
For i = 4 To 8
If Range("d" & i).Value = Range("d" & i) _
..Offset(1, 0).Value Then
Range("d" & i).End(xlDown).Offset(1, 0) = Range("d" & i)
Range("d" & i).Delete shift:=xlUp
End If
Next
End Sub
 
Patty,

If you go to Tools >> Options >> Custom Lists tab and
play around with organizing the list how you want.

Then when you go to Data >> Sort >> Options... select
the list you created, it can only be changed for the
first sort by field if you were wondering, if this
doesn't work you will have to turn to a macro. Do
a search for multiple conditions search or something
and you should find an article from Microsoft's Knowledge
Base that gives more details and an example macro.

The short answer is, unfortunately: No.

But if you put some more details maybe we can come
up with a work around.

Keep digging,

::h::
 
Looks like Heath is giving you a bum steer. A customized list would have no
role to play in the scenario you described. Just to save you a little time.
 
when you save you have 5 random numbers, where are they located. Each set
of 5 in a separate cell. How are the stored in the cell - do they include
the parentheses or is it 5 numbers like 1-5-4-6-6

are the numbers all single digit.

If you had 2-2-3-3-1-1 how would you want the numbers sorted
1-2-3-1-2-3 ? In other words, for numbers pushed to the bottom of the
list, how are they to be sorted or would there only ever be one repeated
number.
 
See response below.

Tom Ogilvy said:
Looks like Heath is giving you a bum steer. A customized list would have no
role to play in the scenario you described. Just to save you a little time.
Click to expand...

I don't know how I could be giving her a bum steer when
I clearly said it wasn't possible. I suggested it assuming
that she didn't know it existed. And it could be useful or
if she sees that it is possible she could change the ways
she is organizing the data.

With the limited details I still think it is a good tip.
Sorry if it isn't up to your standards.

Maybe this is unclear as an answer, if so I apologize for
not being more specific.
 
Certainly it is possible to implement in code.

And as I said, custom lists have no role to play in a the solution.

you said: "Keep digging,"

Which implies that your suggestions might lead to an answer. In my opinion,
that is a bum steer.
 
Although I've never implemented a sorting method *in excel VBA*, from
other programming languages, this should be simple. Not sure why VBA
would have a harder time with it ...

I'd do my sorting algorithm like this (for a real slow sort -- use a
better sort method please, i'm just being fast here)

Note: this may not be so different than what Libby wrote out in VBA
code directly. Try that first. :)


Code:
--------------------

dim array
array = list to be sorted
dim length as integer
length = length of array
dim X as integer, Y as integer
For X from 2 to length
Y = X
While (item Y of array is greater than item Y-1 of array) and (Y is greater than 1) do
Exchange Y with Y-1
decrement Y by 1
end While loop
if item Y is greater than 1 then
if item Y equals item Y-1 then
move item Y down the list, one spot at a time, until it is at item Length
decrement Length by one (so you don't try to sort this one again)
end if #2
end if #1
end For loop

--------------------


You'd end up, after this code, with a sorted list like this:
from 2532431
to
5432123
with the latter 2 and 3 in no order.
** edit: Actually, i'm lying here. You'd get 5432132, since each one
is placed in reverse order -- ie the first duplicate at the end, the
second duplicate at the (end-1) spot, etc. :)

If you want to sort that part of the list, then AFTER this sorting
algorithm, add another one (directly after the end For loop line)

Code:
--------------------

dim length2 as integer
length2=array.length
if length is not equal to length2 then
for X = (length+1) to (length2) do
Y = X
While (item Y of array is greater than item Y-1 of array) and (Y is greater than 1) do
Exchange Y with Y-1
decrement Y by 1
end While loop
end For loop
end If

--------------------


You'd end up now with
4325213
to
5432132
and
43235213
to
54321332

Not necessary of course if you don't care how the extras are sorted.
This method also has the convenient ability to 'cut' out the extras --
ie if the reason you want it sorted but dup's at the end is that you
don't want them, then it's easy to arrange, since the 'length' variable
is already correct for the length of your unique array, and you can
either just copy the original array to a new one using something like
for X from 1 to length do array2.X = array1.X
and it will stop when it gets to the end of the unique array (as long
as you don't end up losing your defined Length array) ... could also
probably just manually delete it, dunno how in VBA though. :)

-Joe
 
Tom

All numbers are in different cells E34-E38.
They are only single digits from 1-6
There can be multiple repeated numbers. Like (6-6-6-5-4, 6-
5-5-5-4, 6-6-6-6-5, 1-1-2-2-2, 4-4-4-4-6), Again each of
the 5 numbers are some what random soo the possibilities
could be endless.

Thanks for your help.
Patty
 
in F34 put in

=IF(COUNTIF($E$34:E34,E34)=1,1,2)
Then drag fill that down to F38

Select E34:F38 and sort with the first key being
Column F, Ascending
second key being
Column E, Descending
 
Back
Top