Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting
I have a long list of people extracted from a database. Some of the names are
in the format of "John Smith" or "Jane Smith" on different lines, but some of them are "John and Jane Smith." How can I sort my list so that I can group all of the cells that contain the word "and" in it? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting
Use a helper column.
Insert a new column next to your data. For this example, I'll assume your data is in column A and the helper column is column B. Type this formula into the cell in the helper column next to your first name in column A. =IF(ISNUMBER(SEARCH(" and ",A1)),2,1) Copy this formula down to each cell in your helper column for each name in column A. Then, when you sort, sort by Column B first, then by Column A. HTH, Elkar "dleo" wrote: I have a long list of people extracted from a database. Some of the names are in the format of "John Smith" or "Jane Smith" on different lines, but some of them are "John and Jane Smith." How can I sort my list so that I can group all of the cells that contain the word "and" in it? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting
Works perfectly. Thanks! "Elkar" wrote: Use a helper column. Insert a new column next to your data. For this example, I'll assume your data is in column A and the helper column is column B. Type this formula into the cell in the helper column next to your first name in column A. =IF(ISNUMBER(SEARCH(" and ",A1)),2,1) Copy this formula down to each cell in your helper column for each name in column A. Then, when you sort, sort by Column B first, then by Column A. HTH, Elkar "dleo" wrote: I have a long list of people extracted from a database. Some of the names are in the format of "John Smith" or "Jane Smith" on different lines, but some of them are "John and Jane Smith." How can I sort my list so that I can group all of the cells that contain the word "and" in it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Sorting | Excel Discussion (Misc queries) | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) | |||
sorting | Excel Discussion (Misc queries) |