"How To Excel" Mini-Tutorials|
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"
Extracting Unique Items From Data In A Column
Anytime you have a column of data that contains duplicate items
and you need just a list of the unique items in that column,
Excel has an option that allows you to easily do this, but few
Excel users know about it.
Say for example that you have a list of hundreds, maybe even
thousands of names and many of these names are in the list
multiple times. You need to pull out a list of names with no
duplicates. I've seen Excel users first sort the list to make it
easier to manually copy the individual names. But this is still
very inefficient and time consuming.
Excel's time-saving option for this task is called Advanced
Filter and here's how it works.
Of the Excel users who have discovered and use Advanced Filter,
many are often frustrated that Excel, it appears, doesn't allow them to select
a different sheet in which to paste the unique filtered records.
- Select the list of data to be filtered (only one column wide).
If your list doesn't have a heading, add one, or include the
blank cell immediately above the list in your selection.
- From the Data menu, select Filter, then Advanced Filter.
- In the Advanced Filter dialog box, ensure that the 'List
Range' is actually the cells you have selected. Often Excel
will select, by default, the entire range of columns and rows
surrounding your selection. To be sure, you can select your
list again while the 'List Range' box is selected.
- Click to select the 'Unique Records Only' option.
- You can choose to 'Filter List In Place', or you can choose
'Copy to another location' and select a cell on the current
sheet where you would like the unique items to be copied,
leaving the original list untouched.
Here's a secret that I use to get around that problem.
- First select the sheet and cell where you want your extracted records
to be copied.
- Select the Advanced Filter option.
- Click the sheet tab containing the list.
- Select the other options as explained above.
Why not print (CTRL+P) this tip and share it with your friends and associates?
Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.|
Copyright © 2003 All Rights Reserved by Francis Hayes (The Excel Addict)