Sorting while keeping 2 columns of grouped data together

[jenny8220]

I have a document that has a list of representatives in Column A. Underneath each of the representatives' names is their company name, address, and phone numbers. In Column B, I have the clients I serve that are represented by the persons in Column A. I need to be able to sort the representatives in Column A alphabetically while keeping their contact information grouped with it. In addition, I need for the individuals they represent in Column B to also remain grouped with them. An example of my data is below:

Column A--------------------Column B

John Smith-------------------Bob Jones
Smith Industries--------------Sally Hansen
100 1st Street----------------James Johnson
New York, NY 10101
800-123-4567

Jane Doe---------------------Adam Smith
Jane Doe Inc.-----------------Mary Miller
123 2nd Street----------------Sarah James
New York, NY 10101
800-321-1234



I know that with the information set up as it is, I won't be able to alphabetize by last name, but that's fine with me. As long as I can alphabetize either by first name or business name I will have what I need. Thank you in advance for any suggestions!

[Pete_UK]

Could you attach a sample workbook with a few examples of your data?

I would suggest setting up a sequential number in another column which has an integer value for the first row of each record and then a fractional value for the subsidiary rows, something like this in C2:

=IF(A2="","",IF(A1="",INT(MAX(C$1:C1))+1,C1+0.1))

which is then copied down. In another column you can then get the name for the first row of each record (and it could be in surname_forename order), and then sort on both these columns (but would need a sample to test it all out on).

Hope this helps.

Pete

[Pete_UK]

Jenny,

In the absence of a sample file, I can only work on the two examples you quoted in your post, so I've put together a file that demonstrates what I described earlier. The data is in columns A and B with headers in row 1 and a blank row between each multi-line record. This formula is in C2:

Please Login or Register  to view this content.

and this one in D2:

Please Login or Register  to view this content.

These are copied down beyond your data (to row 19 in the example file).

What you should now do is to fix the values in columns C and D, and then sort columns A to D using D as the first sort field and C as the second sort field - this will retain the order that you want within each record, as well as retaining the blank rows between records. After sorting you can remove the helper columns C and D.

Hope this helps.

Pete

[jenny8220]

Here's a sample of how I have my workbook set up - and thanks so much for your help. I will try and use your example and formulas to see if I can make this work!

[Pete_UK]

Hi Jenny,

I've been away for a few days, so sorry for the delay in replying. I've attached a workbook based on your latest data which shows the formula adapted to your latest layout. This formula is in C4:

Please Login or Register  to view this content.

and this one is in D4:

Please Login or Register  to view this content.

These formulae are then copied down (to row 53 in your example). As advised before, you can now fix the formulae to values and then sort the 4 columns using Column D as the primary field and column C as the secondary field. This will also give you just one blank row between each record, unlike the multiple blank rows you have at present.

Hope this helps.

Pete

[jenny8220]

Thanks, Pete. I appreciate your help!!