Certainly! Here's the pseudo-original version of your text:

Today I'll share with you a method to keep duplicate entries from showing up in a column within your Excel worksheet. This trick works across Microsoft Excel 365, 2021, 2019, 2016, and earlier versions.

In one of our prior articles, we touched upon a related subject. As such, you might already be familiar with how to automatically flag duplicates once they're inputted.

This piece will guide you through preventing duplicates from cropping up in one or multiple columns in your Excel sheet. This ensures that only unique data resides in the initial column of your table—whether it’s invoice numbers, stock keeping units, or dates, ensuring each appears solely once.

How to Prevent Duplication - 5 Simple Steps

Excel boasts a tool known as Data Validation, which often goes overlooked but is quite powerful. Using this tool helps sidestep errors in your records. In upcoming articles, we’ll explore this feature further. For now, here’s a straightforward example to illustrate its usage.

Let’s assume you’ve got a worksheet titled "Customers," containing columns for Names, Phone numbers, and Emails used for distributing newsletters. Therefore, all email addresses must be distinct. Follow these steps to ensure no messages go out to the same client twice.

1. If needed, locate and eliminate any duplicates present in the table. You can either highlight the duplicates manually by reviewing the values or use the Duplicate Remover add-in to remove them all at once.

2. Highlight the entire column where duplicates are to be avoided. Start by clicking the first cell with data while holding the Shift key, then select the last cell. Alternatively, press Ctrl Shift End. It’s crucial to begin with the first data cell.

   Note: If your data reside in a basic Excel range rather than a complete Excel table, make sure to select all the cells in your column, including blanks, from D2 to D1048576.

   

3. Navigate to the Excel "Data" tab and click on the Data Validation icon to open the dialog box.

4. On the Settings tab, pick "Custom" from the Allow dropdown list and insert =COUNTIF($D:$D,D2)=1 into the Formula box.

   Here, $D:$D represent the addresses of the first and last cells in your column. Notice the dollar signs indicating absolute references. D2 is the address of the first selected cell, which isn’t an absolute reference.

   Through this formula, Excel counts how many times the D2 value occurs in the range D1:D1048576. If it appears just once, all is well. If the same value shows up multiple times, Excel will display an alert message featuring the text you specify under the "Error alert" tab.

   Tip: You can compare your column against another column to spot duplicates. The second column could be located on a separate worksheet or even a different workbook. For instance, you can match the current column with a list of blacklisted emails for clients you no longer wish to engage with. I’ll delve deeper into this Data Validation option in a future post.

5. Move to the "Error alert" tab and fill in the fields Title and Error message. Once you attempt to input a duplicate entry into the column, Excel will display this text. Aim for clarity and accuracy in your description to avoid confusion later. For example: Title: "Duplicate email entry" Message: "You’ve entered an email address that already exists in this column. Only unique emails are permitted."

   

6. Hit OK to close the "Data validation" dialog. From now on, whenever you try pasting an address already present in the column, you’ll receive an error message with your custom text. The rule applies whether you’re entering a new address for a fresh client or replacing an email for an existing one:

   

Exceptions to the "No Duplicates Allowed" Rule

On the fourth step, opt for Warning or Information from the Style menu list. The alert message’s behavior will adjust accordingly:

Warning: The dialog buttons will change to Yes / No / Cancel. Clicking Yes adds the entered value. Choosing No or Cancel returns you to editing the cell. No is the default selection.

Information: The alert message buttons become Ok and Cancel. Clicking Ok (the default) adds the duplicate. Cancel reverts you to editing mode.

Note: Remember, the duplicate entry alert appears solely when you attempt to input a value into a cell. Excel won’t detect existing duplicates when configuring the Data Validation tool. Even if there are over 150 duplicates in your column, this won’t occur. :).

The images remain unchanged and are referenced by their original links.

The above is the detailed content of How to prevent duplicate entries in Excel column, only unique data allowed.. For more information, please follow other related articles on the PHP Chinese website!