Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Continuous Alphabetization
Hello All, I am entering some database information into an excel spreadsheet--namely, clients' names, addresses, phone #s, etc. I know how to make excel alphabetize all the data according to the name column. However, there are many duplicates that I come accross, and so, I find myself having to alphabetize after every entry. Is there any way to make excel alphabetize every time i move to the next row? Thanks, -Michael -- tomcat017 ------------------------------------------------------------------------ tomcat017's Profile: http://www.excelforum.com/member.php...o&userid=30911 View this thread: http://www.excelforum.com/showthread...hreadid=505886 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Continuous Alphabetization
Hi Michael,
Perhaps an event macro like this will do. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending End Sub HTH Regards, Howard "tomcat017" wrote in message ... Hello All, I am entering some database information into an excel spreadsheet--namely, clients' names, addresses, phone #s, etc. I know how to make excel alphabetize all the data according to the name column. However, there are many duplicates that I come accross, and so, I find myself having to alphabetize after every entry. Is there any way to make excel alphabetize every time i move to the next row? Thanks, -Michael -- tomcat017 ------------------------------------------------------------------------ tomcat017's Profile: http://www.excelforum.com/member.php...o&userid=30911 View this thread: http://www.excelforum.com/showthread...hreadid=505886 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Continuous Alphabetization
It is possible to have the alphabetical list updated after every entry.
In J1 enter 1, in K1 enter 2, and in L1 enter 3. Let your data be contained in A2:C15: surnames in column A, addresses in column B and telephone numbers in column C. Cell E2: =SUMPRODUCT(--($A$2:$A$15<$A2)) Cell F2: =IF(ISBLANK(A2)=TRUE,ROW()*1000,E2+ROW()/1000) Cell G2: =RANK($F2,$F$2:$F$15,5) Cell H2: =ROW(G2)-ROW($G$2)+1 Cell I2: =MATCH($H2,$G$2:$G$15,0) Cell J2: =INDEX($A$2:$C$7,$I2,J$1) Copy J2 to K2 and L2 Copy E2:L2 to E2:L15. Make refinements to suit your needs, for example you could have the alphabetical list on a different worksheet, and you also probably want to get rid of the error cells and the zeroes (use an IF function). Also, this is only a sample list with a maximum of 14 entries. Extend the range to what you require. "L. Howard Kittle" wrote: Hi Michael, Perhaps an event macro like this will do. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending End Sub HTH Regards, Howard "tomcat017" wrote in message ... Hello All, I am entering some database information into an excel spreadsheet--namely, clients' names, addresses, phone #s, etc. I know how to make excel alphabetize all the data according to the name column. However, there are many duplicates that I come accross, and so, I find myself having to alphabetize after every entry. Is there any way to make excel alphabetize every time i move to the next row? Thanks, -Michael -- tomcat017 ------------------------------------------------------------------------ tomcat017's Profile: http://www.excelforum.com/member.php...o&userid=30911 View this thread: http://www.excelforum.com/showthread...hreadid=505886 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
frequency table for continuous variable | Excel Discussion (Misc queries) | |||
Continuous series plot | Charts and Charting in Excel | |||
Printing continuous | Excel Discussion (Misc queries) | |||
Formula for Continuous Services Dates | Excel Worksheet Functions | |||
3D Chart with Continuous Data in all three Axes | Charts and Charting in Excel |