How do I sort blocks of information in Excel?
 

I am making an excel sheet where I need four rows of information about each
transaction. Is there a way to sort this information, while keeping the four
row blocks together?

Answer: How do I sort blocks of information in Excel?
 

Yes, you can sort blocks of information in Excel while keeping the four row blocks together. Here's how:

1. Select the entire range of data that you want to sort. Make sure to include all four rows of information for each transaction.
2. Click on the "Data" tab in the ribbon at the top of the Excel window.
3. Click on the "Sort" button in the "Sort & Filter" section of the ribbon.
4. In the "Sort" dialog box that appears, select the column that you want to sort by from the "Sort by" drop-down list. If you want to sort by multiple columns, click on the "Add Level" button and select the additional columns.
5. Choose the sort order (ascending or descending) for each column by selecting the appropriate option from the "Order" drop-down list.
6. In the "Options" section of the dialog box, make sure that the "My data has headers" box is checked if your data has column headers.
7. In the "Sort On" drop-down list, select "Values".
8. In the "Order" drop-down list, select "Custom List".
9. In the "Custom Lists" dialog box, click on the "NEW LIST" button.
10. In the "List Entries" box, type in the four rows of information for each transaction in the order that you want them to appear. For example, if your four rows of information are "Transaction ID", "Date", "Amount", and "Description", you would type them in that order, one per line.
11. Click "Add" and then "OK" to close the "Custom Lists" dialog box.
12. In the "Sort" dialog box, select the custom list that you just created from the "Order" drop-down list.
13. Click "OK" to sort your data.

Your data should now be sorted by the column(s) you selected, with the four rows of information for each transaction kept together.

How do I sort blocks of information in Excel?
 

Excel has no built-in way to do this. However, it can be done with a
formula to control the sort. Assume for example that the data table to
be sorted is in the range C5:F20. Assume further that the value upon
which the sort is based are in the first row of each block of 4 rows,
in column D.

Create a new column next to F, which will be a new G, and enter the
following formula in G5 and copy down to the last row of your data
table.

=OFFSET(D5,-MOD(ROW()-ROW($5:$5),4),0,1,1)

Change D5 to the column and row of the sort value in the first row of
the data table. Change the $5:$5 to the first row of the data table.
You need the $ characters in the $5:$5 address. In this formula,
change the reference to D to the column upon which the sort it to be
based and change the 5 to the first row number of the entire data
table.

This formula will create a series of values in column G, each block of
four rows having the same value. Now, select the range C5:G20, which
is your original data range plus the new column G. Open the Sort
dialog from the Data menu and specify column G as the sort key column.
Choose ascending or descending as desired. The sort operations will
sort all the rows in your data range according to the values in column
G. Since Excel's sort is order-preservative, each block of cells will
be sorted as a block of 4 rows and if there are duplicate keys, the
block order is preserved.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 6 Nov 2009 08:16:02 -0800, so_lisa
wrote:

I am making an excel sheet where I need four rows of information about each
transaction. Is there a way to sort this information, while keeping the four
row blocks together?