Formula Errors

How to Fix Duplicate Entries and Automate Weekly Tips in Excel

Khadija Khan Oct 1, 2026 869 views

Question details

The user needs to prevent duplicate bartender names in a daily tips breakdown and correctly distribute morning and evening tips by weekday using automated formulas.

How to Fix Duplicate Entries and Automate Weekly Tips in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing weekly restaurant or bar staff tips, calculating combined hours, and distributing tips accurately by shift.
Observed behavior: The current daily team workbook incorrectly duplicates bartender names in the breakdown, and tips are not automatically categorized by weekday or shift.

Before you start

Ensure your spreadsheet software supports dynamic array formulas, as functions like UNIQUE are required to automatically prevent duplicate entries and spill data across rows.

Solution 1Recommended

Use Dynamic Array Formulas to Prevent Duplicates

Replace legacy cell references with dynamic array formulas to extract unique bartender names and automatically spill the results down the column.

By utilizing dynamic array functions, you can automate the process of filtering out duplicate names. The formula will automatically adapt its size based on the source data, eliminating the need to manually drag formulas down.

Select the starting cell

Open your daily team workbook and select cell A4, where the tips breakdown list is meant to begin.

Apply the UNIQUE formula

Enter the formula =UNIQUE(SourceRange) into cell A4, replacing 'SourceRange' with the specific column range containing your raw, unedited list of bartender names for the day.

Update adjacent calculation formulas

In the adjacent cells (B4:I4), update your calculation formulas to reference the spilled array by appending a hashtag to the cell reference (e.g., A4#). This ensures the calculations for hours and tips automatically expand alongside the distinct names.

Create a reusable template

Once the formulas in cells A4:I4 are correctly spilling data without duplicates, save this sheet. Right-click the sheet tab, select 'Move or Copy', and duplicate it for the remaining days of the week.

Use Dynamic Array Formulas to Prevent Duplicates

Spill Array Automation: Using the # reference (like A4#) guarantees that if more bartenders are added to the source list, all related tip calculations in B4:I4 will automatically generate without manual adjustments.

Solution 2

Consolidate Morning and Evening Tips using SUMIFS

Use the SUMIFS function to accurately pull, categorize, and combine tip data for specific weekdays and shifts (morning or evening).

Automate Your Spreadsheets

Automate Tip Calculations Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic arrays like UNIQUE and complex conditional formulas like SUMIFS. You can build powerful, automated daily team workbooks to calculate weekly tips, combine hours, and prevent duplicate entries effortlessly.

1. Open your tips workbook: Launch WPS Office and open your existing daily team workbook (.xlsx).
2. Apply dynamic arrays: Select the first cell of your roster breakdown and use the =UNIQUE() function to instantly filter out duplicate bartender entries.
3. Automate tip distribution: Use the =SUMIFS() function in adjacent columns to automatically calculate and split morning and evening tips based on the distinct names.
4. Save and share: Save your completed template to reuse daily, ensuring seamless format compatibility with your team's devices.

Fully compatible with Microsoft Excel formulas and .xlsx filesSupports dynamic array functions for automated data spillingEasily processes large datasets for accurate shift calculationsFree and lightweight software for daily business management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my UNIQUE formula return a #SPILL! error?

A #SPILL! error occurs when the cells below or adjacent to your formula are not completely empty. Clear any existing data, legacy formulas, or hidden text in the downward cells so the dynamic array has enough blank space to display the results.

How do I combine hours for related positions into one total?

You can use the SUMIFS function with multiple criteria, or simply add two SUMIFS formulas together (e.g., =SUMIFS(...) + SUMIFS(...)) to seamlessly combine hours or tips for two related roles like Bartender and Barback.

Do dynamic array formulas work in older versions of spreadsheet software?

Formulas that automatically spill data, such as UNIQUE, require modern spreadsheet versions like Microsoft 365, Excel 2021, or updated versions of WPS Office. If using an older version, you must use alternative manual methods to remove duplicates.

Excel & Data Problems