Sorting by Sections Problem

F

Freshman

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.
 
M

Max

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
 
F

Freshman

Hi Max,

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

Freshman

Max said:
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

Freshman said:
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.
Click to expand...
Click to expand...
 
J

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
 
L

Leo Heuser

Nice one, Max.

Cheers
Leo


Max said:
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

Freshman said:
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.
Click to expand...
Click to expand...
 
Top