+ Reply to Thread
Results 1 to 6 of 6

Sorting while keeping 2 columns of grouped data together

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Atlantic, IA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Exclamation Sorting while keeping 2 columns of grouped data together

    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!
    Last edited by jenny8220; 01-12-2012 at 10:40 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: Sorting while keeping 2 columns of grouped data together

    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

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: Sorting while keeping 2 columns of grouped data together

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-12-2012
    Location
    Atlantic, IA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Sorting while keeping 2 columns of grouped data together

    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!
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: Sorting while keeping 2 columns of grouped data together

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-12-2012
    Location
    Atlantic, IA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Sorting while keeping 2 columns of grouped data together

    Thanks, Pete. I appreciate your help!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1