Sorting within Groups

[randlesc]

In my new spreadsheet I have a list of employees in 192 departments. The
sheet is sorted by department, division, then employee name.

I have a blank line between groups--the blank line is for percentage
calculations.

I also have due dates (you may have seen my earlier post). Let's say that
I've managed to color code the due dates (way overdue, just or soon to be
overdue, and fine).

I would like to sort them with by color but keep them within the departments
and not move the blank lines.

When I try this, all the blank lines are sort together. Is there anyway, to
sort the due dates for Dept. A without interfering with the row separating it
from Dept. B. Keep in mind, I have 192 depts to deal with.

Thanks.

 

[FSt1]

hi
by default, excel sorts all Blank row to the bottom and as far as i know,
there is no real way around that.
if your primary sort is by Department then you could assign the blank row a
department number and that way, excel would alway sort the "Blank row" to the
bottom of each department group there by keeping your blank row between
departments...........sorta.

regards
FSt1

 

[Bernd P]

In my new spreadsheet I have a list of employees in 192 departments.  The
sheet is sorted by department, division, then employee name.

I have a blank line between groups--the blank line is for percentage
calculations.

I also have due dates (you may have seen my earlier post).  Let's say that
I've managed to color code the due dates (way overdue, just or soon to be
overdue, and fine).

I would like to sort them with by color but keep them within the departments
and not move the blank lines.

When I try this, all the blank lines are sort together.  Is there anyway, to
sort the due dates for Dept. A without interfering with the row separating it
from Dept. B.  Keep in mind, I have 192 depts to deal with.

Thanks.

Hello,

You can run a macro to create a helper column first which is filled
(all rows of the dept, also the blank rows) with the right rank for
the whole department.
Then sort by this helper column.

Regards,
Bernd

 

[Dave Peterson]

I've never thought it was a good idea to add those rows to the data --
especially if I have to add them manually.

I'd remove those blank lines, then see if a pivottable would work.

If no, then I'd still remove those blank lines, sort the data, and see if
data|subtotals (xl2003 menus) would work.

But in any case, you may be able to use a macro to sort each group. But I'm not
sure how the data is laid out.

Do you really have a blank row between groups? Or is that blank row now filled
with formulas? Or is there a new blank row above/below this percentage line?

If you don't really have blank rows, how do you know what rows are percentage
rows -- a common description in a field (like column A)???

And I bet there's a difference between the first group -- which doesn't have a
blank/percentage row between the data and the header row.