The ExcelHelpDesk Support team received the following request for help:
Problem – Unable to filter information by month and year with multiple columns
I am in need of a way to filter the information in the attached file. I need to be able to filter the entire sheet to show all row’s that have a specified month/year no matter which column the specified month/year is in.
This is used for my monthly report to show all after sales activity for each month. For example, at the end of the month I need to be able to filter all activities for the month of May 2014 to show all emails, phone calls, start-ups, acct notify, one year follow ups so I can convert that to PDF and send to the CEO. Please help!
The most familiar feature for filtering data is the Autofilter as it is very simple and painless to use. However, it is limited and does not really support filtering multiple columns that are required in this case. There are different approaches to accomplish the task. We could use full VBA coding by looping through all the rows and columns or a VBA-less method using built-in conditional functions such as IF and OR. In this post, we have opted to use a combination of VBA and Autofilter.
Step 1 – Prepare fields and additional column
We will insert a new column to the most right of the table. Let’s put Has Month/Year for the header title. Add month and year field at the top of the new column header just like in the screenshot.
The initial value for the month is March, which is indicated as 3, and year is 2014. It is more convenient to define the values in these two cells so we will be able to change them a lot easier later rather than modifying again the values in all of each row.
Step 2 – Create the function
Click the Developer > Visual basic to open the visual basic editor. If you can’t see the Developer tab, make sure it is checked in File > Options > Customize Ribbon. Now we have the editor opened, let’s insert a new module by clicking the icon shown below then selecting Module.
After the module is created, enter the following code:
Public Function hasMonthYear(intMonth As Integer, intYear As Integer) As Boolean 'declare the variables Dim strValue As String Dim i As Integer Dim intCurrentRow As Integer Dim intCurrentCol As Integer intCurrentRow = Application.Caller.Row 'the row number where the function is called intCurrentCol = Application.Caller.Column 'the column number where the function is called 'this loops from the first column up to the column before where the function is entered. For i = 1 To intCurrentCol - 1 'storing it to variable is faster since we will be using this value more than once strValue = Cells(intCurrentRow, i).Value 'check first if the value is a date so we won't be wasting time and also to avoid unnecessary error If IsDate(strValue) Then 'if it matches the month and year, immediately exit the loop and return true If Month(DateValue(strValue)) = intMonth And Year(DateValue(strValue)) = intYear Then hasMonthYear = True Exit For End If End If Next End Function
Step 3 – Insert the hasMonthYear function
Go to the first cell under the Has Month/Year column and enter the function we created.
T5 stands for the cell value of the month and T6 is for the year but if we try to drag the cell to copy the formula to other cells, Excel will automatically increment the cell values and will result to =hasMonthYear(T6,T7), =hasMonthYear(T7, T8), and so on. To avoid this, we must tell Excel that we do not want to adjust the cell references by putting a dollar sign next to the cell number. So let’s modify the previous function parameters we entered and change it to:
Now, drag it and copy the formula to other cells until the end of the table. The function returns TRUE if it found the month and year in the dates in all columns at least once. Otherwise, it returns FALSE.
Step 4- Filter the data
The final step is to filter the Has Month/Year column. Select the column header and click Home tab > Sort & Filter > Filter. Click the down arrow of the Has Month/Year column then ensure that TRUE is the only one checked in the filter list to display all the rows that match your criteria. You can try changing the values of month and year, or modifying the VBA code to further meet your needs.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.