sort in multiple columns

hi guys,
            how can i sort lists of names in several columns eg: c3:c15,
e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3 and the
last (...Z) end in e31?

regards
bob


0
god4991 (4)
10/31/2003 6:49:50 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
599 Views

Similar Articles

[PageSpeed] 10

Does this mean you  have 13+13+14+14=54 names spread over 4 ranges?

If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and sort
there.  Then copy|Paste the rows back to the 4 areas in that range (if you need
to).



bob wrote:
> 
> hi guys,
>             how can i sort lists of names in several columns eg: c3:c15,
> e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3 and the
> last (...Z) end in e31?
> 
> regards
> bob

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/1/2003 1:17:31 AM
hi dave, those columns should have 15 entries each my mistake. i was hoping
to have this feature happen auto matically as i enter the names. eg: add
another name to a blank cell (anywhere, most likely at the bottom) and it
shuffles amongst the other names to finds its place.

regards
bob


"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FA309AB.7399B762@msn.com...
> Does this mean you  have 13+13+14+14=54 names spread over 4 ranges?
>
> If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and
sort
> there.  Then copy|Paste the rows back to the 4 areas in that range (if you
need
> to).
>
>
>
> bob wrote:
> >
> > hi guys,
> >             how can i sort lists of names in several columns eg: c3:c15,
> > e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3 and
the
> > last (...Z) end in e31?
> >
> > regards
> > bob
>
> --
>
> Dave Peterson
> ec35720@msn.com


0
god4991 (4)
11/1/2003 9:31:31 PM
But the order of placement into those cells is:

C3:C17 then E3:E17 then C18:C32 then E18:E32

If yes, then right click on the worksheet tab that should have this behavior. 
Select view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRng As Range
    Dim myCell As Range
    Dim myArray() As Variant
    Dim iCtr As Long
    Dim TotalElements As Long
    
    If Target.Cells.Count > 1 Then Exit Sub

    'change this line to match the cell where you'll do your data entry
    If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

    If Trim(Target.Value) = "" Then Exit Sub
    
    Set myRng = Range("C3:C17,E3:E17,C18:C32,E18:E32")
    
    If Application.CountA(myRng) = myRng.Cells.Count Then
        MsgBox "No empty cells for new name!"
        Beep
        Exit Sub
    End If
    
    ReDim myArray(1 To myRng.Cells.Count)
    myArray(1) = Target.Value
    iCtr = 1
    For Each myCell In myRng.Cells
        If Trim(myCell.Value) <> "" Then
            iCtr = iCtr + 1
            myArray(iCtr) = myCell.Value
        End If
    Next myCell
    TotalElements = iCtr
    Call QuickSort(myArray(), 1, TotalElements)
    
    iCtr = 0
    Application.EnableEvents = False
    For Each myCell In myRng.Cells
        iCtr = iCtr + 1
        If iCtr > TotalElements Then
            myCell.Value = ""
        Else
            myCell.Value = myArray(iCtr)
        End If
    Next myCell
    
    Target.Value = ""
    Application.EnableEvents = True

End Sub

Then Insert|Module from the VBE toolbar to insert a general module.  Paste this
in:

Option Explicit
Sub QuickSort(SortArray, L, R)
'from Jim Rech
'http://google.com/groups?threadm=eUXU6tB%249GA.196%40uppssnewspub04.moswest.msn.net
'one line in your browser

    Dim i, j, X, Y
    i = L
    j = R
    X = SortArray((L + R) / 2)

    While (i <= j)
        While (SortArray(i) < X And i < R)
            i = i + 1
        Wend
        While (X < SortArray(j) And j > L)
            j = j - 1
        Wend
        If (i <= j) Then
            Y = SortArray(i)
            SortArray(i) = SortArray(j)
            SortArray(j) = Y
            i = i + 1
            j = j - 1
        End If
    Wend
    If (L < j) Then Call QuickSort(SortArray, L, j)
    If (i < R) Then Call QuickSort(SortArray, i, R)
End Sub


bob wrote:
> 
> hi dave, those columns should have 15 entries each my mistake. i was hoping
> to have this feature happen auto matically as i enter the names. eg: add
> another name to a blank cell (anywhere, most likely at the bottom) and it
> shuffles amongst the other names to finds its place.
> 
> regards
> bob
> 
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:3FA309AB.7399B762@msn.com...
> > Does this mean you  have 13+13+14+14=54 names spread over 4 ranges?
> >
> > If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and
> sort
> > there.  Then copy|Paste the rows back to the 4 areas in that range (if you
> need
> > to).
> >
> >
> >
> > bob wrote:
> > >
> > > hi guys,
> > >             how can i sort lists of names in several columns eg: c3:c15,
> > > e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3 and
> the
> > > last (...Z) end in e31?
> > >
> > > regards
> > > bob
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/2/2003 12:13:27 AM
Thank you heaps Dave,
                                    This will make things a lot easier
regards bob

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FA44C27.72654BB7@msn.com...
> But the order of placement into those cells is:
>
> C3:C17 then E3:E17 then C18:C32 then E18:E32
>
> If yes, then right click on the worksheet tab that should have this
behavior.
> Select view code and paste this in:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>     Dim myRng As Range
>     Dim myCell As Range
>     Dim myArray() As Variant
>     Dim iCtr As Long
>     Dim TotalElements As Long
>
>     If Target.Cells.Count > 1 Then Exit Sub
>
>     'change this line to match the cell where you'll do your data entry
>     If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
>
>     If Trim(Target.Value) = "" Then Exit Sub
>
>     Set myRng = Range("C3:C17,E3:E17,C18:C32,E18:E32")
>
>     If Application.CountA(myRng) = myRng.Cells.Count Then
>         MsgBox "No empty cells for new name!"
>         Beep
>         Exit Sub
>     End If
>
>     ReDim myArray(1 To myRng.Cells.Count)
>     myArray(1) = Target.Value
>     iCtr = 1
>     For Each myCell In myRng.Cells
>         If Trim(myCell.Value) <> "" Then
>             iCtr = iCtr + 1
>             myArray(iCtr) = myCell.Value
>         End If
>     Next myCell
>     TotalElements = iCtr
>     Call QuickSort(myArray(), 1, TotalElements)
>
>     iCtr = 0
>     Application.EnableEvents = False
>     For Each myCell In myRng.Cells
>         iCtr = iCtr + 1
>         If iCtr > TotalElements Then
>             myCell.Value = ""
>         Else
>             myCell.Value = myArray(iCtr)
>         End If
>     Next myCell
>
>     Target.Value = ""
>     Application.EnableEvents = True
>
> End Sub
>
> Then Insert|Module from the VBE toolbar to insert a general module.  Paste
this
> in:
>
> Option Explicit
> Sub QuickSort(SortArray, L, R)
> 'from Jim Rech
>
'http://google.com/groups?threadm=eUXU6tB%249GA.196%40uppssnewspub04.moswest
..msn.net
> 'one line in your browser
>
>     Dim i, j, X, Y
>     i = L
>     j = R
>     X = SortArray((L + R) / 2)
>
>     While (i <= j)
>         While (SortArray(i) < X And i < R)
>             i = i + 1
>         Wend
>         While (X < SortArray(j) And j > L)
>             j = j - 1
>         Wend
>         If (i <= j) Then
>             Y = SortArray(i)
>             SortArray(i) = SortArray(j)
>             SortArray(j) = Y
>             i = i + 1
>             j = j - 1
>         End If
>     Wend
>     If (L < j) Then Call QuickSort(SortArray, L, j)
>     If (i < R) Then Call QuickSort(SortArray, i, R)
> End Sub
>
>
> bob wrote:
> >
> > hi dave, those columns should have 15 entries each my mistake. i was
hoping
> > to have this feature happen auto matically as i enter the names. eg: add
> > another name to a blank cell (anywhere, most likely at the bottom) and
it
> > shuffles amongst the other names to finds its place.
> >
> > regards
> > bob
> >
> > "Dave Peterson" <ec35720@msn.com> wrote in message
> > news:3FA309AB.7399B762@msn.com...
> > > Does this mean you  have 13+13+14+14=54 names spread over 4 ranges?
> > >
> > > If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and
> > sort
> > > there.  Then copy|Paste the rows back to the 4 areas in that range (if
you
> > need
> > > to).
> > >
> > >
> > >
> > > bob wrote:
> > > >
> > > > hi guys,
> > > >             how can i sort lists of names in several columns eg:
c3:c15,
> > > > e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3
and
> > the
> > > > last (...Z) end in e31?
> > > >
> > > > regards
> > > > bob
> > >
> > > --
> > >
> > > Dave Peterson
> > > ec35720@msn.com
>
> --
>
> Dave Peterson
> ec35720@msn.com


0
god4991 (4)
11/2/2003 8:08:54 AM
This is a multi-part message in MIME format.

------=_NextPart_000_0016_01C3A1B6.D6DBE180
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

hi dave,
            i've tried this

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRng As Range
    Dim myCell As Range
    Dim myArray() As Variant
    Dim iCtr As Long
    Dim TotalElements As Long
   =20
    If Target.Cells.Count > 1 Then Exit Sub

    'change this line to match the cell where you'll do your data entry
    If Intersect(Target, Range("B3:B17,D3:D17,B20:B34,D20:D34")) Is =
Nothing Then Exit Sub

    If Trim(Target.Value) =3D "" Then Exit Sub
   =20
    Set myRng =3D Range("B3:B17,D3:D17,B20:B34,D20:D34")
   =20
    If Application.CountA(myRng) =3D myRng.Cells.Count Then
        MsgBox "No empty cells for new name!"
        Beep
        Exit Sub
    End If
   =20
    ReDim myArray(1 To myRng.Cells.Count)
    myArray(1) =3D Target.Value
    iCtr =3D 1
    For Each myCell In myRng.Cells
        If Trim(myCell.Value) <> "" Then
            iCtr =3D iCtr + 1
            myArray(iCtr) =3D myCell.Value
        End If
    Next myCell
    TotalElements =3D iCtr
    Call QuickSort(myArray(), 1, TotalElements)
   =20
    iCtr =3D 0
    Application.EnableEvents =3D False
    For Each myCell In myRng.Cells
        iCtr =3D iCtr + 1
        If iCtr > TotalElements Then
            myCell.Value =3D ""
        Else
            myCell.Value =3D myArray(iCtr)
        End If
    Next myCell
   =20
    Target.Value =3D ""
    Application.EnableEvents =3D True

End Sub

the range i've used to enter data is the entire range. the only problem =
is that it enters the data in twice. also this separates the case (eg =
upper case A-Z come before lower case a-z)

any ideas
regards=20
bob


"bob" <god@heaven.com> wrote in message =
news:qY2pb.175025$bo1.4763@news-server.bigpond.net.au...
> Thank you heaps Dave,
>                                     This will make things a lot easier
> regards bob
>=20
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:3FA44C27.72654BB7@msn.com...
> > But the order of placement into those cells is:
> >
> > C3:C17 then E3:E17 then C18:C32 then E18:E32
> >
> > If yes, then right click on the worksheet tab that should have this
> behavior.
> > Select view code and paste this in:
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >     Dim myRng As Range
> >     Dim myCell As Range
> >     Dim myArray() As Variant
> >     Dim iCtr As Long
> >     Dim TotalElements As Long
> >
> >     If Target.Cells.Count > 1 Then Exit Sub
> >
> >     'change this line to match the cell where you'll do your data =
entry
> >     If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
> >
> >     If Trim(Target.Value) =3D "" Then Exit Sub
> >
> >     Set myRng =3D Range("C3:C17,E3:E17,C18:C32,E18:E32")
> >
> >     If Application.CountA(myRng) =3D myRng.Cells.Count Then
> >         MsgBox "No empty cells for new name!"
> >         Beep
> >         Exit Sub
> >     End If
> >
> >     ReDim myArray(1 To myRng.Cells.Count)
> >     myArray(1) =3D Target.Value
> >     iCtr =3D 1
> >     For Each myCell In myRng.Cells
> >         If Trim(myCell.Value) <> "" Then
> >             iCtr =3D iCtr + 1
> >             myArray(iCtr) =3D myCell.Value
> >         End If
> >     Next myCell
> >     TotalElements =3D iCtr
> >     Call QuickSort(myArray(), 1, TotalElements)
> >
> >     iCtr =3D 0
> >     Application.EnableEvents =3D False
> >     For Each myCell In myRng.Cells
> >         iCtr =3D iCtr + 1
> >         If iCtr > TotalElements Then
> >             myCell.Value =3D ""
> >         Else
> >             myCell.Value =3D myArray(iCtr)
> >         End If
> >     Next myCell
> >
> >     Target.Value =3D ""
> >     Application.EnableEvents =3D True
> >
> > End Sub
> >
> > Then Insert|Module from the VBE toolbar to insert a general module.  =
Paste
> this
> > in:
> >
> > Option Explicit
> > Sub QuickSort(SortArray, L, R)
> > 'from Jim Rech
> >
> =
'http://google.com/groups?threadm=3DeUXU6tB%249GA.196%40uppssnewspub04.mo=
swest
> .msn.net
> > 'one line in your browser
> >
> >     Dim i, j, X, Y
> >     i =3D L
> >     j =3D R
> >     X =3D SortArray((L + R) / 2)
> >
> >     While (i <=3D j)
> >         While (SortArray(i) < X And i < R)
> >             i =3D i + 1
> >         Wend
> >         While (X < SortArray(j) And j > L)
> >             j =3D j - 1
> >         Wend
> >         If (i <=3D j) Then
> >             Y =3D SortArray(i)
> >             SortArray(i) =3D SortArray(j)
> >             SortArray(j) =3D Y
> >             i =3D i + 1
> >             j =3D j - 1
> >         End If
> >     Wend
> >     If (L < j) Then Call QuickSort(SortArray, L, j)
> >     If (i < R) Then Call QuickSort(SortArray, i, R)
> > End Sub
> >
> >
> > bob wrote:
> > >
> > > hi dave, those columns should have 15 entries each my mistake. i =
was
> hoping
> > > to have this feature happen auto matically as i enter the names. =
eg: add
> > > another name to a blank cell (anywhere, most likely at the bottom) =
and
> it
> > > shuffles amongst the other names to finds its place.
> > >
> > > regards
> > > bob
> > >
> > > "Dave Peterson" <ec35720@msn.com> wrote in message
> > > news:3FA309AB.7399B762@msn.com...
> > > > Does this mean you  have 13+13+14+14=3D54 names spread over 4 =
ranges?
> > > >
> > > > If yes, I'd add a temporary worksheet, copy those ranges to =
A1:A54 and
> > > sort
> > > > there.  Then copy|Paste the rows back to the 4 areas in that =
range (if
> you
> > > need
> > > > to).
> > > >
> > > >
> > > >
> > > > bob wrote:
> > > > >
> > > > > hi guys,
> > > > >             how can i sort lists of names in several columns =
eg:
> c3:c15,
> > > > > e3:e15,c18:c31,e18:e31. so that the first names (A...) start =
in c3
> and
> > > the
> > > > > last (...Z) end in e31?
> > > > >
> > > > > regards
> > > > > bob
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > ec35720@msn.com
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com
>=20
>=20

------=_NextPart_000_0016_01C3A1B6.D6DBE180
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>hi dave,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; i've tried this</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Option Explicit<BR>Private Sub=20
Worksheet_Change(ByVal Target As Range)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; Dim myRng As=20
Range<BR>&nbsp;&nbsp;&nbsp; Dim myCell As Range<BR>&nbsp;&nbsp;&nbsp; =
Dim=20
myArray() As Variant<BR>&nbsp;&nbsp;&nbsp; Dim iCtr As=20
Long<BR>&nbsp;&nbsp;&nbsp; Dim TotalElements As =
Long<BR>&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; If Target.Cells.Count &gt; 1 Then Exit =
Sub</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; 'change this line to =
match the=20
cell where you'll do your data entry<BR>&nbsp;&nbsp;&nbsp; If =
Intersect(Target,=20
Range("<FONT color=3D#ff0000>B3:B17,D3:D17,B20:B34,D20:D34</FONT>")) Is =
Nothing=20
Then Exit Sub</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; If =
Trim(Target.Value) =3D "" Then=20
Exit Sub<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; Set myRng =3D=20
Range("B3:B17,D3:D17,B20:B34,D20:D34")<BR>&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; If Application.CountA(myRng) =3D =
myRng.Cells.Count=20
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox "No empty =
cells for=20
new name!"<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
Beep<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Exit=20
Sub<BR>&nbsp;&nbsp;&nbsp; End If<BR>&nbsp;&nbsp;&nbsp; =
<BR>&nbsp;&nbsp;&nbsp;=20
ReDim myArray(1 To myRng.Cells.Count)<BR>&nbsp;&nbsp;&nbsp; myArray(1) =
=3D=20
Target.Value<BR>&nbsp;&nbsp;&nbsp; iCtr =3D 1<BR>&nbsp;&nbsp;&nbsp; For =
Each=20
myCell In myRng.Cells<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If=20
Trim(myCell.Value) &lt;&gt; ""=20
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
; iCtr=20
=3D iCtr + =
1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
myArray(iCtr) =3D =
myCell.Value<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End=20
If<BR>&nbsp;&nbsp;&nbsp; Next myCell<BR>&nbsp;&nbsp;&nbsp; TotalElements =
=3D=20
iCtr<BR>&nbsp;&nbsp;&nbsp; Call QuickSort(myArray(), 1,=20
TotalElements)<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; iCtr =3D=20
0<BR>&nbsp;&nbsp;&nbsp; Application.EnableEvents =3D =
False<BR>&nbsp;&nbsp;&nbsp;=20
For Each myCell In =
myRng.Cells<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
iCtr =3D iCtr + 1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If iCtr =
&gt;=20
TotalElements=20
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;=20
myCell.Value =3D ""<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
Else<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;=20
myCell.Value =3D =
myArray(iCtr)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End=20
If<BR>&nbsp;&nbsp;&nbsp; Next myCell<BR>&nbsp;&nbsp;&nbsp;=20
<BR>&nbsp;&nbsp;&nbsp; Target.Value =3D ""<BR>&nbsp;&nbsp;&nbsp;=20
Application.EnableEvents =3D True</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>End Sub</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>the range i've used to enter data is =
the entire=20
range. the only problem is that it enters the data in twice. also this =
separates=20
the case (eg upper case A-Z come before lower case a-z)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>any ideas</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>regards </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>bob</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>"bob" &lt;</FONT><A=20
href=3D"mailto:god@heaven.com"><FONT face=3DArial=20
size=3D2>god@heaven.com</FONT></A><FONT face=3DArial size=3D2>&gt; wrote =
in message=20
</FONT><A =
href=3D"news:qY2pb.175025$bo1.4763@news-server.bigpond.net.au"><FONT=20
face=3DArial=20
size=3D2>news:qY2pb.175025$bo1.4763@news-server.bigpond.net.au</FONT></A>=
<FONT=20
face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>&gt; =
Thank you heaps=20
Dave,<BR>&gt;=20
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
This will make things a lot easier<BR>&gt; regards bob<BR>&gt; <BR>&gt; =
"Dave=20
Peterson" &lt;</FONT><A href=3D"mailto:ec35720@msn.com"><FONT =
face=3DArial=20
size=3D2>ec35720@msn.com</FONT></A><FONT face=3DArial size=3D2>&gt; =
wrote in=20
message<BR>&gt; </FONT><A href=3D"news:3FA44C27.72654BB7@msn.com"><FONT =
face=3DArial=20
size=3D2>news:3FA44C27.72654BB7@msn.com</FONT></A><FONT face=3DArial=20
size=3D2>...<BR>&gt; &gt; But the order of placement into those cells =
is:<BR>&gt;=20
&gt;<BR>&gt; &gt; C3:C17 then E3:E17 then C18:C32 then E18:E32<BR>&gt;=20
&gt;<BR>&gt; &gt; If yes, then right click on the worksheet tab that =
should have=20
this<BR>&gt; behavior.<BR>&gt; &gt; Select view code and paste this =
in:<BR>&gt;=20
&gt;<BR>&gt; &gt; Option Explicit<BR>&gt; &gt; Private Sub=20
Worksheet_Change(ByVal Target As Range)<BR>&gt; &gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Dim myRng As Range<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Dim myCell As Range<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Dim myArray() As Variant<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Dim iCtr As Long<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Dim TotalElements As Long<BR>&gt; =
&gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; If Target.Cells.Count &gt; 1 Then Exit =
Sub<BR>&gt;=20
&gt;<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; 'change this line to match the =
cell=20
where you'll do your data entry<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; If=20
Intersect(Target, Range("a1")) Is Nothing Then Exit Sub<BR>&gt; =
&gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; If Trim(Target.Value) =3D "" Then Exit =
Sub<BR>&gt;=20
&gt;<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; Set myRng =3D=20
Range("C3:C17,E3:E17,C18:C32,E18:E32")<BR>&gt; &gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; If Application.CountA(myRng) =3D =
myRng.Cells.Count=20
Then<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox =
"No=20
empty cells for new name!"<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Beep<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Exit Sub<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; End If<BR>&gt; &gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; ReDim myArray(1 To =
myRng.Cells.Count)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; myArray(1) =3D Target.Value<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; iCtr =3D 1<BR>&gt; =
&gt;&nbsp;&nbsp;&nbsp;&nbsp; For=20
Each myCell In myRng.Cells<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If =
Trim(myCell.Value)=20
&lt;&gt; "" Then<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
iCtr =3D iCtr + 1<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
myArray(iCtr) =3D myCell.Value<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Next myCell<BR>&gt; =
&gt;&nbsp;&nbsp;&nbsp;&nbsp;=20
TotalElements =3D iCtr<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; Call=20
QuickSort(myArray(), 1, TotalElements)<BR>&gt; &gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; iCtr =3D 0<BR>&gt; =
&gt;&nbsp;&nbsp;&nbsp;&nbsp;=20
Application.EnableEvents =3D False<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; =
For Each=20
myCell In myRng.Cells<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; iCtr =3D iCtr + =
1<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If iCtr &gt; =
TotalElements=20
Then<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
myCell.Value =3D ""<BR>&gt; =
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
Else<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
myCell.Value =3D myArray(iCtr)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Next myCell<BR>&gt; &gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Target.Value =3D ""<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Application.EnableEvents =3D True<BR>&gt;=20
&gt;<BR>&gt; &gt; End Sub<BR>&gt; &gt;<BR>&gt; &gt; Then Insert|Module =
from the=20
VBE toolbar to insert a general module.&nbsp; Paste<BR>&gt; this<BR>&gt; =
&gt;=20
in:<BR>&gt; &gt;<BR>&gt; &gt; Option Explicit<BR>&gt; &gt; Sub=20
QuickSort(SortArray, L, R)<BR>&gt; &gt; 'from Jim Rech<BR>&gt; =
&gt;<BR>&gt;=20
'http://google.com/groups?threadm=3DeUXU6tB%249GA.196%40uppssnewspub04.mo=
swest<BR>&gt;=20
..msn.net<BR>&gt; &gt; 'one line in your browser<BR>&gt; &gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; Dim i, j, X, Y<BR>&gt; =
&gt;&nbsp;&nbsp;&nbsp;&nbsp;=20
i =3D L<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; j =3D R<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; X =3D SortArray((L + R) / 2)<BR>&gt; =
&gt;<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; While (i &lt;=3D j)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; While (SortArray(i) =
&lt; X=20
And i &lt; R)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; i =3D=20
i + 1<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Wend<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; While (X &lt; =
SortArray(j)=20
And j &gt; L)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; j =3D=20
j - 1<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
Wend<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If (i &lt;=3D j) =
Then<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; Y =3D=20
SortArray(i)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
SortArray(i) =3D SortArray(j)<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
SortArray(j) =3D Y<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; i =3D=20
i + 1<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; j =3D=20
j - 1<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End=20
If<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; Wend<BR>&gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp; If (L &lt; j) Then Call =
QuickSort(SortArray, L,=20
j)<BR>&gt; &gt;&nbsp;&nbsp;&nbsp;&nbsp; If (i &lt; R) Then Call=20
QuickSort(SortArray, i, R)<BR>&gt; &gt; End Sub<BR>&gt; &gt;<BR>&gt;=20
&gt;<BR>&gt; &gt; bob wrote:<BR>&gt; &gt; &gt;<BR>&gt; &gt; &gt; hi =
dave, those=20
columns should have 15 entries each my mistake. i was<BR>&gt; =
hoping<BR>&gt;=20
&gt; &gt; to have this feature happen auto matically as i enter the =
names. eg:=20
add<BR>&gt; &gt; &gt; another name to a blank cell (anywhere, most =
likely at the=20
bottom) and<BR>&gt; it<BR>&gt; &gt; &gt; shuffles amongst the other =
names to=20
finds its place.<BR>&gt; &gt; &gt;<BR>&gt; &gt; &gt; regards<BR>&gt; =
&gt; &gt;=20
bob<BR>&gt; &gt; &gt;<BR>&gt; &gt; &gt; "Dave Peterson" &lt;</FONT><A=20
href=3D"mailto:ec35720@msn.com"><FONT face=3DArial=20
size=3D2>ec35720@msn.com</FONT></A><FONT face=3DArial size=3D2>&gt; =
wrote in=20
message<BR>&gt; &gt; &gt; </FONT><A =
href=3D"news:3FA309AB.7399B762@msn.com"><FONT=20
face=3DArial size=3D2>news:3FA309AB.7399B762@msn.com</FONT></A><FONT =
face=3DArial=20
size=3D2>...<BR>&gt; &gt; &gt; &gt; Does this mean you&nbsp; have =
13+13+14+14=3D54=20
names spread over 4 ranges?<BR>&gt; &gt; &gt; &gt;<BR>&gt; &gt; &gt; =
&gt; If=20
yes, I'd add a temporary worksheet, copy those ranges to A1:A54 =
and<BR>&gt; &gt;=20
&gt; sort<BR>&gt; &gt; &gt; &gt; there.&nbsp; Then copy|Paste the rows =
back to=20
the 4 areas in that range (if<BR>&gt; you<BR>&gt; &gt; &gt; need<BR>&gt; =
&gt;=20
&gt; &gt; to).<BR>&gt; &gt; &gt; &gt;<BR>&gt; &gt; &gt; &gt;<BR>&gt; =
&gt; &gt;=20
&gt;<BR>&gt; &gt; &gt; &gt; bob wrote:<BR>&gt; &gt; &gt; &gt; =
&gt;<BR>&gt; &gt;=20
&gt; &gt; &gt; hi guys,<BR>&gt; &gt; &gt; &gt;=20
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; how=20
can i sort lists of names in several columns eg:<BR>&gt; c3:c15,<BR>&gt; =
&gt;=20
&gt; &gt; &gt; e3:e15,c18:c31,e18:e31. so that the first names (A...) =
start in=20
c3<BR>&gt; and<BR>&gt; &gt; &gt; the<BR>&gt; &gt; &gt; &gt; &gt; last =
(...Z) end=20
in e31?<BR>&gt; &gt; &gt; &gt; &gt;<BR>&gt; &gt; &gt; &gt; &gt; =
regards<BR>&gt;=20
&gt; &gt; &gt; &gt; bob<BR>&gt; &gt; &gt; &gt;<BR>&gt; &gt; &gt; &gt; =
--<BR>&gt;=20
&gt; &gt; &gt;<BR>&gt; &gt; &gt; &gt; Dave Peterson<BR>&gt; &gt; &gt; =
&gt;=20
</FONT><A href=3D"mailto:ec35720@msn.com"><FONT face=3DArial=20
size=3D2>ec35720@msn.com</FONT></A><BR><FONT face=3DArial size=3D2>&gt; =
&gt;<BR>&gt;=20
&gt; --<BR>&gt; &gt;<BR>&gt; &gt; Dave Peterson<BR>&gt; &gt; </FONT><A=20
href=3D"mailto:ec35720@msn.com"><FONT face=3DArial=20
size=3D2>ec35720@msn.com</FONT></A><BR><FONT face=3DArial size=3D2>&gt; =
<BR>&gt;=20
</FONT></BODY></HTML>

------=_NextPart_000_0016_01C3A1B6.D6DBE180--

0
god4991 (4)
11/2/2003 4:02:30 PM
I thought you said that you were going to enter the data into a different
cell--but maybe you meant another cell in the range (I just reread your other
post and it sounds like I misread it the first time).

But try this in the worksheet module (the general module didn't change):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim myRng As Range
    Dim myCell As Range
    Dim myArray() As Variant
    Dim iCtr As Long
    Dim TotalElements As Long
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Set myRng = Range("B3:B17,D3:D17,B20:B34,D20:D34")
 
    'change this line to match the cell where you'll do your data entry
    If Intersect(Target, myRng) Is Nothing Then Exit Sub
    
    ReDim myArray(1 To myRng.Cells.Count)
    iCtr = 0
    For Each myCell In myRng.Cells
        If Trim(myCell.Value) <> "" Then
            iCtr = iCtr + 1
            myArray(iCtr) = myCell.Value
        End If
    Next myCell
    TotalElements = iCtr

    Call QuickSort(myArray(), 1, TotalElements)
    
    iCtr = 0
    Application.EnableEvents = False
    For Each myCell In myRng.Cells
        iCtr = iCtr + 1
        If iCtr > TotalElements Then
            myCell.Value = ""
        Else
            myCell.Value = myArray(iCtr)
        End If
    Next myCell
    
    Application.EnableEvents = True
 
End Sub


> bob wrote:
> 
> hi dave,
>             i've tried this
> 
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     Dim myRng As Range
>     Dim myCell As Range
>     Dim myArray() As Variant
>     Dim iCtr As Long
>     Dim TotalElements As Long
> 
>     If Target.Cells.Count > 1 Then Exit Sub
> 
>     'change this line to match the cell where you'll do your data entry
>     If Intersect(Target, Range("B3:B17,D3:D17,B20:B34,D20:D34")) Is Nothing
> Then Exit Sub
> 
>     If Trim(Target.Value) = "" Then Exit Sub
> 
>     Set myRng = Range("B3:B17,D3:D17,B20:B34,D20:D34")
> 
>     If Application.CountA(myRng) = myRng.Cells.Count Then
>         MsgBox "No empty cells for new name!"
>         Beep
>         Exit Sub
>     End If
> 
>     ReDim myArray(1 To myRng.Cells.Count)
>     myArray(1) = Target.Value
>     iCtr = 1
>     For Each myCell In myRng.Cells
>         If Trim(myCell.Value) <> "" Then
>             iCtr = iCtr + 1
>             myArray(iCtr) = myCell.Value
>         End If
>     Next myCell
>     TotalElements = iCtr
>     Call QuickSort(myArray(), 1, TotalElements)
> 
>     iCtr = 0
>     Application.EnableEvents = False
>     For Each myCell In myRng.Cells
>         iCtr = iCtr + 1
>         If iCtr > TotalElements Then
>             myCell.Value = ""
>         Else
>             myCell.Value = myArray(iCtr)
>         End If
>     Next myCell
> 
>     Target.Value = ""
>     Application.EnableEvents = True
> 
> End Sub
> 
> the range i've used to enter data is the entire range. the only problem is
> that it enters the data in twice. also this separates the case (eg upper case
> A-Z come before lower case a-z)
> 
> any ideas
> regards
> bob
> 
> 
> "bob" <god@heaven.com> wrote in message
> news:qY2pb.175025$bo1.4763@news-server.bigpond.net.au...
> > Thank you heaps Dave,
> >                                     This will make things a lot easier
> > regards bob
> >
> > "Dave Peterson" <ec35720@msn.com> wrote in message
> > news:3FA44C27.72654BB7@msn.com...
> > > But the order of placement into those cells is:
> > >
> > > C3:C17 then E3:E17 then C18:C32 then E18:E32
> > >
> > > If yes, then right click on the worksheet tab that should have this
> > behavior.
> > > Select view code and paste this in:
> > >
> > > Option Explicit
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > >     Dim myRng As Range
> > >     Dim myCell As Range
> > >     Dim myArray() As Variant
> > >     Dim iCtr As Long
> > >     Dim TotalElements As Long
> > >
> > >     If Target.Cells.Count > 1 Then Exit Sub
> > >
> > >     'change this line to match the cell where you'll do your data entry
> > >     If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
> > >
> > >     If Trim(Target.Value) = "" Then Exit Sub
> > >
> > >     Set myRng = Range("C3:C17,E3:E17,C18:C32,E18:E32")
> > >
> > >     If Application.CountA(myRng) = myRng.Cells.Count Then
> > >         MsgBox "No empty cells for new name!"
> > >         Beep
> > >         Exit Sub
> > >     End If
> > >
> > >     ReDim myArray(1 To myRng.Cells.Count)
> > >     myArray(1) = Target.Value
> > >     iCtr = 1
> > >     For Each myCell In myRng.Cells
> > >         If Trim(myCell.Value) <> "" Then
> > >             iCtr = iCtr + 1
> > >             myArray(iCtr) = myCell.Value
> > >         End If
> > >     Next myCell
> > >     TotalElements = iCtr
> > >     Call QuickSort(myArray(), 1, TotalElements)
> > >
> > >     iCtr = 0
> > >     Application.EnableEvents = False
> > >     For Each myCell In myRng.Cells
> > >         iCtr = iCtr + 1
> > >         If iCtr > TotalElements Then
> > >             myCell.Value = ""
> > >         Else
> > >             myCell.Value = myArray(iCtr)
> > >         End If
> > >     Next myCell
> > >
> > >     Target.Value = ""
> > >     Application.EnableEvents = True
> > >
> > > End Sub
> > >
> > > Then Insert|Module from the VBE toolbar to insert a general module.  Paste
> > this
> > > in:
> > >
> > > Option Explicit
> > > Sub QuickSort(SortArray, L, R)
> > > 'from Jim Rech
> > >
> > 'http://google.com/groups?threadm=eUXU6tB%249GA.196%40uppssnewspub04.moswest
> > .msn.net
> > > 'one line in your browser
> > >
> > >     Dim i, j, X, Y
> > >     i = L
> > >     j = R
> > >     X = SortArray((L + R) / 2)
> > >
> > >     While (i <= j)
> > >         While (SortArray(i) < X And i < R)
> > >             i = i + 1
> > >         Wend
> > >         While (X < SortArray(j) And j > L)
> > >             j = j - 1
> > >         Wend
> > >         If (i <= j) Then
> > >             Y = SortArray(i)
> > >             SortArray(i) = SortArray(j)
> > >             SortArray(j) = Y
> > >             i = i + 1
> > >             j = j - 1
> > >         End If
> > >     Wend
> > >     If (L < j) Then Call QuickSort(SortArray, L, j)
> > >     If (i < R) Then Call QuickSort(SortArray, i, R)
> > > End Sub
> > >
> > >
> > > bob wrote:
> > > >
> > > > hi dave, those columns should have 15 entries each my mistake. i was
> > hoping
> > > > to have this feature happen auto matically as i enter the names. eg: add
> > > > another name to a blank cell (anywhere, most likely at the bottom) and
> > it
> > > > shuffles amongst the other names to finds its place.
> > > >
> > > > regards
> > > > bob
> > > >
> > > > "Dave Peterson" <ec35720@msn.com> wrote in message
> > > > news:3FA309AB.7399B762@msn.com...
> > > > > Does this mean you  have 13+13+14+14=54 names spread over 4 ranges?
> > > > >
> > > > > If yes, I'd add a temporary worksheet, copy those ranges to A1:A54 and
> > > > sort
> > > > > there.  Then copy|Paste the rows back to the 4 areas in that range (if
> > you
> > > > need
> > > > > to).
> > > > >
> > > > >
> > > > >
> > > > > bob wrote:
> > > > > >
> > > > > > hi guys,
> > > > > >             how can i sort lists of names in several columns eg:
> > c3:c15,
> > > > > > e3:e15,c18:c31,e18:e31. so that the first names (A...) start in c3
> > and
> > > > the
> > > > > > last (...Z) end in e31?
> > > > > >
> > > > > > regards
> > > > > > bob
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > > ec35720@msn.com
> > >
> > > --
> > >
> > > Dave Peterson
> > > ec35720@msn.com
> >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/2/2003 10:35:16 PM
Reply:

Similar Artilces:

Copy data from column that begins with data to new sheet
Hello, I'm currently trying to copy data from a row in excel based on what's in colum B to it's own sheet. I've found some good macros but need some help on how to modify it to fit my needs. The one macro that is almost perfect is AdvFilterRepFiltered.zip on http://www.contextures.com/excelfiles.html#CondFormat That being said, Column B consists of something like this: 3960020262 3960020263 4550050473 4550050474 I want to be able to sort 39600 and 45500 to their own sheet. The macro I mentioned above works great but it will sort everything to it's own sheet that doesn...

sort by the last 2 digits #2
Thank you Frank, great solution george : -- georgeawwa ----------------------------------------------------------------------- georgeawwad's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=432 View this thread: http://www.excelforum.com/showthread.php?threadid=26258 ...

Outlook Folder Columns
Using standalone OL 2007, I tend to use a lot of local Outlook folders. I find that each folder has the folder columns (i.e. - From, To, Subject etc.), I want to elect one set for all folder and let that be the default of creating any new ones. This includes the column size etc. Any help would be appreciated. Thanks Ronnie See this article for how to do it - disregard the title, later it gives you instructions to set the default view for all folders: http://www.outlook-tips.net/howto/grouping.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion...

delete column if blank
Data from A:AB. Row 1 has headers. If there is no data in any of the rows beneath headers, delete entire column. Try this Sub Test() Dim I As Long For I = 28 To 1 Step -1 If Application.WorksheetFunction.CountA(Columns(I)) = 1 Then Columns(I).Delete End If Next I End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message news:eaa3809f-b5ac-4937-b972-344db5dea987@15g2000yqi.googlegroups.com... > Data from A:AB. > Row 1 has he...

Changing Row and Column colors
Is it possible to change the default color of the Row and Column headings (A,B,C ...., 1,2,3 ...)? hooper, You can change the font style/size etc by going to Format | Style | Modify...and changing the Normal style. However, it also changes the font used in the worksheet. You can change the color by right-clicking the desktop and choosing... Appearance (tab) | Advanced (button) | Item (3D objects) However, I doubt if you will like the results. Jim Cone San Francisco, USA "hooper" <tgegan55@yahoo.com> wrote in message news:hnbTd.21751$Tt.20355@fed1read05... > Is i...

sort 2 column in the same time "" as dictionary ""
Hello all I need to make sort of pages to be like a dictionary is that possible I put a test file on the next link http://rapidshare.com/files/382288320/index_test.xls.html I need to sort the 2 yellow column ,to be alphapetic page by page appreciate for help me Thanks Mahmoud ...

Removing Multiple Hyperlinks
I have worked out how to remove a hyperlink from a single cell, but ho do you remove them from complete columns, as I have 80,000 to remove?? Joh -- john8 ----------------------------------------------------------------------- john8b's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1679 View this thread: http://www.excelforum.com/showthread.php?threadid=31988 Select your range (ctrl-a (twice in xl2003) to select all the cells on the worksheet. Then alt-f11 to get to the VBE. ctrl-g to see the immediate window Then type this and hit enter: selection.hyperli...

Sorting a long column of numbers...
Actually, I end up with two "batches" of numbers. All I wanted was to sort the number in ascending order. I highlight the entire worksheet, and go to sort, and I end up with 1/2 of the numbers in order, then the other "1/2" in order after the first group.They are all in as numbers. maxine ...

Two Headers, one subject column and various columns with data
I am trying to create a simple graph chart and get bupkus! The chart consists of: CST RESULTS Subject School District 2005 2006 2005 2006 2007 English-Language Arts 22 29 39 40 43 Mathematics 16 10 18 22 19 Science 29 31 31 31 History-Social Science 11 14 36 32 30 I tried various charts and nothing... what's wrong with my approach? On Fri, 10 Oct 2008, in microsoft.public.excel.charting, ILoveMyCorgi <ILoveMyCorgi@discussions.microsoft.com> said: >I tried various charts and nothing... what's wrong wit...

WHY CAN'T I GET MY COLUMN TO SUM?
i can get certain columns to sum, but not others, why??? "julie c." wrote: > i can get certain columns to sum, but not others, why??? One guess is that the numbers in the col are "text numbers" Try this to convert to real numbers: Input in an empty cell the number 1, then copy it Then right-click on the col > paste special > multiply > ok -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Can you post your worksheet here? Is there anything different about the column that won't sum? Perhaps its formatting, the way the data was enter...

Blocking multiple senders
Hi, several times, I get more than 1 spam msg in my mailbox. Is it possible to add for example 3 senders to the blocked sender list in 1 movement? Thx! C. Are you using Outlook or Outlook Express? If you're using Outlook add the addresses directly to the Junk Senders.TXT file "Chillax666" <waxspinner@hotmail.com> wrote in message news:BQYob.119377$9x6.5391478@phobos.telenet-ops.be... > Hi, > > > several times, I get more than 1 spam msg in my mailbox. Is it possible to > add for example 3 senders to the blocked sender list in 1 movement? I'm a...

Outlook 2003 Signatures and Multiple Accounts
Hi, I'm having an issue that I haven't seen anyone bring up regarding signatures and multiple accounts. In Outlook 2003, I have 5 pop/smtp accounts, one of which is a work account and I have a signature assigned to it for new messages, and for replies and forwards, however this work account is not my default mail account. So what happens is I open the new mail window, which defaults to my personal account, so no signature, then I click on the Accounts button in the new mail, select my work account but the signature doesn't appear. It does work in replies and forwards if th...

Summing values in database according to multiple criteria
Dear All I have a spreadsheet; A B D E Dept Account Staff Amount 1 AA 1002 Peter 10 2 BB 1002 John 20 3 AA 1002 Peter 30 4 CC 1002 Paul 40 I want to sum the content column E by Dept (A), Account Number (B), and Staff (D). How can I do this by formula? As opposed to pivot tables. Many thanks Peter Take a look at the SUMPRODUCT function. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Peter" <Peter@discussions.microsoft.com> wrote in message news:07BDA04E-6B9D-476C-AA59-4AB...

excel sharing multiple users one file
Is there any simple solution to sharing a large excel spreadsheet wit multiple tabs between users at different physical locations? Requirements: 1. one copy of the file centrally located 2. 3-4 users able to access and make changes simultaneously 3. users are not located at the same location (some connected via ver slow dialup connection. I tried something called badblue but that did not work very well fo us. Any suggestions? thank you -- Message posted from http://www.ExcelForum.com Hi though Excel has the feature of shared workbooks ('Tools - Share') this isn't that rel...

finding last non empty cell in a column #3
FYI - The column could be any column not just B -- cparson ----------------------------------------------------------------------- cparsons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1286 View this thread: http://www.excelforum.com/showthread.php?threadid=26238 ...

caps in columns
Hey Everyone, Is it possible to have an entire column that automatically formats so that all typed in letters are capital. If it is possible how would I go about doing this. thanks Chris go to google.com and look for free truetype fonts find an all-caps font install it use that font in excell (format.. cells... font) >-----Original Message----- >Hey Everyone, >Is it possible to have an entire column that automatically >formats so that all typed in letters are capital. If it >is possible how would I go about doing this. > >thanks >Chris > >. > Previo...

Problem with signature and multiple accounts
Hello I have Outlook as part of Office 2003 I have multiple email addresses (accounts) so when Outlook goes on line, it checks three servers I wanted to create a signature for the third account The problem is that when I click on new message, it is always the first account that comes up (without the signature of course) I then tell Outlook to send the message via the third account but of course, the email is already open and the signature does not appear. I should explain that Outlook is using Word 2003 to type emails (but the option is apparently available still) What do I have ...

Collapsing columns in Excel?
I am using Excel XP 2003. Excel's subtotals feature lets you collapse rows and show summary data. It displays little "plus" and "minus" buttons to show or hide the extra rows. I'm sure I have seen a spreadsheet where this same technique has been applied to columns, but I cannot find out how to do it. I'm creating a spreadsheet where I have a bunch of data about pupils in a school. At the end of each year they take 11 exams, and I would like to store all the grades in separate columns for each subject, but I would like to collapse them down to display an ...

Comparing two columns of data and Highlighting the Differences
I have values (mix of text and numbers)in column A and B. Each cell value in column B is also in column A. I want to highlight (Change Fill color to Red) for example) all those cells in column A that are also present in the column B. Any help writting a Macro will be highly appreciated. Thanks in advance. Harapa No macro needed. Select Column A's Data. Use Format/Conditional Formatting. Change "Cell Value Is" to "Formula Is" Assuming A1 is the active cell, enter this formula: =NOT(ISERROR(MATCH(A1,$B$1:$B$500,0))) click Format, click Patterns tab, select Red fill patte...

How do I add a horizontal data line to a column chart?
I have two columns of data which I have charted, now I would like to put the differences between the two. For instance, if I have 926-843= 83, I want to be able to put that number on my chart to show the difference. Thanks -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:2A1331C2-DD17-43B0-B0BF-962B052D4626@microsoft.com... >I have two columns ...

Is there a way I can enter a letter to proceed text a column of ce
I need to enter a source code (letter W) before all the text that I have listed in a column. Example: All of column C has text... MPV001, MPV002, MPV003, ETC. I need to put a "W" in front of all the text. i.e. WMPV001, WMPV002, WMPV003, ETC. Is there a way to do this without manually typing W in all the cells? The spreadsheet has 1500 rows... MelanieWW wrote: > I need to enter a source code (letter W) before all the text that I > have listed in a column. Example: All of column C has text... > MPV001, MPV002, MPV003, ETC. I need to put a "W" in front of...

balance with three columns one row.
ok trying to create a budget that opperates like this Deduction, Start Balance, EndBalance �10 �800 �790 ok its ok if i want to take �10 from the start balance of �800 i get �790, Yippeee , yaeh and yahoooo.... but how do i ..... when the deduction cell is changed to another value, to delete that from the new balance of �790 and not the 800.... please help.... i'ts driving me round the Deduction, Start Balance, EndBalance �10 �800 �780 bend...seriously ...it really is...... --- Message posted from http://www.ExcelForum.co...

Add up contents of column, if nothing in that column add up contents of other column
I'll try to explain this dilemma in simple terms: Code: -------------------- |---A---|---B---| |october|novembr| | beer | beer | | gin | beer | | beer | | | beer | gin | | gin | | | water | gin | -------------------- Basically, say I want to have a total of what was drunk most recently - so it will look in november, add up whats there, but if there is nothing in that column it will look down october and use whatever value is in that column, Thus I should get the following results: Beer = 3 Gin = 3 --- Message posted from http://ww...

Teplate to Track sales of multiple products on per client basis.
Need a template to track on a per client basis multiple sales of three products to multiple buyers. Within this, I need to track product expenses on a per product basis. You will probably have to roll your own in this case. Your question/problem is too open-ended and broad to receive much help. Just start building what you want this "tracking" to look like and come back with questions regarding specific problems as you get to them. HTH Otto "Agriculture" <Agriculture@discussions.microsoft.com> wrote in message news:03792ABB-52FD-4B8A-8B1D-6CBE8B01A8E8@mi...

Counting only displayed cells in the column
I have a worksheet that I would like to be able to count only the displayed cells in a column i.e. I do not want to count hidden cells (rows). If all rows are displayed the total count should equal all rows with contents (counta function), if not - count only those rows displayed. Hi there. There are, at least, two choices: =SUBTOTAL(2,A2:A11) count only the cells which are not hidden by means of filters. OR =SUBTOTAL(102,A2:A11) count only cells which are not hidden either manually or by means of a filter. Regards, Otávio "CEG_Staffer" wrote: >...