Microsoft Office Tutorials and References
In Depth Information
You can also use the new Flash Fill feature to join columns without using formulas. Just provide an example or
two in an adjacent column, and press Ctrl+E.
Rearranging columns
If you need to rearrange the columns in a worksheet, you could insert a blank column and then drag another
column into the new blank column. But then the moved column leaves a gap, which you need to delete.
Here's an easier way:
1. Click the column header of the column you want to move.
2. Choose Home Clipboard Cut.
3. Click the column header to the right of where you want the column to go.
4. Right-click and choose Insert Cut Cells from the shortcut menu that appears.
Repeat these steps until the columns are in the order you desire.
Randomizing the rows
If you need to arrange rows in random order, here's a quick way to do it. In the column to the right of the data,
insert this formula into the first cell and copy it down:
=RAND()
Then sort the data using this column. The rows will be in random order, and you can delete the column.
Matching text in a list
You may have some data that you need to check against another list. For example, you may want to identify the
data rows in which data in a particular column appears in a different list. Figure 16-16 shows a simple example.
The data is in columns A:C. The goal is to identify the rows in which the Member Num appears in the Resigned
Members list, in column F. These rows can then be deleted.
Here's a formula entered in cell D2, and copied down, that will do the job:
=IF(COUNTIF(\$F\$2:\$F\$22,B2)>0,”Resigned”,”” )
This formula displays the word Resigned if the Member Num in column B is found in the Resigned Members
list. If the member number not found, it returns an empty string. If the list is sorted by column D, the rows for
all resigned members will appear together and can be quickly deleted.
This technique can be adapted to other types of list matching tasks.
Search JabSto ::

Custom Search