# [SOLVED] Excel 2003-group rows together to sort



## nickib622

Is there any way to "group" (if that is the right term?) two rows together so that when data is sorted they are treated as one individual row/value?

I have a huge database with two rows per client. I need to sort each client's information by the case manager in order to split out each case manager's information for a report. I am stuck. Here is a quick example

Row #1 - client is john doe - case manager is billy kid
Row #2 - dob for john doe 01/01/10 - 

Both rows have other columns with data, but the basic idea is that I want the information from row#2 to be included with row#1 when sorting. Is this even possible?

For ease-of-reading purposes I have at times inserted a blank row between john doe (row #1) and sally smith (row #3) just to make it easier to spot individual information. As far as sorting goes, the only other alternative I have found is to manually sort/split them - ugh! :4-dontkno

I am hoping there is some quicker way to do this! Thanks!


----------



## RSpecianjr

*Re: Excel 2003-group rows together to sort*

Hey nickib622,

Without having the same case manager on Row 1 and Row 2, then you cannot sort by both. If you had that, you could sort by Name, then by Manager. That should group the two Client rows together, then the Managers together.

You may also want to look into custom sorts, though your data doesn't seem to be consistent so sorting is going to be tough any way you look at it. 

If you posted a workbook example of some data that you have, it would help us to try and figure out a possible solution. In the long run, I would recommend having consistent data for all your Client rows. It makes a much neater and easier to work with database.

Hope this helps,

Robert D. Specian Jr.


----------



## nickib622

*Re: Excel 2003-group rows together to sort*

Okay, I tried to make a "dummy" sheet to hopefully clarify. It's hard to explain, but it is a comprehensive database of about 100 clients and the information is consistent, all of the names in the database have the common denominator of receiving the same type of program, just different workers, some who overlap. I am trying to extrapolate each case manager's clients out in order to make each individual case manager (all of Joe Smith's clients, etc., ) a list of their clients, status, what needs to be scheduled, etc. from the master database. I hope this makes more sense!

Uh - how do I post a workbook example? Sorry!


----------



## RSpecianjr

*Re: Excel 2003-group rows together to sort*

Hey Nickib662,

If you zip the file, then you can attach it. Hit 'Go Advanced' then 'Manage Attachments'. This will allow you to browse to the zipped file and upload it.

Thanks,

Robert


----------



## nickib622

*Re: Excel 2003-group rows together to sort*

Okay - I hope this works . . .


----------



## RSpecianjr

*Re: Excel 2003-group rows together to sort*

Hey Nickib662,

What your asking for is going to be really complicated. Because you have the DOB by itself without anything else in the row to distinguish it as being part of the row above it, there is nothing to group together. If your data was consistent throughout the database, then it would be fairly easy. 

There are really two choices. 

1) Move the DOB to line up with the rest of the corresponding data.
2) Create a new column to associate the two lines together.

Instructions on how to accomplish:

1) In the first available Column, Row 2, place this formula:


Code:


=IF(LEFT(A3,3)="DOB",A3,"")

Expand this formula down your dataset. Copy and paste values of the column you placed the above formula in. Sort Column B to blanks and delete the rows of data that is showing. Then simply sort by Case Manager.

2) In the first available Column, Row 2, place this formula:



Code:


=IF(LEFT(A2,3)="DOB",D1&A1,D2&A2)

Expand the formula down your dataset. Copy and paste values of the column you placed the above formula in. This will combine the Case Manager and Client Name to create a unique identifier, linking the DOB with the associated client. This will also be the column you will have to sort off of.

*Note: Option 2 has a fairly big possibility of messing up. If two people with the same name have the same case manager, there is no way to distinguish between the two. You could add the DOB in there to have more integrity.

Hope this helps,

Robert D. Specian Jr.


----------



## nickib622

*Re: Excel 2003-group rows together to sort*

Thanks Robert!

I tried option #2, but I must be doing something wrong. I cut and pasted the formula from your last post and the value I got for column J (which is where I placed the formula) was (for instance row #2" "JoeSmithJohnSmith" but when I sorted data, the names were sorted by case manager name, but all rows with DOB were sorted after client name rows (sigh). It almost seems like the DOB information row isn't a factor in the formula??

What am I doing wrong? Thanks again - I'm almost there!


----------



## RSpecianjr

*Re: Excel 2003-group rows together to sort*

Hey Nickib622,

I guess I am confused. 

You want to keep the DOB row right below the Client name after the sort. The sort should be by Case manager name. 

That should be what the formula does. So 'unsorted' becomes 'sorted'. *see attachments respectively

If a case manager handled two different clients and the data is originally not grouped next to each other, then after the sort, all the case managers clients will be next to each other. Perserving the corresponding DOB's for the clients.

If this isn't quite what you wanted, then perhaps you can provide a before and after pictures of the sort you want. Just let me know = ).

Thanks,

Robert D. Specian Jr.


----------



## nickib622

*Re: Excel 2003-group rows together to sort*

OMG - NOW I get it! Thank you for the before and after pics - that made it clear to me! I was not putting the formula in the DOB rows, just the client name rows - duh!

I LOVE THIS FORUM - THANK YOU - THANK YOU - THANK YOU!!!!!!!!!!!!!! ray:ray:ray:ray:


----------

