The advanced filter can be a very useful tool when trying to remove duplicate values from a field.
Working example: To remove duplicate position numbers from an establishment report.
- From the toolbar select Data > Filter > Advanced Filter…
- Click the selector tool for the List Range (circled below)
- Click the header bar for the G column (Position No) to select all the data in that column
- It should be automatic but check the Criteria Range is the range for all the data you wish to include in the filter
- Tick the box “Unique records only”
- Click OK.The duplicates (rows 9 and 10) that contain the same position number as row 8 have now been hidden from view.
Do you need a little more flexibility than the autofilter? Create your first advanced filter.
1. Add your filter column and criteria information somehwere in your worksheet
2. Select the entire range and headings you would like to filter
3. On the main menu click Data then Filter then Advanced filter
4. Click in the Criteria range box, then on your worksheet select the filter column and criteria as a range
SideNote: List range should already have values in it, if not, select your range and headings again
5. Click OK and see your newly advance filtered data (in this example it only shows Amounts greater than 500$ now