Sort on groups of rows?

  • Thread starter Thread starter bhammer
  • Start date Start date
B

bhammer

I have a Projects status worksheet that uses from two to seven rows per
project to store relevant info. Is there a way to sort by, say, project name,
or number, so that the groups of rows for any one project stay together?
 
Don,

OK, so like:
ProjNo, ProjName, LineNo, MyCol1, MyCol2,
500 Needles 1
500 Needles 2
500 Needles 3
510 Heywood 1
510 Heywood 2
560 Simspson 1
560 Simspson 2
560 Simspson 3
560 Simspson 4

Seems awkward, short of going to an MDB.
 
Not really. Excel is built to handle, in your case, one project on one row.
It can be done but only with a good bit of awkward coding (programming).
You would have a far easier time handling all the tasks that you do with all
the data if you placed one project on one row. For instance, you could use
a simple macro to display all the data for one project in any format (rows
and columns) that you want. That would be a simple case of selecting the
project you want to see and clicking on a button. HTH Otto
 
One line will not do, as there may be multiple Tasks for one Project.

I looked in MS Templates for something similar, but. . . This must be a
common business need--a list of active Projects with a few lines of Tasks and
DatesDue and Priorities--sortable by Project Number or Project Name where the
blocks of tasks stay in order, grouped with the Project Name.

The need is not really to view the data for just one project, but more to
sort and view them as a whole.
 
Provide a bit of information on the format of what you have. For instance,
where is the project name relative to all the other information on that
project? The same question for the project number? When I say "where" I
mean what column and row. Information like:
The project name is always in Column A. The project number is always in
Column B. All the data that goes with that project is in Column C starting
one row down from the project name/number. Etc, etc. Another way to ask
the same questions is:
How can the VBA code find the project number/name? How can it find all the
data that goes with that name/number? HTH Otto
 
Otto Moehrbach said:
Provide a bit of information on the format of what you have. For instance,
where is the project name relative to all the other information on that
project? The same question for the project number? When I say "where" I
mean what column and row. Information like:
The project name is always in Column A. The project number is always in
Column B. All the data that goes with that project is in Column C starting
one row down from the project name/number. Etc, etc. Another way to ask
the same questions is:
How can the VBA code find the project number/name? How can it find all the
data that goes with that name/number? HTH Otto
news:[email protected]...
Click to expand...

Otto,

I do have a little VBA experience via MS Access, perhaps you could point me
to an example of how I might make a command button in Excel to
"SortByProjName", "SortByClientName".

What I'm attempting is one worksheet that all three project managers can use
to log the status of their projects, for a 'big' picture' summary review by
the boss. A snapshot of all projects in one, sortable list. Sounds simple
enough.

The problem is that one line of info per project will not do. And entering
one-line tasks with the project name, then group/sort on the project name
will not do.

What that leaves me with is a card-like concept where each project's status
'card' is a few lines long, between three and ten, say.

Col A: ProjType (sortable)
Col B: ProjName (sortable)
(the following stay in line item order within the project)
Col C: ItemNo
Col D: Delegate
Col E: DueDate
Col F: Task
Col G: Priority
 
Otto Moehrbach said:
Provide a bit of information on the format of what you have. For instance,
where is the project name relative to all the other information on that
project? The same question for the project number? When I say "where" I
mean what column and row. Information like:
The project name is always in Column A. The project number is always in
Column B. All the data that goes with that project is in Column C starting
one row down from the project name/number. Etc, etc. Another way to ask
the same questions is:
How can the VBA code find the project number/name? How can it find all the
data that goes with that name/number? HTH Otto
news:[email protected]...
Click to expand...

Otto,

I do have a little VBA experience via MS Access, perhaps you could point me
to an example of how I might make a command button in Excel to
"SortByProjName", "SortByClientName".

What I'm attempting is one worksheet that all three project managers can use
to log the status of their projects, for a 'big' picture' summary review by
the boss. A snapshot of all projects in one, sortable list. Sounds simple
enough.

The problem is that one line of info per project will not do. And entering
one-line tasks with the project name, then group/sort on the project name
will not do.

What that leaves me with is a card-like concept where each project's status
'card' is a few lines long, between three and ten, say.

Col A: ProjType (sortable)
Col B: ProjName (sortable)
(the following stay in line item order within the project)
Col C: ItemNo
Col D: Delegate
Col E: DueDate
Col F: Task
Col G: Priority
 
For purposes of this email, I'll call the sheet that holds your data "Main".
I would first create a new blank sheet named, maybe, "Utility". This sheet
would be a hidden sheet so that the users would not see it.

I get, from what you are saying, that Column A of the Main sheet has nothing
in it but a list of ProjType and nothing else. Never mind that there are
blank cells in Column A between projects. It's important only that ProjType
are in Column A and nothing else. The same thing for ProjName in Column B.
If this is not the case, then what follows will not work.

The methodology of the code that I would use is to first delete all the
range names in the Main sheet. Then clear the entire Utility sheet.

I'll assume the Main sheet has headers in Row 1 and the first entry in
Columns A & B is in Row 2.

Then I would setup a "Do" loop through all the entries in Column A. For
each entry, I would determine the range of that one project as going from
the Column A entry to the cell immediately above the next Column A entry, 7
columns wide. I would assign a range name to that range and the range name
would be the Column A entry. That range name will then be placed in the
first empty cell in Column A of the Utility sheet, and the corresponding
ProjType would be placed in Column B of the Utility sheet in the same row.

Clicking on one of the 2 buttons will set a variable and that variable will
be used to set the "sort by" column in the sort of Columns A & B of the
Utility sheet.

The rest of the code will arrange the project ranges in the order found in
Column A of the Utility sheet. I don't know at this point if I would do
that on the Main sheet or on the Utility sheet. That would depend on the
total number of rows that you are using. Let me know how this sounds to you
and if I can be of any help with the code. HTH Otto
 
Back
Top