Duplicator Example

In this example, we want to create a report that shows the products sold by each sales representative. For each representative, we want to see the product, date, amount and customer. The goal is to create a separate sheet for each sales representative to make it easier to view invoices for a specific country.

To do this, we’ll set up a workbook that includes a data extraction. Then, using the Duplicator, we’ll insert a cell reference and generate all the necessary sheets in just a few clicks.

Create and set up your workbook

1. On the Workbooks tab, right-click and select New Workbook from the context menu.
2. On the first sheet (Sheet1), enter Sales Rep in cell A1 and AO251 in cell B1.
3. Add another sheet called Filter, enter Year in cell A1 and 2018 in cell B1.

Set up the data extraction

1. Select an empty cell where you want to insert the data extraction.
2. On the Analysis tab, select Data Extraction Wizard.
3. From the Data Model dropdown menu, select Invoice Analysis.
4. Name the data extraction ExtractionSalesRep.
5. On the Fields tab, add the following fields (use the search bar if needed):
   • Customer Code
   • Customer Name
   • Product Code
   • Date
   • Document Number
   • Quantity Sold (SUM)
   • Ledger Currency Amount (SUM)
   • Ledger Currency Cost (SUM)
   • Ledger Currency Margin Amt (SUM)
6. In the Filters tab, click Add Filter, then configure the following:
   • Sales Rep 1 Code – Select cell B1 of Sheet1 sheet.
   • Year – Select cell B1 of Filter sheet.
7. Toggle Refresh On Open to ensure the data refreshes each time you open the workbook.
8. Select Create. The output appears on Sheet1. You now have a data extraction filtered by sales representative AO251 for the year 2018.

Duplicate the sales representative

1. Select cell B1, which contains AO251.
2. In the Analysis tab, select Duplicator.
3. From the Data Model dropdown menu, select Invoice Analysis.
4. In the Field dropdown menu, search for Sales Rep Code.
5. Confirm the cell reference to use: Sheet1$B$1.
6. Click Preview Values to view all the available sales representatives. You should see 24 values.
7. Toggle Refresh On Open to refresh the sheets each time you open the workbook.
8. Select Save. You now have one sheet for each sales representative for the year 2018.

Refine duplicated results with filters

1. Select cell B1 again in Sheet1.
2. In the Analysis tab, select Duplicator. Your existing settings are still visible.
3. Click Add Filter, then configure the following:
   • Quantity Sold – 1000.
4. Click Preview Values. You should now see 6 values, meaning only the representatives who sold at least 1,000 products in 2018 will be included.
5. Select Save. Now, only 6 sheets remain—any sheets without data were removed automatically.