Sorting Collection
 

I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) < UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

Thanks for the help, but when I tried that code I got a subscript out
of range error on this line:

If UCase(nodupes(lLoop2)) < UCase(nodupes(lLoop)) Then

any ideas why? Thanks.

- David

Sorting Collection
 

Can you post the rest of the code please? Specifically how you dim the
Collection and get names into it.

Charles

David wrote:
Thanks for the help, but when I tried that code I got a subscript out
of range error on this line:

If UCase(nodupes(lLoop2)) < UCase(nodupes(lLoop)) Then

any ideas why? Thanks.

- David

Sorting Collection
 

Here you go.

On Error Resume Next
For g = 1 To 669
If ActiveSheet.Cells(g, 1) < Empty Then
nodupes.Add ActiveSheet.Cells(g, 1).Value,
CStr(ActiveSheet.Cells(g, 1).Value)
Else
End If
Next g
On Error GoTo 0

Just for refrence the names are in the format last name, first name

-David

Sorting Collection
 

I did a little more search and found this routine:
Public Sub SortCollection(ColVar As Collection)
Dim oCol As Collection
Dim i As Integer
Dim i2 As Integer
Dim iBefore As Integer
If Not (ColVar Is Nothing) Then
If ColVar.Count 0 Then
Set oCol = New Collection
For i = 1 To ColVar.Count
If oCol.Count = 0 Then
oCol.Add ColVar(i)
Else
iBefore = 0
For i2 = oCol.Count To 1 Step -1
If LCase(ColVar(i)) < LCase(oCol(i2)) Then
iBefore = i2
Else
Exit For
End If
Next
If iBefore = 0 Then
oCol.Add ColVar(i)
Else
oCol.Add ColVar(i), , iBefore
End If
End If
Next
Set ColVar = oCol
Set oCol = Nothing
End If
End If
End Sub

Let me know if it works

Charles

David wrote:
Here you go.

On Error Resume Next
For g = 1 To 669
If ActiveSheet.Cells(g, 1) < Empty Then
nodupes.Add ActiveSheet.Cells(g, 1).Value,
CStr(ActiveSheet.Cells(g, 1).Value)
Else
End If
Next g
On Error GoTo 0

Just for refrence the names are in the format last name, first name

-David

Sorting Collection
 

Anyhow I figured out the error for this one, Collections use Base 1 not
Base 0 so we need to change the first For loop
For lLoop = 1 to ....

Charles


David wrote:
Thanks for the help, but when I tried that code I got a subscript out
of range error on this line:

If UCase(nodupes(lLoop2)) < UCase(nodupes(lLoop)) Then

any ideas why? Thanks.

- David

Sorting Collection
 

That worked great. Thanks for your help.

-David

Sorting Collection
 

I notice that your collection was named nodupe, do you just want to
determine whether or not there was a duplicate?

Charles

David wrote:
That worked great. Thanks for your help.

-David

Sorting Collection
 

I suspect the OP is using some partial code taken from John Walkenbach's site
where a complete solution is presented.

http://www.j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

Unless you do a major modification of someone else's code it is usually
better to provide the link so the complete context can be seen by the OP.

--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

I notice that your collection was named nodupe, do you just want to
determine whether or not there was a duplicate?

Charles

David wrote:
That worked great. Thanks for your help.

-David

Sorting Collection
 

Actually what I was getting at was possible using a dictionary instead
of a collection as it has an "Exist" Property. I though perhaps that
might speed things up for the OP. Have you ever used the Scripting
Dictionary before?

Charles

Tom Ogilvy wrote:
I suspect the OP is using some partial code taken from John Walkenbach's site
where a complete solution is presented.

http://www.j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

Unless you do a major modification of someone else's code it is usually
better to provide the link so the complete context can be seen by the OP.

--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

I notice that your collection was named nodupe, do you just want to
determine whether or not there was a duplicate?

Charles

David wrote:
That worked great. Thanks for your help.

-David

Sorting Collection
 

Set a reference to the Microsoft Scripting Runtime

then look in the object browser in the script library for the dictionary
object.

http://tinyurl.com/egu7o

--
Regards,
Tom Ogilvy




"David" wrote in message
oups.com...
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

Sorry to say but whenever I try to run that code excel crashes.
Die_Another_Day wrote:
David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

David, Try single stepping through the code and find out where it
crashes. What version of excel are you using?

Charles Chickering

David wrote:
Sorry to say but whenever I try to run that code excel crashes.
Die_Another_Day wrote:
David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

by crashing, do you mean raises an error? If so

Try it this way

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Scripting.Dictionary
Set nodupes = New Scripting.Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

if that is problematic, try it either of these ways:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
Range("D1").Resize(nodupes.Count, 1) = _
Application.Transpose(nodupes.Items)
End Sub


Sub RemoveDupes1()
Dim cnt As Long, v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
v = nodupes.Items
cnt = 1
For i = LBound(v) To UBound(v)
Range("D" & cnt).Value = v(i)
cnt = cnt + 1
Next

End Sub

--
Regards,
Tom Ogilvy



"David" wrote in message
oups.com...
Sorry to say but whenever I try to run that code excel crashes.
Die_Another_Day wrote:
David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

I am using Excel 2003 and by crashing I mean that my computer freezes
until I press ctrl-alt-delete and end the process.
Tom Ogilvy wrote:
by crashing, do you mean raises an error? If so

Try it this way

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Scripting.Dictionary
Set nodupes = New Scripting.Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

if that is problematic, try it either of these ways:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
Range("D1").Resize(nodupes.Count, 1) = _
Application.Transpose(nodupes.Items)
End Sub


Sub RemoveDupes1()
Dim cnt As Long, v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
v = nodupes.Items
cnt = 1
For i = LBound(v) To UBound(v)
Range("D" & cnt).Value = v(i)
cnt = cnt + 1
Next

End Sub

--
Regards,
Tom Ogilvy



"David" wrote in message
oups.com...
Sorry to say but whenever I try to run that code excel crashes.
Die_Another_Day wrote:
David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

Do you have a reference to ms word in your references. Word also has a
dictionary object, but it isn't related to this one in the scriptiong
runtime. However, the code would look at the first instance of a dictionary
object in the reference list. That might be causing the crash. If that is
the case, then one of mine should fix that problem. If that isn't it, then
I am surprised that you would get such a problem.

--
Regards,
Tom Ogilvy



"David" wrote in message
ups.com...
I am using Excel 2003 and by crashing I mean that my computer freezes
until I press ctrl-alt-delete and end the process.
Tom Ogilvy wrote:
by crashing, do you mean raises an error? If so

Try it this way

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Scripting.Dictionary
Set nodupes = New Scripting.Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

if that is problematic, try it either of these ways:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
Range("D1").Resize(nodupes.Count, 1) = _
Application.Transpose(nodupes.Items)
End Sub


Sub RemoveDupes1()
Dim cnt As Long, v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
v = nodupes.Items
cnt = 1
For i = LBound(v) To UBound(v)
Range("D" & cnt).Value = v(i)
cnt = cnt + 1
Next

End Sub

--
Regards,
Tom Ogilvy



"David" wrote in message
oups.com...
Sorry to say but whenever I try to run that code excel crashes.
Die_Another_Day wrote:
David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it
will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting Collection
 

Nope as far as I know there's no refrence to word in my code. Thanks
for your help though.

- David
Tom Ogilvy wrote:
Do you have a reference to ms word in your references. Word also has a
dictionary object, but it isn't related to this one in the scriptiong
runtime. However, the code would look at the first instance of a dictionary
object in the reference list. That might be causing the crash. If that is
the case, then one of mine should fix that problem. If that isn't it, then
I am surprised that you would get such a problem.

--
Regards,
Tom Ogilvy



"David" wrote in message
ups.com...
I am using Excel 2003 and by crashing I mean that my computer freezes
until I press ctrl-alt-delete and end the process.
Tom Ogilvy wrote:
by crashing, do you mean raises an error? If so

Try it this way

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Scripting.Dictionary
Set nodupes = New Scripting.Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

if that is problematic, try it either of these ways:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
Range("D1").Resize(nodupes.Count, 1) = _
Application.Transpose(nodupes.Items)
End Sub


Sub RemoveDupes1()
Dim cnt As Long, v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value)) Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
v = nodupes.Items
cnt = 1
For i = LBound(v) To UBound(v)
Range("D" & cnt).Value = v(i)
cnt = cnt + 1
Next

End Sub

--
Regards,
Tom Ogilvy



"David" wrote in message
oups.com...
Sorry to say but whenever I try to run that code excel crashes.
Die_Another_Day wrote:
David, just to give you more specific instructions for VBA, Start by
opening the VBA Editor, Then goto Tools... References... and click the
checkbox for "Microsoft Scripting Runtime" then click "Ok"
Now use the following code:

Sub RemoveDupes()
Dim cnt As Long
Dim nodupes As Dictionary
Set nodupes = New Dictionary
For cnt = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Not nodupes.Exists(CStr(ActiveSheet.Cells(cnt, 3).Value))
Then
nodupes.Add CStr(ActiveSheet.Cells(cnt, 3).Value), _
ActiveSheet.Cells(cnt, 3).Value
End If
Next
For cnt = 1 To nodupes.Count
Range("D" & cnt) = nodupes.Items(cnt - 1)
Next
End Sub

Let me know if you have problems

Charles

David wrote:
I am actually using the collection to remove duplicate items from a
list though I did not get the example I used from that site it was
similar. I've never used a scripting dictionary before and if it
will
speed things up I'd love to hear about how it works. Thanks

- David

Sorting
 

Hi,
I need to draw a pareto chart based on a list of values, which are
formula-driven.
First I need to sort the values, take the cumulative, calculate the
percentage and then plot a pareto chart.

As I know, excel doesn't allow sorting of formula-driven values.
So, effectively, I need some option, function or code which takes these
formula-driven values and sort them, so that I can plot a pareto chart

Please clarify.
Thanks and regards
Mahesh

Sorting
 

Hi Mahesh,

Can you give an example of formula values which do not permit sorting?



---
Regards,
Norman



"mahesh" wrote in message
oups.com...

Hi,
I need to draw a pareto chart based on a list of values, which are
formula-driven.
First I need to sort the values, take the cumulative, calculate the
percentage and then plot a pareto chart.

As I know, excel doesn't allow sorting of formula-driven values.
So, effectively, I need some option, function or code which takes these
formula-driven values and sort them, so that I can plot a pareto chart

Please clarify.
Thanks and regards
Mahesh

Sorting
 

Hi Norman,

It's not exactly "formula values do not permit sorting", but "sorting
doesn't take formula fields". For example, if you have a list of 5
values in column A. Take the cumulative, for each cell, in the adjacent
column B. And take the percentage of each value corresponding to the
total value and place them in column C

Now select all the columns and sort by column A.

The result will be that only Col A will be sorted and not the other
two.
It is understood that, when you are selecting all the columns, all the
values should align accoridngly with the sorted col.

Could I answer you?

Thanks
Mahesh


Norman Jones wrote:
Hi Mahesh,

Can you give an example of formula values which do not permit sorting?



---
Regards,
Norman



"mahesh" wrote in message
oups.com...

Hi,
I need to draw a pareto chart based on a list of values, which are
formula-driven.
First I need to sort the values, take the cumulative, calculate the
percentage and then plot a pareto chart.

As I know, excel doesn't allow sorting of formula-driven values.
So, effectively, I need some option, function or code which takes these
formula-driven values and sort them, so that I can plot a pareto chart

Please clarify.
Thanks and regards
Mahesh

Sorting Collection
 

Is it possible to adapt your code to sort a column in numerical sequence that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) < UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) < UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

did follow also have tried int still get 1122334455667788 want 12345678 then
repeat till entire column sorted

"Tom Ogilvy" wrote:

in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) < UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

I see in another thread, you posted sample code that is sorting on column C,
but your original post said your data was in column B. You also appear to
want to sort the rows out to column t, so the information I gave you would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) < UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

Either you haven't correctly described what you want to do, or you didn't
apply the solution properly.

for example, in your sample code you posted you do

for t = 2 to cells(100,3).End(xlup).Column step 8

cells(100,3).End(xlup).Column will always return 3, so your loop would
only do one pass.


you also select Range(Cells(t, 3), Cells(t + 3, 3)).Select

and then sort only the selection. This would sort C2:C5 on the one pass
you make, but you said you want to sort 8 cells. Also, it appears you want
to sort all the columns from A to T (you explanations are not that clear),
so you would not just sort column C alone.

I posted some corrections in that thread, but hopefully you can understand
that I don't particularly accept you answer that you triet it and it failed.
That may very well be, but it would work for the problem as I understood you
to described it (which may not be the problem).

--
Regards,
Tom Ogilvy

"Curt" wrote in message
...
did follow also have tried int still get 1122334455667788 want 12345678
then
repeat till entire column sorted

"Tom Ogilvy" wrote:

in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

i caught onto the relationship of columns so it did sort. Only i am after a
sort of 12345678 not 111222333444555 this is my problem. need to sort into
groups of 8 repeated till column is sorted. the other post has had diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on column C,
but your original post said your data was in column B. You also appear to
want to sort the rows out to column t, so the information I gave you would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) < UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am after a
sort of 12345678 not 111222333444555 this is my problem. need to sort into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on column
C,
but your original post said your data was in column B. You also appear
to
want to sort the rows out to column t, so the information I gave you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort 12345678. as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am after a
sort of 12345678 not 111222333444555 this is my problem. need to sort into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on column
C,
but your original post said your data was in column B. You also appear
to
want to sort the rows out to column t, so the information I gave you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8

so you don't have an equal number of each value (1 to 8)

How do you want them sorted (assume the first 1 is in C2 for each case)

1 2 3 4 5
6 7 8 1 2
3 4 5 6 7

or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

what to do about incomplete sets?

and, this cetainly doesn't match any of your descriptions or your code.

If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i 8 then i = 1
cell = i
Next


or

i = 0
for each cell in range("C2:C17")
i = i + 1
if i 8 then i = 1
cell.Resize(1,5).Value = i
Next


--
Regards,
Tom Ogilvy




"Curt" wrote in message
...
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort 12345678.
as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am
after a
sort of 12345678 not 111222333444555 this is my problem. need to sort
into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on
column
C,
but your original post said your data was in column B. You also
appear
to
want to sort the rows out to column t, so the information I gave you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as
the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new
to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

in the column to be sorted needs to finish out at 12345678 / 12345678 until
it does the entire column. I am sure I can adjust for the columns if I have a
macro that will sort.
I've pulled my hair out what I have left !!!
Thanks Much

"Tom Ogilvy" wrote:

Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8

so you don't have an equal number of each value (1 to 8)

How do you want them sorted (assume the first 1 is in C2 for each case)

1 2 3 4 5
6 7 8 1 2
3 4 5 6 7

or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

what to do about incomplete sets?

and, this cetainly doesn't match any of your descriptions or your code.

If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i 8 then i = 1
cell = i
Next


or

i = 0
for each cell in range("C2:C17")
i = i + 1
if i 8 then i = 1
cell.Resize(1,5).Value = i
Next


--
Regards,
Tom Ogilvy




"Curt" wrote in message
...
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort 12345678.
as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am
after a
sort of 12345678 not 111222333444555 this is my problem. need to sort
into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on
column
C,
but your original post said your data was in column B. You also
appear
to
want to sort the rows out to column t, so the information I gave you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as
the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new
to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

Sorry didn"t get all sort is down a column not across a row
Thanks
Curt

"Tom Ogilvy" wrote:

Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8

so you don't have an equal number of each value (1 to 8)

How do you want them sorted (assume the first 1 is in C2 for each case)

1 2 3 4 5
6 7 8 1 2
3 4 5 6 7

or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

what to do about incomplete sets?

and, this cetainly doesn't match any of your descriptions or your code.

If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i 8 then i = 1
cell = i
Next


or

i = 0
for each cell in range("C2:C17")
i = i + 1
if i 8 then i = 1
cell.Resize(1,5).Value = i
Next


--
Regards,
Tom Ogilvy




"Curt" wrote in message
...
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort 12345678.
as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am
after a
sort of 12345678 not 111222333444555 this is my problem. need to sort
into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on
column
C,
but your original post said your data was in column B. You also
appear
to
want to sort the rows out to column t, so the information I gave you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as
the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new
to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

incomplete sets just are that incomplete 12345---
Thanks again

"Tom Ogilvy" wrote:

Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8

so you don't have an equal number of each value (1 to 8)

How do you want them sorted (assume the first 1 is in C2 for each case)

1 2 3 4 5
6 7 8 1 2
3 4 5 6 7

or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

what to do about incomplete sets?

and, this cetainly doesn't match any of your descriptions or your code.

If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i 8 then i = 1
cell = i
Next


or

i = 0
for each cell in range("C2:C17")
i = i + 1
if i 8 then i = 1
cell.Resize(1,5).Value = i
Next


--
Regards,
Tom Ogilvy




"Curt" wrote in message
...
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort 12345678.
as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am
after a
sort of 12345678 not 111222333444555 this is my problem. need to sort
into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on
column
C,
but your original post said your data was in column B. You also
appear
to
want to sort the rows out to column t, so the information I gave you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and select
Values.

Now sort the data with column C as the first key, then column B as
the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am new
to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

Sub AAAA()
Dim cell As Range
Dim rng As Range
Dim rng1 As Range
For Each cell In Range("C2:G2")
Set rng = Range(cell, cell.End(xlDown))
cell.Offset(0, 1).EntireColumn.Insert
Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1)
rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _
":" & rng(1).Address(0, 0) & "," & _
rng(1).Address(0, 0) & ")"
rng1.Formula = rng1.Value
Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _
key1:=cell.Offset(0, 1), Order1:=xlAscending, _
key2:=cell, Order2:=xlAscending
cell.Offset(0, 1).EntireColumn.Delete
Next

End Sub

produced:

1 1 1 1 1
2 2 2 2 2
3 8 3 3 3
4 1 4 4 4
5 2 5 5 5
6 1 6 6 6
7 2 7 7 7
8 1 8 8 8
1 2 1 1 1
2 1 2 2 2
3 2 3 3 3
4 1 4 4 4
6 2 5 5 5
7 1 6 6 6
8 2 8 7 7
7 1 8


with your test data.

Use the method I originally advised, applied to the multicolumn layout of
your data and the statement (as I understood it) that you wanted each column
sorted separately. As I understand it, that is what you want.

--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
incomplete sets just are that incomplete 12345---
Thanks again

"Tom Ogilvy" wrote:

Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8

so you don't have an equal number of each value (1 to 8)

How do you want them sorted (assume the first 1 is in C2 for each case)

1 2 3 4 5
6 7 8 1 2
3 4 5 6 7

or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

what to do about incomplete sets?

and, this cetainly doesn't match any of your descriptions or your code.

If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i 8 then i = 1
cell = i
Next


or

i = 0
for each cell in range("C2:C17")
i = i + 1
if i 8 then i = 1
cell.Resize(1,5).Value = i
Next


--
Regards,
Tom Ogilvy




"Curt" wrote in message
...
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort
12345678.
as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am
after a
sort of 12345678 not 111222333444555 this is my problem. need to
sort
into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on
column
C,
but your original post said your data was in column B. You also
appear
to
want to sort the rows out to column t, so the information I gave
you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and
select
Values.

Now sort the data with column C as the first key, then column B
as
the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am
new
to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested
and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David

Sorting Collection
 

is there something I can add into "For Each cell In Range("C2")" to have it
disregard text in the cells. Would like to use a pull down menu for data
entry into the cell. Thanks it does the job extremely well. Once I relized it
stops where there blanks. With this new sheet there won't be any blanks.
Thanks Again



"Tom Ogilvy" wrote:

Sub AAAA()
Dim cell As Range
Dim rng As Range
Dim rng1 As Range
For Each cell In Range("C2:G2")
Set rng = Range(cell, cell.End(xlDown))
cell.Offset(0, 1).EntireColumn.Insert
Set rng1 = cell.Offset(0, 1).Resize(rng.Count, 1)
rng1.Formula = "=Countif(" & rng(1).Address(1, 1) & _
":" & rng(1).Address(0, 0) & "," & _
rng(1).Address(0, 0) & ")"
rng1.Formula = rng1.Value
Range(cell, cell.Offset(0, 1)).Resize(rng.Count).Sort _
key1:=cell.Offset(0, 1), Order1:=xlAscending, _
key2:=cell, Order2:=xlAscending
cell.Offset(0, 1).EntireColumn.Delete
Next

End Sub

produced:

1 1 1 1 1
2 2 2 2 2
3 8 3 3 3
4 1 4 4 4
5 2 5 5 5
6 1 6 6 6
7 2 7 7 7
8 1 8 8 8
1 2 1 1 1
2 1 2 2 2
3 2 3 3 3
4 1 4 4 4
6 2 5 5 5
7 1 6 6 6
8 2 8 7 7
7 1 8


with your test data.

Use the method I originally advised, applied to the multicolumn layout of
your data and the statement (as I understood it) that you wanted each column
sorted separately. As I understand it, that is what you want.

--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
incomplete sets just are that incomplete 12345---
Thanks again

"Tom Ogilvy" wrote:

Data value Qty
1 16
2 15
3 8
4 8
5 7
6 8
7 8
8 8

so you don't have an equal number of each value (1 to 8)

How do you want them sorted (assume the first 1 is in C2 for each case)

1 2 3 4 5
6 7 8 1 2
3 4 5 6 7

or
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

what to do about incomplete sets?

and, this cetainly doesn't match any of your descriptions or your code.

If you have complete sets, you might as well regenerate them
i = 0
for each cell in Range("C2").Resize(16,5)
i = i + 1
if i 8 then i = 1
cell = i
Next


or

i = 0
for each cell in range("C2:C17")
i = i + 1
if i 8 then i = 1
cell.Resize(1,5).Value = i
Next


--
Regards,
Tom Ogilvy




"Curt" wrote in message
...
entry Helper 4
1 1 2 1 1
2 1 5 2 2
3 1 5 3 3
4 1 7 4 4
4 2 8 8 8
6 1 1 5 5
7 1 6 7 7
8 1 3 6 6
1 2 6 3 3
2 2 1 1 1
3 2 3 2 2
5 1 8 4 4
6 2 4 7 7
7 2 4 6 6
8 2 2 5 5
7 8 8



Here are a copy paste from a worksheet need the columns to sort
12345678.
as
you can see I have been trying. So far no luch
Thanks

"Tom Ogilvy" wrote:

If you want to send a sample workbook, I will set it up for you.



--
Regards,
Tom Ogilvy


"Curt" wrote in message
...
i caught onto the relationship of columns so it did sort. Only i am
after a
sort of 12345678 not 111222333444555 this is my problem. need to
sort
into
groups of 8 repeated till column is sorted. the other post has had
diferent
ways to try maybe i left some trial data in.

"Tom Ogilvy" wrote:

I see in another thread, you posted sample code that is sorting on
column
C,
but your original post said your data was in column B. You also
appear
to
want to sort the rows out to column t, so the information I gave
you
would
be put in column U and you would sort on U as your primary key.

=countif($C$1:C1,C1)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
in Column C put in this formula in the first row

=countif($B$1:B1,B1)

then drag fill down the column.

Select column C, do Edit=Copy, then Edit=Paste Special and
select
Values.

Now sort the data with column C as the first key, then column B
as
the
second key.

This is fairly trivial - make an effort. - pay attention to the
instructions.

--
Regards,
Tom Ogilvy



"Curt" wrote in message
...
Is it possible to adapt your code to sort a column in numerical
sequence
that
is 12345678 repeat 12345678 until entire column is sorted. I am
new
to
sorting so have tried all I can. Found no way in options to get
sequence
all
ways got 1122334455667788 needed 12345678
any way
Thanks


"Die_Another_Day" wrote:

Here's a mix on some code from ozgrid. Note this is untested
and
adapted from the original:
Dim MyCollection As New Collection
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

'Sort array
For lLoop = 0 To MyCollection.Count
For lLoop2 = lLoop To MyCollection.Count
If UCase(MyCollection(lLoop2)) <
UCase(MyCollection(lLoop))
Then
str1 = MyCollection(lLoop)
str2 = MyCollection(lLoop2)
MyCollection(lLoop) = str2
MyCollection(lLoop2) = str1
End If
Next lLoop2
Next lLoop

HTH

Charles Chickering

Here's the original website if you want to read it as well:
http://www.ozgrid.com/VBA/sort-array.htm
David wrote:
I have a collection filled with names and I was wondering if
someone
could show me some code to sort it alphabetically. Thanks

- David