Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Tables / June 2007

Tip: Looking for answers? Try searching our database.

Sort alpha in 1 column with gaps?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
k1ngy - 21 Jun 2007 22:34 GMT
How do i sort a-z column 2 only. I can do it if i have a, b, c, d mixed in
column 2 but not if there are gaps, i have shown example below
Any help,
Thanks
A        A    A    A
B    D    B    B    B
C        C    C    C
D    B    D    D    D

Like this
A        A    A    A
B    B    B    B    B
C        C    C    C
D    D    D    D    D
Graham Mayor - 22 Jun 2007 10:59 GMT
It must be the heat? Something is causing correspondents to write gibberish
this morning. :(
Can you explain in English what it is you are trying to do?

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> How do i sort a-z column 2 only. I can do it if i have a, b, c, d
> mixed in column 2 but not if there are gaps, i have shown example
[quoted text clipped - 11 lines]
> C C C C
> D D D D D
Suzanne S. Barnhill - 22 Jun 2007 13:50 GMT
Think of it as a fruit machine ("slot machine" Stateside). He's got columns
with letters A-Z, and he wants to sort each column separately so that it is
in alpha order, but some of the columns have gaps between letters, and those
have to be placed where they belong (not rise to the top) in order to keep
the B with the other B's, etc. The only solution I can think of would be to
actually insert the appropriate letter and format it as white or possibly
Hidden.

Signature

Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

> It must be the heat? Something is causing correspondents to write gibberish
> this morning. :(
[quoted text clipped - 15 lines]
> > C C C C
> > D D D D D
Graham Mayor - 22 Jun 2007 15:05 GMT
That crystal ball again? ;)

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Think of it as a fruit machine ("slot machine" Stateside). He's got
> columns with letters A-Z, and he wants to sort each column separately
[quoted text clipped - 31 lines]
>>> C C C C
>>> D D D D D
Suzanne S. Barnhill - 22 Jun 2007 20:37 GMT
Well, if you look at the examples he gives...

Signature

Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

> That crystal ball again? ;)
>
[quoted text clipped - 33 lines]
> >>> C C C C
> >>> D D D D D
Helmut Weber - 24 Jun 2007 10:56 GMT
Hi Suzanne,
hi Graham,

as there was no other amusement on a lazy sunday morning...

I wonder whether this will help the OP,
but is was a nice exercise:

Sub Test1()
Dim bEmp As Boolean ' is empty cell
Dim oTbl As Table
Dim oclm As Column
Dim oCll As Cell
Dim lngCll As Long
Dim cArr() As String
Set oTbl = Selection.Tables(1)
For Each oclm In oTbl.Columns
  bEmp = False
  oclm.Select
  ' is there an empty cell?
  For Each oCll In Selection.Cells
     If Len(oCll.Range.Text) = 2 Then
        bEmp = True
        Exit For
     End If
  Next
  If bEmp = False Then
     ' no empty cell then just sort the column
     Selection.Sort ExcludeHeader:=False, _
     SortColumn:=True, _
     sortorder:=wdSortOrderAscending
  Else
     ' there is at least one empty cell
     Selection.Sort ExcludeHeader:=False, _
     SortColumn:=True, _
     sortorder:=wdSortOrderAscending
     lngCll = 0
     ' count not empty cells
     For Each oCll In Selection.Cells
        If Len(oCll.Range.Text) > 2 Then
           lngCll = lngCll + 1
        End If
     Next
     ' set up an array for the values of the not empty cells
     ReDim cArr(1 To lngCll)
     lngCll = 0
     ' put the sorted values into the array
     For Each oCll In Selection.Cells
        If Len(oCll.Range.Text) > 2 Then
           lngCll = lngCll + 1
           cArr(lngCll) = _
           Left(oCll.Range.Text, Len(oCll.Range.Text) - 2)
        End If
     Next
     ' unsort the column
     ActiveDocument.Undo 1
     lngCll = 0
     ' put the values from the array of not empty cells
     ' into the not empty cells
     For Each oCll In Selection.Cells
        If Len(oCll.Range.Text) > 2 Then
           lngCll = lngCll + 1
           oCll.Range.Text = cArr(lngCll)
        End If
     Next
  End If
Next
End Sub

I've used Word's sort algorithm in order to avoid
having to include something like bubblesort
and thus start a discussion about sorting, possibly.

Have a nice day.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.