Sorting by Sections Problem

[Guest]

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.

 

[Guest]

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

 

[Guest]

Hi Max,

Your way works perfectly. Thanks a million. Best regards to you & your family.

Freshman

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

---

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.

 

[Jim Cone]

Nice one Max.
--
Jim Cone
San Francisco, USA


"Max" <[email protected]>
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

 

[Leo Heuser]

Nice one, Max.

Cheers
Leo

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

---

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.

 

[Max]

Pleasure` Freshman !
Thanks for the feedback ..

 

[Max]

Thanks for compliment, Jim !

 

[Max]

Thanks for compliment, Leo !