Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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:: ---------------------------------------------------------- =if(solution=not(macro),Finesse,Brute) "Patty" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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. -- Regards, Tom Ogilvy "Heath" wrote in message om... 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:: ---------------------------------------------------------- =if(solution=not(macro),Finesse,Brute) "Patty" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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. -- Regards, Tom Ogilvy "Patty" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
See response below.
"Tom Ogilvy" wrote in message ... 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. 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. -- Regards, Tom Ogilvy "Heath" wrote in message om... 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. Maybe this is unclear as an answer, if so I apologize for not being more specific. But if you put some more details maybe we can come up with a work around. Keep digging, ::h:: ---------------------------------------------------------- =if(solution=not(macro),Finesse,Brute) "Patty" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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. -- Regards, Tom Ogilvy "Heath" wrote in message om... See response below. "Tom Ogilvy" wrote in message ... 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. 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. -- Regards, Tom Ogilvy "Heath" wrote in message om... 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. Maybe this is unclear as an answer, if so I apologize for not being more specific. But if you put some more details maybe we can come up with a work around. Keep digging, ::h:: ---------------------------------------------------------- =if(solution=not(macro),Finesse,Brute) "Patty" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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 --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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 -----Original Message----- 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. -- Regards, Tom Ogilvy "Patty" wrote in message ... 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort issue
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 -- Regards, Tom Ogilvy "Patty" wrote in message ... 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 -----Original Message----- 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. -- Regards, Tom Ogilvy "Patty" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Issue | Excel Discussion (Misc queries) | |||
SORT/FILTER ISSUE | Excel Worksheet Functions | |||
Macro to sort - Issue | Excel Discussion (Misc queries) | |||
Simple sort issue | Excel Discussion (Misc queries) | |||
Sort issue | Excel Worksheet Functions |