Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Sorting - keeping rows together

    I have a spreadsheet listing a customer name in row #1; then the next 2 rows (rows #2 & #3) below it lists the customer's branches and totals loans completed. The last row (#4) totals all the branch loans made. I need to sort my list of customers by the customer loan total, but keep the rows together that shows the branch totals

    Example:
    Row #1 : First American Bank
    Row #2 : First American Bank North 5 loans
    Row #3 : First American Bank East 10 loans
    Row #4 : Customer Total = 15 loans

    Row #5: Hillsdale Bank
    Row #6 : Hillsdale Bank West 7 loans
    Row #7 : Hillsdale Bank South 12 loans
    Row #8 : Customer Total 19 loans

    So now I want to sort descending so that Hillsdale Bank is listed first because they have more loans - but I need to keep rows 5-8 together.

    Any help would be appreciated.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is a solution I came up with to sort a list by group totals in Excel by using a specific format for the totals row and a formula inserted in a column to accomplish the task.

    In each total row, I added the bank name with the underscore character between each word of the name, and an asterisk at the end, for example, " First_American_Bank Total*". The underscore characters cause the row to sort to the bottom of each group when the bank name column is sorted in ascending order. The asterisk is used by the formula to display the total for each row of the group.

    In my attached example, the formula causes every row in column C to show the total number of loans for the specific bank. In cell C2, the formula reads:
    =IF(RIGHT(A2,1) = "*",B2,C3)
    translated: if this row has an asterisk at the end, it is a total row so display the total found in column B, otherwise display the value in the next cell down in column C.

    When you sort by the formula in column C as the primary sort column in descending order , and by "Bank Name" as the secondary sort column in ascending order, you get the largest loan totals at the top, and banks with the same loan totals will be sorted alphabetically in ascending order.

    Jerry


    The great French sculptor Rodin once explained how to carve a statue of a horse. “Get a block of marble, take a hammer and a chisel, and cut away everything that doesn’t look like a horse.”

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    ...and here is the attachment.
    Attached Files Attached Files

Posting Permissions

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