| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging Alphabetical List
I have several cities, in alphabetical order, listed in column A, such as:
A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
One way...
In column B (insert a new column B temporarily if needed), add a formula to extract the 2-character state abbreviation: =RIGHT(A1,2) and copy down through all your rows of data (this example assumes your data starts in A1). Select all the data and sort by column B. Hope this helps, Hutch "F. Lawrence Kulchar" wrote: I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
Perfect...Thanks
"Tom Hutchins" wrote in message ... One way... In column B (insert a new column B temporarily if needed), add a formula to extract the 2-character state abbreviation: =RIGHT(A1,2) and copy down through all your rows of data (this example assumes your data starts in A1). Select all the data and sort by column B. Hope this helps, Hutch "F. Lawrence Kulchar" wrote: I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
This formula gives me ONLY the states in column B.
What next? "Tom Hutchins" wrote in message ... One way... In column B (insert a new column B temporarily if needed), add a formula to extract the 2-character state abbreviation: =RIGHT(A1,2) and copy down through all your rows of data (this example assumes your data starts in A1). Select all the data and sort by column B. Hope this helps, Hutch "F. Lawrence Kulchar" wrote: I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
YES...you are correct...it will work!!
"Tom Hutchins" wrote in message ... One way... In column B (insert a new column B temporarily if needed), add a formula to extract the 2-character state abbreviation: =RIGHT(A1,2) and copy down through all your rows of data (this example assumes your data starts in A1). Select all the data and sort by column B. Hope this helps, Hutch "F. Lawrence Kulchar" wrote: I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
In the next column enter =RIGHT(A1,2) and pull down. Now sort both column
using the right column to sort on. Then delete helper column. HTH Regards, Howard "F. Lawrence Kulchar" wrote in message ... I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
Thanks...perfect!
"L. Howard Kittle" wrote in message . .. In the next column enter =RIGHT(A1,2) and pull down. Now sort both column using the right column to sort on. Then delete helper column. HTH Regards, Howard "F. Lawrence Kulchar" wrote in message ... I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
DataText to ColumnsDelimited by "Comma"Finish
Now sort on the Stae column Gord Dibben MS Excel MVP On Wed, 16 Apr 2008 12:30:00 -0700, F. Lawrence Kulchar wrote: I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging Alphabetical List
Thanks...very good!
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... DataText to ColumnsDelimited by "Comma"Finish Now sort on the Stae column Gord Dibben MS Excel MVP On Wed, 16 Apr 2008 12:30:00 -0700, F. Lawrence Kulchar wrote: I have several cities, in alphabetical order, listed in column A, such as: A Albany, NY Anchoridge, AK Atlanta, GA Baltimore, MD Baton Rouge, LA etc. Wichita, KS QUESTION: hOW DO I REARRANGE THEM ALPHABETICAL, BY STATE??? (instead of 'by city')?? Thanks, FLKulchar Wich |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Alphabetical List Box | Excel Discussion (Misc queries) | |||
| alphabetical list | New Users to Excel | |||
| how do i do an alphabetical list of songs for a booklet | Excel Discussion (Misc queries) | |||
| Alphabetical list of Numbers are not correct! | Excel Discussion (Misc queries) | |||
| creating pages from an alphabetical list | Excel Worksheet Functions | |||
Hybrid Mode
