Excel: 21 Removing Duplicates and Filtering

Using the Advanced Filter Command

Advanced Filter can be used in 8 different ways. There are three options in the Advanced Filter Dialog, and depending on your choices for those three options, you can have possible combinations. 1. You can choose to either Filter in Place or Copy to a New Location 2. You can choose to filter with a criteria range or without any criteria 3. You can choose to return all matching values or only the unique values. If you choose to copy records to a new location, you can either copy all the input columns in order or specify a subset of columns and/or a new sequence of columns. You can build a simple filter for one column. You can combine any number of filters for multiple columns. You can build incredibly complex filters, using any formula imaginable. Alternatively, you can not use no criteria at all. Using no criteria is common when you using Advanced Filter to extract unique values or when you want to use Advanced Filter to reorder the sequence of columns. Follow the steps to use the Advanced Filter on a data set: 1. If you are using criteria, copy one or more headings from your data set to a blank section of the worksheet. Under each heading, list the value(s) that you want to be included 2. If you are using an output range and want to reorder the columns or include a subset of the columns, copy the headings into the appropriate order in a blank section of the worksheet. If you want all the original columns in their original sequence, the output range can be any blank cell. 3. Select a cell in your data range 4. Select Data, Sort and Filter, Advanced. 5. Verify that the list range contains your original data set 6. If you are using criteria, enter the criteria range. 7. If you want to copy the matching records to a new location, select Copy to Another Location. This enables the reference box for Copy to. Fill in the output range. 8. If you want the output range to contain only unique values, click Unique Records Only. If your output range contained a single field, a list of the values in that field is displayed that match the criteria. If your output range contains two or more fields, every unique combination of those two or more fields is displayed. 9. Click OK to preform the filter You can build advanced filter criteria that can ask for a range of values. It is unlikely you will want to filter to the customer with exactly 7,553 in sales. However, you might want to filter to invoices that are over $5,000 in sales. To set up this criteria, type Sales into Cell K1. In Cell K2, type the text > 5000. When you issue the Advanced Filter, Excel returns all invoices in excess of $5,000. Use the Advanced Filter dialog, and enter the Copy to another location with the Copy to cell named ranges.

Using Remove Duplicates to Find Unique Values

As you analyze transactional data, you often want to find the number of something in the data set. Advanced Filter, IF function, Pivot Table Remove Duplicates tool You can create an extra column for the customer id for duplicated transactions. Follow these Steps: • Copy the data set to a blank section of the worksheet • Leave a blank column in between real and copy data • Select a single cell within the data set • Data tab, in the Data Tools group, select Remove Duplicates. Excel expands the selection to include the entire range. In the Remove Duplicates dialog, Excel predicts if your data has headers. This dialog also shows a list of all the fields in the data set. • Because you're interested in a unique list of customers, click the Unselect All button to clear all check boxes and then select the customer field (one column) as shown. A. Removing Duplicates Based on Several Columns Select Home, Conditional Formatting, Highlight Cell Rules, Duplicate Values Send a copy of the unique values to a new location by using the Advanced Filter Remove duplicates, add the sales for all the removed records and then add them to the customer field. This can be used with the Pivot Tables by using the Consolidate feature. To analyze unique combination of two fields when looking for duplicates: Copy the data then paste it to a blank section of the worksheet Leave a blank column between your data and the copy of the data Select a single cell within the data set On the Data tab, Data Tools group, select Remove Duplicates In the Remove Duplicates dialog, leave the check boxes for both of the fields selected Click OK to remove the duplicate values In this case, the result is a list of all unique combinations of market and customer B. Handling Duplicates Other Ways The Remove Duplicates command is also available in Table Tools, Design tab. For example, you can use this option to remove duplicates from a table that you have defined as a range. C. Combining Duplicates and Adding Values You can use a pivot table to find the total sales for each customer. Alternatively, you can use the data tools to consolidate the table down to one record per customer, start a blank section of the workbook before invoking the feature. Use these tools when you have a customer that appears in the column values (sales, costs, profit values) more than once. To use the Consolidate feature: 1. Move the cell pointer to a blank section of the worksheet 2. Select Data, Data Tools, Consolidate, then the Consolidate dialog box appears 3. In the dialog, enter the reference to your data in the Reference box. The data will be combined based on the field in the left column of the range. If you had multiple lists of customers, you could click the Add button and enter additional ranges. 4. Make sure to select the Top Row and Left Column check boxes in the Use Labels in section 5. Click OK Excel creates a new table. Each customer appears in the table just once. The sales associated with all the records of the customer appear in the new total.

Filtering Records

Filter works with any range of data with headings, ranges that have been defined as well as regular ranges. You can also filter text columns that contain a value (conceptual values , ect.) rather starting and ending with a value. Filter by colors, text columns, number columns, date values, by selection. The columns must have special text, number or date formatting uniformly to one column. Excel offers only the special filtering type for the value type that occurs more frequently in the column that may have different types. A. Using a Filter You need to select one cell in your data range before clicking the filet. Your data cannot have any blanks columns nor rows before filtering. The filter drop down now features a multi-select list as well as special filters. B. Selecting One or Multiple Items from the Filter Drop-Down Select the Filter Drop-Down, then click Text Filters for options for filtering, you can also select the values in the Filter Drop Down. ☺ C. Identifying Columns with Filters The columns will have filters D. Combining Filters Two filters can be applied to the same column. E. Clearing Filters Clear Filter from Column - this is from the filter drop down and this leaves the filter on in other columns. Choose a different filter - this is located in the filter drop down From the ribbon - clear the column from the filer, but leaves the filter feature to later select the values. F. Refreshing Filters When you add new rows or edit data or formulas/ functions in other workbooks, filters do not automatically filter. Reapply a filter by right a cell and then select Filter, Reapply. G. Resizing the Filter Drop-Down To resize the filter drop-down, hover the mouse to the bottom right corner where you see the three dots and expand. H. Filtering by Selection-Hard Way MS Access has offered a Filter by Selection icon in the right click menu (filter by value, color, font color, or icon) I. Filtering by Selection-Easy Way Add the AutoFilter icon to the Quick Access Toolbar by clicking on the File, Options, Quick Access Toolbar, then scroll down to All Commands, then add the Customize Quick Access Toolbar, Add AutoFilter. This is Filter by Selection. J. Filtering by Color or Icon Works for the cell color, font color or the icon in the cell. K. Handling Date Filters Groups the dates into hierarchical groups. You can turn off the grouping in the File, Options, Advanced category, scroll down to the section for Display for This Workbook. Next, select a workbook and then clear the check box for Group Dates in the AutoFilter Menu. Click the plus sign to expand the grouped dates. L. Using Special Filters for Dates, Text, and Numbers Determine if the column contains mostly text, dates, or numeric values. Text: Begins With, Ends With, Contains, Does Not Contain, Equals, and Does Not Equal. You can use an asterisk (*) for any number of characters or a question mark (?) to represent a single character. Numeric Values: Top 10, Above Average, Below Average, Between, Less Than, Greater Than, Does Not Equal, and Equals., top 10 percent of items. Change the number 10 to any number. Dates: Before, After, or Between a particular day, week, month, quarter or year. The special filters also include Year to Date or All Dates in a particular period, as shown in Figure 21.11. Date Filters, All Dates in the Period, Quarter 3 The Custom AutoFilter dialog, allows you combine two conditions by using an AND or OR clause.

Removing Duplicates and Filtering

Remove duplicates with filtering

Sorting Filtered Results

The Filter drop down provides choices to sort a column. After applying the Top 10 filter to Column F, Excel shows the top 10 values, but they are unsorted. Excel does not filter the hidden rows. A. Totaling Filtered Results SUBTOTAL function sums the visible rows from the data set that has been filtered. You can use this function to find the count, average, minimum, and maximum as well as other calculations on the visible rows. Cauton on the COUNTIF function with the SUBTOTAL, use another column to copy the visible rows for an accurate result. B. Formatting and Copying Filtered Results Rows that are hidden using Hide Rows will often be included when you copy or format a range that contains those rows. You must use Alt + to narrow your selection to only the visible rows. To highlight all rows matching criteria by changing the background color of the cell: Select one cell in the unfiltered data set that matches the proper criteria Click the Filter by Selection icon in the Quick Access Toolbar Select the first visible cell below the headings Press Ctrl + Shift + Down Arrow and then Ctrl + Shift + Right Arrow to select all the cells below the heading Format the cells as desired Select Data, Filter to remove the filter and show all rows. You will find that only the rows that were visible during the filter have the new formatting.