Alphabetical order possible? or not
 

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

Alphabetical order possible? or not
 

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
wrote:


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

Alphabetical order possible? or not
 

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

Alphabetical order possible? or not
 

Gord Dibben Wrote:
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
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 ?


--
OSSIE
------------------------------------------------------------------------
OSSIE's Profile: http://www.excelforum.com/member.php...o&userid=31638
View this thread: http://www.excelforum.com/showthread...hreadid=554258

Alphabetical order possible? or not
 

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


--
u0107
------------------------------------------------------------------------
u0107's Profile: http://www.excelforum.com/member.php...o&userid=11329
View this thread: http://www.excelforum.com/showthread...hreadid=554258

Alphabetical order possible? or not
 

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
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 ?