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

- David

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

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

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

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

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

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

That worked great. Thanks for your help.

-David

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

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

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

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

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

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

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

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

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

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