Pivot Table Issues

How to Populate Supplier Worksheets from Daily Data in Excel

WPS Content Manager Sep 30, 2026 868 views

Question details

The user needs a method to organize daily transport logs and extract or populate data for specific suppliers using Microsoft Office 2019.

How to Populate Supplier Worksheets from Daily Data in Excel

Product: Microsoft Excel (Office 2019)
Device & OS: not provided
Scenario: Managing daily transport logs and separating the data to view individual supplier records.
Observed behavior: The user is attempting to maintain multiple manually synchronized worksheets for each supplier because dynamic array functions are not available in Office 2019, leading to an inefficient workflow.

Before you start

Before you begin, ensure all your daily data sheets use identical column headers (such as Date, Transport, Time, Length of Stay, and Supplier) so they can be seamlessly combined into one master table.

Solution 1Recommended

Consolidate Data and Use a Pivot Table

The most reliable approach in Office 2019 is to combine all daily records into a single master table and use a Pivot Table to filter and display records by supplier.

Maintaining separate worksheets for every supplier is highly inefficient and prone to errors. By keeping all your data in one master sheet, you ensure data integrity and avoid manual synchronization.

Since dynamic array functions like FILTER are not available in Excel 2019, a Pivot Table is the most effective tool to dynamically group and view your supplier data.

Create a Master Worksheet

Create a new worksheet named 'Master Data'. Copy and paste all the daily transport and supplier records from your individual daily sheets into this single table.

Insert a Pivot Table

Highlight your combined data range in the Master Data sheet. Go to the 'Insert' tab on the ribbon and click 'PivotTable'. Choose to place it on a New Worksheet.

Configure Pivot Table Fields

In the PivotTable Fields pane, drag the 'Supplier' field into the 'Filters' area (or 'Rows' area). Drag 'Date', 'Transport', and 'Time' into the 'Rows' area to display the specific logs.

Filter by Supplier

Use the drop-down filter at the top of the Pivot Table to select a specific supplier. The table will instantly update to show only the relevant transport logs for that supplier.

Pro Tip: Format your Master Data range as an Excel Table (Ctrl + T) before inserting the Pivot Table. When you paste new daily records at the bottom, simply right-click the Pivot Table and select 'Refresh' to update your supplier views automatically.

Solution 2

Use Data Filtering on a Master Sheet

If you prefer not to use Pivot Tables, applying standard data filters to a consolidated master sheet is a simple alternative for quick viewing.

Organize Data Easily with WPS Office

Use WPS Spreadsheet to Consolidate Daily Records

WPS Spreadsheet provides powerful data management tools, including advanced Pivot Tables and seamless filtering capabilities, making it easy to organize daily transport and supplier logs in one centralized place without complicated formulas.

1. Open Your Data File: Launch WPS Spreadsheet and open your workbook containing the daily transport data.
2. Consolidate to a Master Sheet: Copy all your daily records into a single worksheet. Ensure columns like Date, Supplier, and Transport are clearly labeled.
3. Insert a Pivot Table: Select your entire data range, navigate to the 'Insert' tab, and click 'PivotTable'.
4. Filter by Supplier: Drag the 'Supplier' field into the Report Filter area and your log details into the Rows area to instantly generate a clean, supplier-specific report.

Fully compatible with Microsoft Excel (.xlsx) formatsRobust Pivot Table features for easy data consolidationFree and lightweight Office alternativeFamiliar user interface with no steep learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use the FILTER function to populate supplier sheets in Office 2019?

The dynamic array FILTER function was introduced in Excel for Microsoft 365 and Excel 2021. Because Office 2019 does not support dynamic arrays, you must use alternative methods like Pivot Tables, Advanced Filter, or complex INDEX/MATCH arrays to achieve similar results.

Is it bad practice to maintain separate worksheets for each supplier?

Yes, manually updating dozens of individual supplier worksheets is highly error-prone and time-consuming. Consolidating data into a single master table and using filters or Pivot Tables is standard best practice for database management in Excel.

How can I automatically update my Pivot Table when new daily data is added?

To make updates seamless, highlight your master data and press Ctrl+T to format it as a Table. When you append new daily rows to the bottom of the Table, they are automatically included in the source range. You simply need to right-click your Pivot Table and choose 'Refresh'.

Excel & Data Problems