Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Dear all,
I've data in column A and are separated by sections. Each section has a heading. For example: ELS Team: Peter Ken Justin PLP Team Paul Mary Byran May QWS Team Wilson Tim Isa If sometimes later, the name "QWS Team" is changed to "DGK Team" and I want to sort the teams again in alphabetical order of team name so that "DGK Team" is in front of "ELS Team". Please advise how can I sort the data by section name like the example above. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Perhaps one way. Try this play ..
(source data must start in row2 down) Assuming source data as posted is within A2:A17 (range is inclusive of the blank row below the last section) Put in B2: =IF(ISNUMBER(SEARCH("Team",A2)),LEFT(A2),LEFT(B1)) Copy B2 down to B17 (include the blank cell below the last section) [Leave B1 empty] Now, try sorting the "sections" in col A by the helper col B Select A2:B17, click Data Sort Check "No header row" Sort by column B (ascending) OK Test out the sorting with various changes to the team names Seems to work ok here -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote: Dear all, I've data in column A and are separated by sections. Each section has a heading. For example: ELS Team: Peter Ken Justin PLP Team Paul Mary Byran May QWS Team Wilson Tim Isa If sometimes later, the name "QWS Team" is changed to "DGK Team" and I want to sort the teams again in alphabetical order of team name so that "DGK Team" is in front of "ELS Team". Please advise how can I sort the data by section name like the example above. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Hi Max,
Your way works perfectly. Thanks a million. Best regards to you & your family. Freshman "Max" wrote: Perhaps one way. Try this play .. (source data must start in row2 down) Assuming source data as posted is within A2:A17 (range is inclusive of the blank row below the last section) Put in B2: =IF(ISNUMBER(SEARCH("Team",A2)),LEFT(A2),LEFT(B1)) Copy B2 down to B17 (include the blank cell below the last section) [Leave B1 empty] Now, try sorting the "sections" in col A by the helper col B Select A2:B17, click Data Sort Check "No header row" Sort by column B (ascending) OK Test out the sorting with various changes to the team names Seems to work ok here -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote: Dear all, I've data in column A and are separated by sections. Each section has a heading. For example: ELS Team: Peter Ken Justin PLP Team Paul Mary Byran May QWS Team Wilson Tim Isa If sometimes later, the name "QWS Team" is changed to "DGK Team" and I want to sort the teams again in alphabetical order of team name so that "DGK Team" is in front of "ELS Team". Please advise how can I sort the data by section name like the example above. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Nice one Max.
-- Jim Cone San Francisco, USA "Max" wrote in message Perhaps one way. Try this play .. (source data must start in row2 down) Assuming source data as posted is within A2:A17 (range is inclusive of the blank row below the last section) Put in B2: =IF(ISNUMBER(SEARCH("Team",A2)),LEFT(A2),LEFT(B1)) Copy B2 down to B17 (include the blank cell below the last section) [Leave B1 empty] Now, try sorting the "sections" in col A by the helper col B Select A2:B17, click Data Sort Check "No header row" Sort by column B (ascending) OK Test out the sorting with various changes to the team names Seems to work ok here -- Max Singapore http://savefile.com/projects/236895 xdemechanik |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Nice one, Max.
Cheers Leo "Max" skrev i en meddelelse ... Perhaps one way. Try this play .. (source data must start in row2 down) Assuming source data as posted is within A2:A17 (range is inclusive of the blank row below the last section) Put in B2: =IF(ISNUMBER(SEARCH("Team",A2)),LEFT(A2),LEFT(B1)) Copy B2 down to B17 (include the blank cell below the last section) [Leave B1 empty] Now, try sorting the "sections" in col A by the helper col B Select A2:B17, click Data Sort Check "No header row" Sort by column B (ascending) OK Test out the sorting with various changes to the team names Seems to work ok here -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote: Dear all, I've data in column A and are separated by sections. Each section has a heading. For example: ELS Team: Peter Ken Justin PLP Team Paul Mary Byran May QWS Team Wilson Tim Isa If sometimes later, the name "QWS Team" is changed to "DGK Team" and I want to sort the teams again in alphabetical order of team name so that "DGK Team" is in front of "ELS Team". Please advise how can I sort the data by section name like the example above. Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Pleasure` Freshman !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote in message ... Hi Max, Your way works perfectly. Thanks a million. Best regards to you & your family. Freshman |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Thanks for compliment, Jim !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jim Cone" wrote in message ... Nice one Max. -- Jim Cone San Francisco, USA |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by Sections Problem
Thanks for compliment, Leo !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Leo Heuser" wrote in message ... Nice one, Max. Cheers Leo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting problem . . . | Excel Discussion (Misc queries) | |||
Problem sorting cells containing hyperlinks | Excel Worksheet Functions | |||
Date sorting problem | Excel Discussion (Misc queries) | |||
An instant sorting problem | Excel Worksheet Functions | |||
Sorting problem | Excel Worksheet Functions |