Remove Duplicates in Excel 2013 List

If I need a list of unique items from a long list, I usually use an Advanced Filter, because it leaves the original list alone, and extracts a list of unique records. The filter can be done in place, or sent to another range, on the same sheet, or a different sheet. It’s easier in newer versions of Excel though – here’s how to quickly remove duplicates in Excel 2013.

Continue reading “Remove Duplicates in Excel 2013 List”

Click a Cell to Filter Excel Column

Click on a cell in an Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.

.filterclick01

To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.

filterclick02

FilterClick Sample File

This filtering trick is accomplished with a bit of programming. There is an event procedure – Worksheet_SelectionChange – that automatically runs when you select a cell on the worksheet . You can turn the FilterClick feature on or off, by clicking on a cell that’s named FilterStatus. When you click on that cell, its value automatically toggles between On and Off.

The SelectionChange procedure checks the value in that cell, and only filters the column if the FilterStatus cell says “On”.

filterclick03

Update for Working With Excel Tables

I created the sample file a few years ago, and have just updated it to include a worksheet that has the data in a formatted Excel table. The code is almost identical – only one line is different if you’re using a table.

In the sample code shown below, there are two versions of the line that sets the range rngF. For formatted tables, use the first line, and for non-table AutoFilters, use the second line.

  • Set rngF = ActiveSheet.ListObjects(1).Range ‘for tables
  • ‘Set rngF = ActiveSheet.AutoFilter.Range ‘for AutoFilter ranges

Type an apostrophe at the start of the line that you don’t need, and remove the apostrophe at the start of the line that you do need.

You can see more examples of List AutoFilter VBA here.

The FilterClick Code

Here is the code, and you can copy it to the worksheet module for the sheet that your list is on. Remember to fix the Set rngF lines, so one has an apostrophe, and one doesn’t – or delete the line that you don’t need.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngF As Range
Dim rngFS As Range
Dim lRow As Long
Dim lCol As Long
Set rngF = ActiveSheet.ListObjects(1).Range 'for tables
'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges
Set rngFS = ActiveSheet.Range("FilterStatus")
lCol = rngF.Columns(1).Column - 1
lRow = rngF.Columns(1).Row
If Target.Count > 1 Then GoTo exitHandler
If Target.Address = rngFS.Address Then
  If rngFS.Value = "On" Then
    rngFS.Value = "Off"
  Else
    rngFS.Value = "On"
  End If
End If
If UCase(rngFS.Value) = "ON" Then
  If Not Intersect(Target, rngF) Is Nothing Then
    If Target.Row > lRow Then
      rngF.AutoFilter Field:=Target.Column - lCol, _
          Criteria1:=Target.Value
    ElseIf Target.Row = lRow Then
      rngF.AutoFilter Field:=Target.Column - lCol
    End If
  End If
End If
exitHandler:
  Exit Sub
End Sub

Download the Sample File

You can download the sample file, with the code for both versions – table and non-table – on my Contextures website. Go to the Excel Sample Files page, and in the Filters section, look for FL0021 – FilterClick.

_______________

Use Slicers to Filter a Table in Excel 2010

Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table.

slicerstableaj01

Slicers take up some room on the worksheet, but you can quickly see what filters have been applied. And, unlike Report Filters, Slicers show you what is available in the other fields, after you have applied a filter.

Slicers for Excel Tables

In Excel 2013, Slicers were enabled for named tables too, so you can filter your data with a single click. They work just like Pivot Table Slicers, and are especially handy if you’re doing a presentation. You can click one of the big Slicer buttons, instead of fumbling through the filter drop downs.

slicerstableaj02

Workaround for Excel 2010 Tables

Slicers don’t work on Excel 2010 tables, but if you’re using that version, there’s good news – AlexJ has developed a workaround.

There are a couple of limitations:

  • You need a unique identifier in each table row.
  • Changes made manually to the field filters on the table are not reflected on the slicers (you might want to hide the table filters)

slicerstableaj03

Add a Pivot Table and Slicers

From the Excel table’s data, AlexJ built a pivot table, with the ID field in the Row Labels area. Next, he added two Slicers for the pivot table, using the Size and Colour fields.

slicerstableaj04

Then, copy or move those Slicers to the worksheet where the Excel Table is located.

Check for the ID

A named range – DD.Filter – is created, based on column A on the pivot table worksheet. In the Excel Table, a new column is added – xFilter – and a formula in that column checks for the row’s ID in the DD.Filter range.

The formula result is TRUE or FALSE, and only the TRUE rows will show after a Slicer is clicked.

slicerstableaj05

Add Some Event Code

The final step is to add some event code to the pivot table, so it filters the table after a pivot table update. The pivot table update event is fired by the user action of changing a slicer selection.

Here is the code from the SalesPivot worksheet module.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim tbl As ListObject
    Dim lCol As Long
    Application.ScreenUpdating = False
    Set tbl = Worksheets("SalesData").ListObjects("Table1")
    lCol = tbl.ListColumns("xFilter").Index
    With tbl
        If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        .Range.AutoFilter Field:=lCol, Criteria1:="TRUE"
    End With
    Application.ScreenUpdating = True
    Set tbl = Nothing
End Sub

Download the Sample File

To download the AlexJ’s sample file, you can visit his page on the Contextures website. In the Filters section, look for

FL0002 – Filter Excel 2010 Table With Slicers

The file is designed for Excel 2010 only, and you’ll have to enable macros to test the file.

_____________

Find Unmatched Amounts With Advanced Filter

You know that it’s time to update a web page, when all of the dates in the examples are 2002. Winking smile
Yes, that’s what I found when I took a peek at my Advanced Filter – Complex Criteria page. Yikes! Fortunately, the Advanced Filter steps haven’t changed since then, but I added some new screen shots, and updated the sample file.

Find Unmatched Amounts

I’ve also created a video to show the steps for one of the examples – finding unmatched amounts in two fields of a record.
With a simple formula in the criteria range, you can filter all the records where the invoice amount does not equal the amount paid. It’s time to find those customers who have had outstanding amounts since 2002!

Watch the Video

In this video you’ll see how to set up a small criteria range, and enter a formula to check for unmatched amounts. Then, run the Advanced Filter to see the results.

______________

Interactive Excel Sample Data

There’s sample data on my Contextures website, and it’s useful for testing formulas and pivot tables in Excel.
sampledatainteractive01
On that page, there’s a link to a downloadable Excel file, with the same sample data. Or, you could just copy and paste the data into a workbook.

Now With Interaction

Now, thanks to Microsoft’s Excel Services, you can play with the data in Excel Interactive View. Just click the button above the sample data table, and the interactive view will open up.
Note: I’ve added the same feature to my Excel Book List page.
sampledatainteractive02
At the left, the Filters tab is selected, and you can click on any field name, to see its items. Then, click on an item to filter the table.
sampledatainteractive03

Sort the Data

In the data table, you can click any of the headings, to sort that column.
sampledatainteractive04
To sort it in the opposite order, click the heading again.
sampledatainteractive05

Create a Chart

You can also create a quick chart from the data. In the View panel at the right, click one of the chart views. On my Sample Data page, there is a chart view for each of the Value fields – Units, Cost and Total.
sampledatainteractive06
Here is the chart for Total values.
sampledatainteractive07

Table View With Data Bars

To return to Table view, click Table in the View panel.
sampledatainteractive08
The value fields in the table also show Data Bars, like the ones you can add in Excel, with Conditional Formatting.
sampledatainteractive09

Save and Print the Data

In the interactive view, you can also save and print the data. To print it, click the Options tab (gear icon), and click Print.
sampledatainteractive10
To download the data, click the Do More tab (X icon), and click Download.
sampledatainteractive11

Open in Excel Web App

To work with the data online, click on the Do More tab, then click Open in Excel Web App. In there, you can add formulas, and change the data, and use the other features available.
sampledatainteractive12

Add an Excel Interactive View on Your Site

To add an Excel interactive view to a table on your site, go to the Excel Mashup website. Click the Learn How link, to go to the page with instructions.
It’s easy to do:

  1. Select a button style
  2. Enter a title
  3. Add your website name
  4. Click the Generate Code button.

sampledatainteractive13
Follow the instructions in the two steps, to copy the code, and paste it into your website source code.
sampledatainteractive14
Then click Close, to go back to the web page.

Try the Interactive Excel View

To see how the interactive view works, go to the sample data page on my Contextures website, click the green button to get started.
Thanks Microsoft – this is an awesome new feature!
sampledatainteractive02
___________________

Trouble Counting Excel AutoFilters on Sheet

It seemed simple enough, but counting the AutoFilters on an Excel sheet is a tough job! The answer to "How many worksheet AutoFilters are there?" is "It depends!"

You can read the fascinating (to me!) results below.

The Good Old Days

In the old days, before Excel 2003, you could only have one AutoFilter on a sheet. That’s pretty easy to count – either 1 or 0.

For example, this Excel 2010 sheet has a single list that is not a named table. An AutoFilter was applied to this list, and the AutoFilter arrows have been turned off.

If you point to one of the headings, where the hidden arrow is, the tooltip for that filter appears, so that shows us the AutoFilter is still active.

autofiltercount01

Count the Worksheet AutoFilters

To count the worksheet AutoFilters, I usually use AutoFilterMode to check if one exists. Recently, Pascal (forum name: p45cal) emailed me, to suggest that checking for a worksheet AutoFilter would be more reliable. Thanks, Pascal, for inspiring this test!

This code tests for a worksheet AutoFilter, by using either AutoFilterMode or AutoFilter:

Sub CountSheetAutoFilters()
Dim iARM As Long
Dim iAR As Long
'counts all worksheet autofilters
'even if all arrows are hidden
If ActiveSheet.AutoFilterMode = True Then iARM = 1
    Debug.Print "AutoFilterMode: " & iARM
If Not ActiveSheet.AutoFilter Is Nothing Then iAR = 1
    Debug.Print "AutoFilter: " & iAR
End Sub

When I test that code in the Immediate window, both counting methods show 1 AutoFilter.

autofiltercount02

Named Table on the Worksheet

What happens if there is a named table on the worksheet, and it has its own AutoFilter? I ran the code again, on the worksheet shown below. It has a named table, and it has an AutoFilter applied, with all the arrows hidden.

autofiltercount03 

When I test the code in the Immediate window, both counting methods show zero AutoFilters. I consider that count correct, because there is a ListObject with an AutoFilter, but no worksheet AutoFilter.

autofiltercount04 

In the screen shot above, you can see that cell A1 is selected – outside of the named table. When I selected cell B1, inside the table, and ran the code, the results were different. AutoFilterMode was still zero, but AutoFilter detected one.

Apparently, Excel is counting the active cell’s table as a worksheet AutoFilter, with the AutoFilter counting method. I’d rather go with the AutoFilterMode’s zero, and count the ListObject AutoFilters separately.

autofiltercount05

Test With Visible AutoFilter Arrows

Maybe the hidden arrows are affecting the results. To check, I ran code to show all the list AutoFilter arrows, and tested again.

The results were the same as in the previous tests, so visible arrows don’t make a difference.

Multiple Tables on Worksheet

For some final tests, I created a sheet with 3 lists:

  1. Named Table – no AutoFilter – no arrows
  2. Named Table – AutoFilter – visible arrows
  3. Worksheet table – AutoFilter – visible arrows

With a cell in Named Table 1 selected, AutoFilterMode counted one, and AutoFilter counted zero. As in the previous test, the AutoFilter counting method is based on the active cell’s table AutoFilter. It doesn’t detect the AutoFilter in Worksheet Table 3.

autofiltercount06

With any other cell in the worksheet selected, the results were different – both AutoFilter and AutoFilterMode counted one – the correct count of worksheet AutoFilters.

autofiltercount07

Counting Worksheet AutoFilters Conclusion

Because ActiveSheet.AutoFilter detects the AutoFilter in the active cell, it could cause a miscount of worksheet AutoFilters.

I’ll stick to the AutoFilterMode for a count of worksheet AutoFilters, and use other code to count the ListObject AutoFilters.

AutoFilters in Other Excel Versions

After running these tests in Excel 2010, I tested the AutoFilter counting code in Excel 2003, and got the same results.

If you find different results in other versions of Excel, please let me know.

___________

Filter Excel Data Onto Multiple Sheets

There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. You can click a button, and a sheet is created for each sales rep, with that person’s orders.

filtersheetrep05

There is another version of the file, and it creates a sheet for each sales rep name that is visible, after an AutoFilter has been applied.

filtersheetrep01

How It Works

When you click the worksheet button, a macro runs. It sets up a list of sales rep names, then loops through that list, creating a worksheet for each name.

To get the orders onto each sheet, the macro uses an Advanced Filter, setting up a criteria range in cells L1:L2 on the worksheet.

In the screen shot below, the first name from the list, Gill, has been entered into the criteria range.

filtersheetrep03

Advanced Filter Criteria Problems

The filter works well, unless there is another name that begins with the same characters as one of the full names. In the screen shot below, the orders for Giller have been included on the Gill orders sheet.

filtersheetrep02

A Giller sheet was also created, and it has the correct list of orders for Giller.

Why did this happen? The Advanced Filter assumes that we are looking for anything that BEGINS with "Gill", so it includes Giller. It’s as if there is an invisible "*" wildcard character at the end of the Sales Rep name.

To get an exact match only, we can change the criteria cell, so it uses an equal sign before the name. To do this manually, we would enter this formula in cell L2:

="=" & "Gill"

filtersheetrep04

Fix the Problem in the VBA Code

In the original Excel VBA code, the sales rep’s name is entered into cell L2.

.Range("L2").Value = c.Value

filtersheetrep06

I revised the code, so it includes the equal sign and quote marks, just as the manually-entered formula would have.

.Range("L2").Value = "=""="" & " & Chr(34) & c.Value & Chr(34)

filtersheetrep07

Now, when you run the macro, only the exact matches for a sales rep name will appear on the list (upper and lower case will not affect the results).

Download the Sample File

To test the Advance Filter macros, you can download the sample files from the Contextures website.

On the Sample Excel Files page, go to the Filters section. Then, look for these files:

  • FL0013 – Create New Sheets from Filtered List
  • FL0004 – Create New Sheets from List

Advanced Filter Video

To see the steps for a manual Advanced Filter to a different worksheet, you can watch this short Excel video tutorial.

Or watch on YouTube: Automatically Copy Excel Data to Other Sheet

______________

Excel AutoFilter By Typing Criteria

Someone emailed me for help with an Excel AutoFilter last week. He wanted to type the criteria onto a worksheet, and have the filtered results shown automatically. There are some built-in options for filtering by text, and keep reading to see a worksheet version that Roger Govier designed.

AutoFilter Search in Excel 2010

There is a new feature in Excel 2010 that provides easy searching, though not on the worksheet. You can see an example here, for the Excel 2010 AutoFilter search feature.

image

AutoFilter Search in Earlier Versions

In earlier versions of Excel, you can filter for text, but it’s a bit more work. In Excel 2007 you can use a text filter, which opens the Custom AutoFilter dialog box

image

In Excel 2003, use the Custom option on the AutoFilter drop down.

image

Roger Govier’s FastFilter

If you’d like to enter the AutoFilter criteria on the worksheet, instead of a search box or dialog box, you can use Roger Govier’s FastFilter sample Excel file.

He has set up a table on the worksheet, with an empty row above the table. In that row, you can type one or more criteria, and when you press the Enter key, the table is automatically filtered.

For a simple filter, type an exact match for a value, and press Enter. In the screen shot below, the table is showing only the items from category 2.

fastfilter01

You can also use operators, and in the next screen shot I’ve added a ">20" criterion in the Unit Price column.

fastfilter02

Use WildCard Characters

If you’re trying to find a specific string of characters in a column, you can use the * and ? wildcard characters. In the next screen shot, I used *b* in the product name column, to find any products that have a "b" somewhere in the name.

fastfilter03

Use Multiple Criteria in a Column

You can use special characters for OR (^^) and AND (^), to combine multiple criteria in a single heading cell. In the Category ID column, I used the ^^ characters to find category 2 OR 4. In the Unit Price column, the ^ character limits the price to >20 AND <35.

fastfilter04

Remove the Criteria

To clear the filter from a column, just click on the criteria cell, and press the Delete key on your keyboard. If you want to clear all the filters, select all the criteria cells, and press Delete.

Download the Sample File

To download the sample file, you can visit Roger’s Sample Files page on the Contextures website. In the Filters section, look for FL0001 – Fast Filter. There is a download link for the FastFilter zipped file.

The file is in Excel 2003 format, and will work in later versions too. After you open the file, enable macros, so you can test the automatic filter feature.

____________