Rearranging Rows in Excel by Sliding in Word
Jon Von Gunten sent in this week's tip. Excel is excellent at sorting data. Sometimes, though, you need to do a manual sort. Consider this database.
Let's say that you want to sort this data by neighborhood. Any businesses on Maple, Edison, or Prospect streets (or the side streets within a block of those streets) should be grouped together. It would be possible to write a complex IF statement that would attempt to group them. However, for a small list, sometimes it is just easier to shift the rows around.
You might try the method of selecting the cells in a row, using Edit - Cut, then Insert - Cut Cells to oh-so-slowly resequence the data. Jon has found a better way.
Microsoft Word has a feature which Excel is lacking. Jon's method involves moving the data to Word, employing the Word command and then pasting the data back to Excel. Follow these steps.
1. Copy the relevant chunk of rows and columns out of your speadsheet. It is best to note the size of the range, e.g., 118 rows x 5 columns
2. Paste the data into a Microsoft Word document, where it automatically becomes a table and retains all your formatting.
3. In Word, use the little-known SHIFT-ALT-UP-ARROW and SHIFT-ALT-DOWN-ARROW to very speedily slide rows (or selected chunks of rows) up and down at will. Select one or more rows. You can select the entire row or just a portion of the row as shown here.
Hit Shift+Alt+UpArrow several times in order to quickly slide the rows up into position.
4. When you have sequenced the rows as you like, paste them back into Excel, making sure you overwrite the exact same size chunk you copied.
Thanks to Jon Von Gunten for this idea about making use of Word to solve this problem.
This tip was originally published on September 27, 2004. The permanent URL for this page is http://www.mrexcel.com/tip075.shtml.
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.
All contents Copyright
1998-2008 by MrExcel Consulting.