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.

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.”

...and here is the attachment.