Alphabetical order possible? or not

can you have excel as you enter data arrange it in alphabetical order by last names or names?

Using event code I'm sure you could get Excel to sort your entries as you
entered them, but I would find that very annoying.

I prefer to enter my names in a column in any order and when done, sort that
column at one go.

But here is some code to sort as you enter.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value <> "" Then
Call Macro2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Sub Macro2()
Range("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the event code and the macro2 into that module.

Close that module and hit ALT + Q to go back to Excel.

Start typing names in column A


Gord Dibben MS Excel MVP




On Wed, 21 Jun 2006 13:39:02 -0500, OSSIE
<OSSIE.29rm8q_1150915215.1851@excelforum-nospam.com> wrote:

>
>can you have excel as you enter data arrange it in alphabetical order by
>last names or names?

DATA | CLASSIFY

"OSSIE" escreveu:

>
> can you have excel as you enter data arrange it in alphabetical order by
> last names or names?
>
>
> --
> OSSIE
> ------------------------------------------------------------------------
> OSSIE's Profile: http://www.excelforum.com/member.php...o&userid=31638
> View this thread: http://www.excelforum.com/showthread...hreadid=554258
>
>

Originally Posted by Gord Dibben

Using event code I'm sure you could get Excel to sort your entries as you
entered them, but I would find that very annoying.

I prefer to enter my names in a column in any order and when done, sort that
column at one go.

But here is some code to sort as you enter.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value <> "" Then
Call Macro2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Sub Macro2()
Range("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the event code and the macro2 into that module.

Close that module and hit ALT + Q to go back to Excel.

Start typing names in column A


Gord Dibben MS Excel MVP




On Wed, 21 Jun 2006 13:39:02 -0500, OSSIE
<OSSIE.29rm8q_1150915215.1851@excelforum-nospam.com> wrote:

>
>can you have excel as you enter data arrange it in alphabetical order by
>last names or names?

Hi and thnak you for your help
one more question on this last name and name have to be in the same colum correct co is it possible to have the last name on column A and the name on B ?

One way that I normally do is:

1) Define a Parameters Tab where all parameters are stored.
2) In this Parameters Tab, in one of the cells enter a formula using Countif over a suitably large range (say 3000 rows when you expect to have about 1000 or slightly more rows), which tells you how many rows of names are populated.
3) Using Insert-Name-Define, name this cell suitably say No_of_name_rows.
4) go back to your tab and create a button.
5) Using Record Macro actually sort your name list while recording your macro.
6) Modify the recorded macro (after stopping) to use the value of the No_of_name_rows which you defined in step 3.
7) Associate the macro with the button you created in step 4.

Now you can enter a few names and click the button and get the data sorted.

As far as getting first name and last name in columns A and B but using a combination of First and last name, you can do that by using Concatenate function in the corresponding cell in Column C on the same row.

Please write to me in case of questions.

Uttam

Try this modification.

Either set your ENTER key to move right upon entry or use the Tab key to move
right out of column A to B

Enter last name in col A and first name in col B.

When you hit ENTER out of col B, A and B will sort by Last Name

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1:B100")) Is Nothing Then
With Target
If .Value <> "" Then
Call Macro2
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Sub Macro2()
Range("A:B").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Gord

On Thu, 22 Jun 2006 10:20:50 -0500, OSSIE
<OSSIE.29t7vn_1150989902.2278@excelforum-nospam.com> wrote:

>Hi and thnak you for your help
>one more question on this last name and name have to be in the same
>colum correct co is it possible to have the last name on column A and
>the name on B ?