If you are a wholesaler or distributor, you will often form more price lists for different groups of partners, which can be retail, wholesale, hotels and restaurants… Wouldn’t it be a good idea to keep all your product and price information in one table and print it, by desired criteria, when needed for each specific customer? Let’s see the practical application of the Advanced Filter option…
First, it is necessary to have a general price list in one worksheet. It should be a traditional Excel spreadsheet with detailed headers, you can even format it according to your own preferences, but it is not allowed to create a data table (structured table) or add filters – this can disrupt application of Advanced Filter option, which we will use to generate price lists. In another part of the spreadsheet you should copy the header of the general price list table. Below it we will enter the filter criteria. For more convenience in this example criteria table is placed above the general price list.
How do we generate a price list only with items from a particular manufacturer, for the brand you choose, or based on some other criteria? For example, let’s see how to get price data for all beers. In the table above (criteria), under the heading SALES GROUP, it is necessary to enter the word BEER. Then we need to create a new worksheet. Further go to Data ribbon and launch the Advanced Filter option. After the dialog of the same name opens, you first need to select the Copy to another location option. In the List Range field should enter the range of data in which the general price list is located, Criteria Range should contain the range with the criterion („SALES GROUP“ and cell below, in which we entered the word “BEER”) and in the Copy to field it is necessary to specify the address of the destination cell. After clicking OK, a new table will appear on a given destination containing exclusively data for SALES GROUP named BEER. Now all you have to do is prepare a worksheet for printing by adding a header, a date, setting the report width to one page etc.
What if we want to generate a price list for the BEER sales group, but we only need to see several columns, for example: ID, ITEM NAME and PRICE RT? Procedure is similar to the previous one, we should create a new worksheet and there copy headings of columns we want to show in a report. Then, let’s run Advanced Sheet option. Again, choose Copy to another location. The Range sheet and the Criteria Range fields contain the same ranges as in the previous example, and in the Copy to field should enter the range of cells within a new worksheet where the column titles are located. After confirming the action, we will get a filtered table in accordance to the given criteria.