Sort on groups of rows?

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?
0
bhammer (10)
8/10/2009 9:16:01 PM
excel 39879 articles. 2 followers. Follow

8 Replies
614 Views

Similar Articles

[PageSpeed] 59

You could have a defined name for each block and sort by that block(s)
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"bhammer" <bhammer@discussions.microsoft.com> wrote in message 
news:33E69DD2-F43D-4983-9D64-57A4E34C389A@microsoft.com...
>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? 

0
dguillett1 (2487)
8/10/2009 9:35:17 PM
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.
0
bhammer (10)
8/10/2009 9:51:01 PM
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
"bhammer" <bhammer@discussions.microsoft.com> wrote in message 
news:33E69DD2-F43D-4983-9D64-57A4E34C389A@microsoft.com...
>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? 


0
8/10/2009 9:51:17 PM
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.
0
bhammer (10)
8/10/2009 10:14:01 PM
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
"bhammer" <bhammer@discussions.microsoft.com> wrote in message 
news:A6AC62EB-DAC9-41FE-BE26-C828827D621D@microsoft.com...
> 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. 


0
8/10/2009 10:54:05 PM
"Otto Moehrbach" wrote:

> 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
> "bhammer" <bhammer@discussions.microsoft.com> wrote in message 
> news:A6AC62EB-DAC9-41FE-BE26-C828827D621D@microsoft.com...
 
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
0
bhammer (10)
8/11/2009 4:01:01 PM
"Otto Moehrbach" wrote:

> 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
> "bhammer" <bhammer@discussions.microsoft.com> wrote in message 
> news:A6AC62EB-DAC9-41FE-BE26-C828827D621D@microsoft.com...
 
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
0
bhammer (10)
8/11/2009 4:02:02 PM
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

"bhammer" <bhammer@discussions.microsoft.com> wrote in message 
news:A6DEE525-F2C6-4BBA-B183-B102C374E8F1@microsoft.com...
> "Otto Moehrbach" wrote:
>
>> 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
>> "bhammer" <bhammer@discussions.microsoft.com> wrote in message
>> news:A6AC62EB-DAC9-41FE-BE26-C828827D621D@microsoft.com...
>
> 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 


0
8/11/2009 6:40:51 PM
Reply:

Similar Artilces:

Group olicy for Internet Explorer Proxy settings not being applied.
Some of the computers on our domain are not applying the policy for Internet Explorer Proxy settings The clients are typically Windows XP with Service pack 2 The domain controllers are Windows 2003 R2 with service pack 2 the Policy has the following settings: User Configuration (Enabled) Policies Windows Settings Internet Explorer Maintenance Connection/Proxy Settings Enable proxy settings Protocol Server Port HTTP proxy 8080 Secure proxy 8080 FTP proxy 8080 Gopher proxy 8080 Socks p...

group emails #6
I have a client base in my email address. When I send out a flyer it take way too long to open. Is the slowness due to the email group size 200/300 addresses or the art graphics (logos and clip art) on the flyer? Probably the art. You certainly shouldn't be sending to 2-300 people except with Bcc or a mail merge. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "ms" <anonymous@discussions.microsoft.com> wrote in message news:04a701...

viewing the cell reference as the top row
within a macro I want to have the cell I call be the top row for viewing,= is this possible? yes, it is possible. Use the ActiveWindow.ScrollRow command. For example, setting ActiveWindow.ScrollRow = 10 moves row 10 to the top of the screen. You just need to replace "10" with the row of the cell that you call, it can be a variable, and it should work. Katie "Bob Clark" wrote: > within a macro I want to have the cell I call be the top row for viewing, is > this possible? > God Bless and Thank You!! I've tried several methods BUT how can I now move t...

Imputed Income on Group Term Life in excess of $50k
I'm wondering if anyone knows if there is any functionality in HR or PR that does the calculation of imputed income on the value of group term life insurance (GTLI) in excess of $50k? It is an IRS requirement that the imputed income be reported on the employee's W2 at year end. I've searched all my resources but find nothing. Thx, Frank Yes, HR has this functionality. -- Charles Allen, MVP "Frank" wrote: > I'm wondering if anyone knows if there is any functionality in HR or PR > that does the calculation of imputed income on the value of group term &...

Forwarding NDR's to a distribution group
There is several people in my organization that would like to see ALL NDR's for our mail server. I have generated a specific distribution group for this purpose, added the postmaster SMTP address to it, and added the users who will be members of this group. Finally, I added the group's SMTP address to the SMTP protocol tab. This should in theory force all NDR's to go to that group and in turn be forwarded to its members. However, it doesn't seem to work. Any idea why? If I change the address on SMTP protocol tab to a specific user instead of a group, it seems to work ...

Adding totals by group
I have a query were I run totals by router, I need an if statement that says SumOfQty for router "Loose Cases" = "Loose Cases" + "Sort & Segretate", but also gives me the totals for each of the other routers by them selves. Thank you On Wed, 26 Dec 2007 16:44:01 -0800, Gonzalo <Gonzalo@discussions.microsoft.com> wrote: >I have a query were I run totals by router, I need an if statement that says >SumOfQty for router "Loose Cases" = "Loose Cases" + "Sort & Segretate", but >also gives me the totals for each...

delete all rows except those meeting criterias
Headers on row 1. Data in A:AD Criteria in column B. Delete rows if the value in row B does not equal "Apples" or "oranges" Then create sheets based on value in A, and copy the data onto each sheet based on the data in A and the name of sheet. This will delete the rows for you. Hope this helps! If so, let me know, click "YES" below. Sub DeleteRows() Dim LastRow As Long Dim rw As Long Application.ScreenUpdating = False LastRow = Cells(Rows.Count, "B").End(xlUp).Row For rw = LastRow To 2 Step -1 If Ce...

sorting without affecting the format
Pls tell me how to sort out datas (A-Z or alphabetical oreder) without affecting the format (like spaces or rows in between). Any help would be greatly appreciated. Thank you. Sort the individual blocks separately (data without blanks), or else the space (blanks) will get sorted as well. HTH, Bernie MS Excel MVP "alyndejs" <alyndejs@discussions.microsoft.com> wrote in message news:7799C820-6BFE-4C45-894A-FA787089675D@microsoft.com... > Pls tell me how to sort out datas (A-Z or alphabetical oreder) without > affecting the format (like spaces or rows in between). An...

Hide rows based on selection in drop down list....
Hello. I was windering if anyone can tell me how to automatically hide/show rows in a spreadsheet based on the "Yes" or "No" choice in a drop down list? Can anyone help me here? I am having trouble reading responses on this group, so if you could copy my email with any response it would be really appreciated. sean_goldsworthy@hotmail.com Thanks! Sean ...

Excel Text Sorting? An Easy Solution
Hello everyone, I am trying to figure out a way in Excel to take all the text to the right of a "-" symbol and put it in a new column. For example; If I have a list of albums in column A such as: "Bob Dylan-The Freewheelin" I'd like to have it so that it just says "Bob Dylan" in column A, and "The Freewheelin" in column B. Is there an easy way to do this with text sorting? Or any other function in Excel that could easily do this? It would save me a lot of time in doing it manually. Thanks for taking the time to read this, I greatly appreciate a...

Can you sort text in a random order in Excel
I'm fairly new to excel and trying to figure out if I can sort a column of text in a random order. Any information would be appreicated tbowden, If you insert a column, and fill it with formulas =RAND() to match your text, then select and sort both the text and the formulas based on the formula, you will have randomized your text. HTH, Bernie MS Excel MVP "tbowden" <tbowden@discussions.microsoft.com> wrote in message news:40BF67D9-34A4-4748-9E5C-A1C33C12FAC6@microsoft.com... > I'm fairly new to excel and trying to figure out if I can sort a column of > tex...

Dealing with large group memberships (range retrieval)
I'm working on a project where I need to have a script return the distinguished names of all members of a distribution group. Easy enough, except when the distribution group has more then 1,500 members. I know I can do this with non-native cmdlets or the Exchange cmdlets, but I really need to do this using LDAP\ADSI. The problem appears to be that I need to use range retrieval, however I'm having a hard time finding working code samples that do this. Does anyone have any code samples that can be used to spit out group member DN's for distribution groups that ar...

Moving Rows to Columns
I have a text file that I am importing that has information on multiple rows (each entry has three rows of data). I would move each row of data into a column. Anyone have any ideas/macros on how to do this? Dear Paul Select the row of data, Copy it, move to the cell of your choice (I would recommend on a new worksheet).Edit/Paste Special/Tick the transpose box. Hope this helps Paul Falla >-----Original Message----- >I have a text file that I am importing that has >information on multiple rows (each entry has three rows of >data). I would move each row of data into a co...

Find a Row
I want to do a text search in column A of sheet 1. I want the ROW number where it finds this text first to be displayed. Then I want to select the 3rd columns to the right of the row containing the text. Is that possible in VBA ? Jeff Sub FindIt() Dim sfind As String Dim rownum As Long sfind = "apple" rownum = Application.WorksheetFunction. _ Match(sfind, Range("A:A"), 0) MsgBox Cells(rownum, "D").Value End Sub HTH Jason Atlanta, GA >-----Original Message----- >I want to do a text search in column A of sheet 1. I want the ROW number >where it f...

Match and combine multiple rows into one
Hi all, I have the data set below in three separate columns in one worksheet A3 B3 C3 10 71 120171 10 71 43311 10 71 43312 10 72 101321 10 72 10372 1 421 42101 1 421 42102 I want the data to just match and return the first two rows and combine it into one cell in another worksheet so it would look like this (with the dash): A3 10-71 10-72 1-421 Thanks! Cheers, Chris Add headers to row 1 (if you don't have them) create a new column (column D???) In D2: =a2&"-"&b2 Drag down your list as far as you need. Then use the technique ...

Add public folder to distribution group
hi, anyone know how to acheive this, so that emails sent to a distribution group are populated in a public folder too? Make PF visible and add it to DG. Andy Wolsten wrote: > hi, > > anyone know how to acheive this, so that emails sent to a distribution group > are populated in a public folder too? -- Text from most Windows dialogs can be copied to clipboard with Ctrl-INS. Free productivity applications suit - www.openoffice.org Free Internet calling - www.skype.com Free SQL database Firebird - full support for transaction control, triggers, stored procedures, partial SQ...

sorting and grouping window in report designer is missing
Access 2003 - When I toggle the sorting and grouping window in Report Designer, the toolbar button acts like it's toggling on and off (gets highlighted/loses highlight), but the window isn't appearing anywhere. Any ideas? The box is probably opening off screen. This is not uncommon if you have used different monitors, or tested different resolutions. When you click the button, you will see the Access window lose focus as the (off-screen) Sorting'n'Grouping box gets focus. Then press Alt+Space (to drop its control box), M (for Move), and the arrow keys repeated...

Vlookup and retrieve values 1 row below the normally retrieved val
I'm trying to figure out how to retrieve a value 1 row below the value that would normally be retrieved with a regular vlookup. As an example below, using 1234 as my lookup value, column A-B as the table array, column index =2, and range lookup ="false" I'd like to return the value "5" instead of "2". Can this be done somehow through an formula? COL A COL B Item # Sales 1234 2 5 3 4 2345 ...

Integration Group
I run integrations to integrate items, invoices, customers, etc. I have an integration group that runs all of these together. The problem is when I choose the integration group from the dynamics menu, dynamics closes, and the next thing should be the progress window popping up. It never pops up. If I just run a single integration it works fine. If I run the integration group from "integration manager" it works fine. Has anyone seen this before? I am on Dynamics GP 10.x and Integration Manager 10.x -- Dan Eagan ...

group recipients and naming of sender
I have been receiving emails lately that have either 'undisclosed' as the recipient or showing the senders name as both the sender and recipient. How is this done? Using 2007 Outlook Also have one email that comes in with the sender and recipient names the same and it shows as being forwarded out on arrivial. Why would this be, there is no rule set up to do this to anyone that I can see and I can not find how to tell who it has been forwarded to. You were BCC'd on those messages. Sender sent the message to himself and BCC'd you. -- -Ben- Ben M. Schorr, MVP Roland Schorr &a...

Security Distribution Groups
Hi, how can I set up MS Exchange 2007 to allow a user to send an email from a distribution group? Everytime I try to send using a distribution email address it bounces back saying that I do not have permission to send on that senders behalf. Thanks! The user needs to have SendAs permission on the Distribution Group. This can be done from the Exchange shell using the following syntax: Add-AdPermission "Group Name" -user "User Name" -AccessRights extendedright -ExtendedRights "send as" -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: e...

Printing header row on each page
I know I've done this a million times, but I'm suddenly braindead. How can I print the header row on each page? Thank you Phyllis Go to File/Page Setup/Sheet and set what you want in 'Rows to repeat at top' Andy. "Phyllis Humrich" <HumrichP@cityofreno.com> wrote in message news:0f6a01c37df7$329697b0$a401280a@phx.gbl... > I know I've done this a million times, but I'm suddenly > braindead. How can I print the header row on each page? > Thank you Are you really talking about "page" or "sheet" ... worksheet that is ?...

How to insert an empty row at the same time in more than one sheet?
Hi there, I was wondering how you can insert an empty row the same place i different sheets at the same time... E.g.: If you have 2 (and up, of course) sheets with the exactly sam stuff, and you want to put in an empty row after row 6 (or any othe row for that matter), is there any function you can use? I tried finding out if there was a function for inserting a row, bu apparently you just use "insert..." while clicking on a row, else yo could just use that function and make it run on all sheets.... Can anyone help me? Best Regards Nik -- ncikus ---------------------------...

How to make repeating rows stop
I have a client with Excel 2002. She has row 7 set to repeat. However after row 210 (which becomes a notes section) she wants it to stop. Is this possible? Thanks, carla bradley carla.bradley@DELETETHIS.lgeenergy.com I don't believe this is possible. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Carla Bradley" <anonymous@discussions.microsoft.com> wrote in message news:01e001c48b9f$de40b4e0$a401280a@phx.gbl... > I have a client with Excel 2002. She has row 7 set to > repeat. However after row 210 (which be...

I want to delete duplicate rows of similar info and use the lates.
I am trying to paste new data into a workbook that may already contain the it. I need to filter out the old data and insert or keep the new. The new data is not in the same order as the old. Is there a filter or something that will find a duplicate and give me the option of which row I want to delete? Add the new data to the old data, and go to data/filter/advanced filter and select "unique records only". This will show you unique records of both the old and new data. Then copy these unique records into a new file. Hope this helped. >-----Original Message----- >I am try...