You work as an Office Assistant for Peach Tree Inc. You have created a report in a workbook using
Microsoft Excel 2010. The report includes a worksheet that contains sales data of the company. You
want to use complex criteria to filter the list. For testing, you have inserted criteria rows in the sheet.
The fragment of the worksheet is shown below:
The list range is defined as A9:D44. For testing, you want to filter the list for all the records of Mark.
You want to ensure that the filtered list does not provide duplicate rows. You also want to display
the filtered list at a new location at the G9 cell position. You open the Advanced Filter dialog box by
clicking the Advance option in the Sort & Filter group on the Data tab.
Fill the Advanced Filter dialog box options to fulfill your requirements.
Answer: 
Explanation:
According to the question, you have to accomplish the following tasks:
Display all the records of Mark.
No duplicate rows should exist in the filtered list.
The filtered list is inserted and displayed in the new location at the G9 cell position.
In order to accomplish the task, you will have to provide entries as shown in the image given below:Opting for the Copy to another location radio-button will enforce to display the filtered list in the
specified location (in this case G9). You will have to provide A9:D44 in the List range option to specify
the list to be filtered. Provide range C5:C6 for the Criteria range option. The Copy to option specifies
the location where the filtered data has to be displayed. Provide G9 to accomplish the task. As the
question states that no duplicate records should appear, check the Unique records only check-box.
These steps will copy the filtered data at the G9 cell position as shown in the image given below:In Excel, the Advance Filter option is used to filter a range of cells by using complex criteria.
