Hi all, not sure if this is possible or not. beginning on row 6 I
have several blocks of data. Each block is exactly 9 rows long, 40
columns wide. In the first row of each block(and only the first row
of each block), column A, there is an identifier. I would like to
sort based on that identifier, but each block has to stay in tact. So
essentially, each block of 9 rows must move together as if it were a
single row of data. Thanks for all of your help!!

Perhaps this approach:

GROUP each section, with settings to apply summary rows
above the data(see example)
then collapse each section to one row...

When you sort the visible rows, the associated
data moves with it.

Simple Example:

A2: Alpha
A3: 1
A4: 2
A5: 3
A6: 4
A7: 5
A8: Bravo
A9: 10
A10: 20
A11: 30
A12: 40
A13: 50

Select A3:A7
<data<group and outline<group...Check:Rows...Click: OK

Select A9:A13
[F4].....shortcut for <edit<repeat

Repeat for as many groups as you need.
Collapse all groups (by clicking the [1] in the upper left)
Sort the data range
Expand the groups (by clicking the [2] in the upper left)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Steve" wrote in message
...
Hi all, not sure if this is possible or not. beginning on row 6 I
have several blocks of data. Each block is exactly 9 rows long, 40
columns wide. In the first row of each block(and only the first row
of each block), column A, there is an identifier. I would like to
sort based on that identifier, but each block has to stay in tact. So
essentially, each block of 9 rows must move together as if it were a
single row of data. Thanks for all of your help!!

I would use a couple of helper columns.

I'd insert two new columns to the left of column A.

In the new A6, I'd put this formula:
=if(mod(row(),9)=6,c6,a5)

In the new B6, I'd put this formula:
=row()

Then drag those formulas down the columns as far as your data.

Then I'd convert those formulas to values:
Select A6:Bxxx
edit|copy
edit|paste special|values

And sort your data using these two helper columns.

After you're done, delete the helper columns (or hide them).

Steve wrote:

Hi all, not sure if this is possible or not. beginning on row 6 I
have several blocks of data. Each block is exactly 9 rows long, 40
columns wide. In the first row of each block(and only the first row
of each block), column A, there is an identifier. I would like to
sort based on that identifier, but each block has to stay in tact. So
essentially, each block of 9 rows must move together as if it were a
single row of data. Thanks for all of your help!!

--

Dave Peterson

Hi Ron. Thanks for the response. That would work for me in the back
office. But this will be a tool that others will have access to.
Instructing them to do anything other than a mindless click of a
button would be worthless, and only create thousands of questions! I
have that Row1ColA cell I mentioned earlier formulated to give a valid
search criteria. So my hopes were to have the users input info on the
bottom of the list, and the "button" would take care of the rest.

On Mar 13, 3:41*pm, "Ron Coderre"
wrote:
Perhaps this approach:

GROUP each section, with settings to apply summary rows
above the data(see example)
then collapse each section to one row...

When you sort the visible rows, the associated
data moves with it.

Simple Example:

A2: Alpha
A3: 1
A4: 2
A5: 3
A6: 4
A7: 5
A8: Bravo
A9: 10
A10: 20
A11: 30
A12: 40
A13: 50

Select A3:A7
<data<group and outline<group...Check:Rows...Click: OK

Select A9:A13
[F4].....shortcut for <edit<repeat

Repeat for as many groups as you need.
Collapse all groups (by clicking the [1] in the upper left)
Sort the data range
Expand the groups (by clicking the [2] in the upper left)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Steve" wrote in message

...



Hi all, not sure if this is possible or not. *beginning on row 6 I
have several blocks of data. *Each block is exactly 9 rows long, 40
columns wide. *In the first row of each block(and only the first row
of each block), column A, there is an identifier. *I would like to
sort based on that identifier, but each block has to stay in tact. *So
essentially, each block of 9 rows must move together as if it were a
single row of data. *Thanks for all of your help!!- Hide quoted text -

- Show quoted text -