How to Prevent Duplicate Entries with Excel Data Validation
Question details
The user needs to set up a data validation rule that prevents duplicate values from being entered into column A if the same value already exists in another row and is marked as 'Occupied' in column B.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a data entry form or tracking sheet where conditional duplicates must be strictly blocked to avoid double-booking or redundant entries.
- Observed behavior
- The goal state is to reject duplicate entries in column A dynamically when the adjacent status in column B indicates 'Occupied'.
Ensure you clearly identify the data range for validation (e.g., A2:A50) and verify that your status column uses consistent spelling for conditions like 'Occupied'.
Use Custom Formula in Data Validation
Apply a custom formula combining SUM and ROW functions to evaluate matching values and conditions across multiple rows.
This method uses an array-style formula inside the Data Validation tool to check if the entered value matches any other row in column A that also has an 'Occupied' status in column B.
Highlight the cells where you want to apply the rule (e.g., A2:A50). Make sure the top cell (A2) is the active cell in your selection.
Navigate to the Data tab on the ribbon and click on Data Validation.
In the Settings tab, click the Allow drop-down menu and select Custom.
In the Formula box, enter =SUM(($A$2:$A$50=A2)*($B$2:$B$50="Occupied")*(ROW($A$2:$A$50)<>ROW(A2)))=0. Then click OK to apply.
Set Up Data Validation Rules in WPS Spreadsheet
WPS Spreadsheet fully supports advanced Data Validation and array formulas, allowing you to easily prevent duplicate entries. With its familiar interface, you can manage complex datasets and maintain data integrity without any hassle.
- 1. Open your Spreadsheet: Launch WPS Spreadsheet and select the column range you want to restrict.
- 2. Access Data Validation: Go to the Data tab and select Validation from the toolbar.
- 3. Apply Custom Rule: Choose Custom from the Allow drop-down list and paste your conditional validation formula.
- 4. Customize and Save: Set an Error Alert message explaining the duplicate rejection and click OK to enforce the new rule.
Frequently Asked Questions
Why does my data validation formula allow duplicates even after setting it up?
Make sure you used absolute references (like $A$2:$A$50) for the range, but relative references (like A2) for the active cell. If references are misaligned, the formula will check the wrong cells when validating.
Can I use COUNTIFS instead of SUM for conditional duplicates?
Yes. A formula like =COUNTIFS($A$2:$A$50, A2, $B$2:$B$50, "Occupied")<=1 can also work depending on your data structure, but the SUM method paired with ROW ensures it strictly excludes evaluating the current cell against itself.
How can I remove the data validation rule from my cells?
Select the cells that have the rule applied, go to the Data tab, click Data Validation, and press the 'Clear All' button in the bottom left corner of the dialog box.
Does this advanced data validation formula work in WPS Spreadsheet?
Yes, WPS Spreadsheet has robust support for array-based logical formulas within its Data Validation tool, executing them exactly as Microsoft Excel does.
