Www.exceltip.com



Excel Advanced Filter - EBook? 2015 & Self-PublishingALL RIGHTS RESERVED. This book contains material protected under International and Federal Copyright Laws and Treaties. Any unauthorized reprint or use of this material is prohibited. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system without expressing written permission from the author / publisher. The information in this book is provided on as-it-is basis. The authors and publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.Author- Excel and Cover Design-Published by- Self-publishingFirst Published in- April 2015Hello User,Greetings from ExcelForum & Exceltip!In this E-book we are offering you 7 ways of using “Advance Filter”Advance Filter is the most powerful feature of Excel. The advanced filtering feature in Excel allows you to quickly copy unique information from one data list to another. It allows the person to quickly remove duplicates, extract records that meet certain criteria. It works great when we use wildcards, within 2 date criteria.Filtering is a simple, however, amazing & powerful way to analyze data. Advance filter are quite easy to use. Here's how you can use Excel's advanced filtering capabilities.381009525ADVANCE FILTER INDEX:00ADVANCE FILTER INDEX:Using “And”, “Or” and Multiple CriteriaHow to Filter Unique Records?How to Use Wildcards?Extract records after specific date criteriaExtract items between 2 Date’s criteriaHow to extract data with specific text?How to extract data to another worksheet using VBA?Let us start with creating “Advanced Filter”Below shown picture is the example of data that we will use in this exampleClick on any cell in the above mentioned dataClick on Data ribbonFrom Sort & Filter group, click on AdvancedAdvanced Filter dialog box will pop upor use ALT + A + Q, a shortcut keyThe List range box will automatically takes the data range (eliminates the step for user to select whole data)In Criteria range; select the criteria range as I1:N2Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.Click on OK button will give us the filtered data. Refer below snapshot.All the rows which contain Manager as Susan will get extracted.-7620-165101. Using “And”, “Or” and Multiple Criteria’s001. Using “And”, “Or” and Multiple Criteria’sAnd CriteriaFollowing picture contains range I1:N2 wherein we have couple of criteria:State should be ColoradoManager should be SusanAdvance filter will retrieve the data that matches both the conditions; we need to follow the below steps:Use ALT + A + Q to launch Advanced Filter dialog boxThe List range box will contain range A1:F273; the whole data tableIn Criteria range, select the criteria range as I1:N2 Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrievedClick on OK button will give us the filtered data. Refer below snapshotIn this way, we can use ‘AND’ condition to get the data that matches both the conditions.We can have multiple conditions to get the output that matches all the conditions.OR CriteriaFollowing is the picture of ‘OR’ criteriaAdvance filter will look for any one condition:Manager should be Susan & Process should be XYZManager should be Ronald & Process should be PMPIf any condition is met then the data will be extractedUse ALT + A + Q to launch Advanced Filter dialog boxThe full data will be listed in List rangeIn Criteria range, select the criteria range as I1:N3Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.Click on OK button will give us the filtered data. Refer below snapshotIn this way, we can use ‘OR’ condition to get the data that matches any one condition & produces result accordingly.Multiple CriteriaFollowing is the picture of Multiple CriteriaAdvance filter will check for data that matches all the entries in a row; if all the conditions will meet in a row then the data will be retrieved using advanced filter.We need to follow the below steps:Use ALT + A + Q to launch Advanced Filter dialog boxEnter the data range in List range fieldIn Criteria range, select the criteria range as I1:N3Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.Click on OK button will give us the filtered data. Refer below snapshotIn this way, we can use multiple conditions to get the data that matches any one condition & gets us the result.-3048019052. How to Filter Unique Records?002. How to Filter Unique Records?Advance filter can be used to filter unique records or removing duplicate records from the database. To get unique list of Managers, we will copy the unique list in different location other than the original list.Note: The list must contain a heading, or the first item may be duplicated in the results.We need to follow the below steps:Select a cell in the database or use shortcut key ALT + A + QSelect “Copy to another location”In List range, select column EDo not select anything as Criteria rangeIn Copy to field, enter the cell wherein you want to paste the unique recordsThe last step is to tick on the Unique records onlyBy clicking OK button, we will get the following list of all the ManagersIn this way, we can use advance filter to extract unique records.088903. How to Use Wildcards? 003. How to Use Wildcards? We can use Asterisk (*) & Question mark (?) wildcards.The * WildcardFollowing is the snapshot of the example that we are going to useFrom above data, we will use Wildcard Asterisk (*) to represent any number of characters in that positionWe have entered AL* in State columnAdvance filter will look for all the states that start with characters as “AL”Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fields This example will produce the following list of States that contain characters “AL”The ‘?’ WildcardWe will use question mark Wildcard (?). It represents one character within a string or text in that positionWe need to follow the below steps:We have “Boys Clothing ??? Yrs” as Wildcard in Product columnAdvance filter will look for all the text that meets the condition & extract the result.The 3 question marks represent 3 charactersIn this example, there are two Boys Clothing slabs i.e. 0-5 Yrs & 6-8 Yrs.The question mark wild card will pull out both the entriesPress ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fieldsThis example will extract the data that contains list of Products that has Boys clothing. Refer below snapshot for more information: 004. Extract records after specific date criteria004. Extract records after specific date criteriaFollowing is the picture of criteria that will be referred at the time of extracting data.In this example, any records that fall after the criteria date (>3/31/2015) will be retrievedPress ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fieldsThis example will extract the data that falls after the specific date. Refer below snapshotleft95255. Extract items between 2 Date’s criteria005. Extract items between 2 Date’s criteriaFollowing is the picture of criteria that need to be looked for data that occurs between 1/1/2014 & 12/31/2014Press ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fieldsThis example will extract the data that contains between the two criteria dates. Refer below snapshot006. How to extract data with specific text? 006. How to extract data with specific text? We can use advance filter to derive the items that contain specific text. In this example, we will use “=Tablets” in Product column. Advance Filter will find all the rows that contain Tablets in Product column.Following is the picture of specific text criteriaPress ALT + A + Q for launching advance filter dialog box & follow the below ranges in the fieldsIn this example, we will get a list of Product as Tablets. Refer below snapshot for better understanding:-101600209557. How to extract data to another worksheet using VBA?007. How to extract data to another worksheet using VBA?We have Company Report with Employee Name, State, Emp Id, Manager & Process.The above data contains more than 250 rows. Now, we want user to select Name, Manager & Process from dropdown lists.The information should be updated in another sheet with a single clickTo get the output on the above shown multiple criteria, we require VBA code. We need to follow the below mentioned steps to launch VB editorClick on Developer tabFrom Code group, select Visual Basic or simply press ALT +F11 to launch VBA screen Click on Insert, and then on ModuleThis will create a new module.Enter the following code in the Module-31750138430Sub AdvanceFilter()Sheet2.Range("A4:F10000").ClearSheet1.Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, CriteriaRange:=Sheet2.Range("A1:E2"), _CopyToRange:=Sheet2.Range("A7:F7"), Unique:=FalseColumns(4).AutoFitEnd Sub00Sub AdvanceFilter()Sheet2.Range("A4:F10000").ClearSheet1.Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, CriteriaRange:=Sheet2.Range("A1:E2"), _CopyToRange:=Sheet2.Range("A7:F7"), Unique:=FalseColumns(4).AutoFitEnd SubWe can assign the macro to GO button. After clicking the GO button, we will get the following resultThe above VBA code will automatically update the result as we change Employee Name, Manager or Process.In this way, using Advance Filter through VBA, we can extract data on multiple conditions. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download