Trouble with my excel multiple columns preventing duplicate entries

**bellawang** · 01-24-2018, 02:36 AM

Hi Guys,

This is Bella from China. I come across this forum from google search. I cannot find answers from our domestic searching engine so I searched from Google. My case is I am making an excel with 3 successive columns, column H, I, J, I need the texts in each cell of these three columns to be unique. All the answers I can find is teaching you how to prevent duplicate entries in one column. But I need in 3 successive columns, each entry is unique.

Is there any formula in the data validity setting? Will be much thankful for anyone who can help me with this. I am stuck now. Urgent need, highly appreciated!

**kersplash** · 01-24-2018, 02:42 AM

You can conditionally format across columns so when a duplicate is entered it will highlight the issue.

**shukla.ankur281190** · 01-24-2018, 02:45 AM

Ho Bella, Welcome to excel forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**bellawang** · 01-24-2018, 02:46 AM

COUNTIF(A$1:A1,A1)=1

thank you for your reply but I don't uderstand. Above is the formula to prevent duplicate entries in one column. Do you know the formula of preventing in multiple columns? Great thanks!

**JeteMc** · 01-26-2018, 08:33 PM

It is hard to know what you want.
If you want to identify duplicates anywhere in a three column range you could select the range and apply the rule for highlighting duplicates as modeled on sheet 1 of the attached file.
If you want to identify duplicates on a single row you could use a helper column as modeled on sheet 2.
If you want to restrict the use of duplicates it will take a bit of doing, however I have put an example of how you could make a shrinking list for data validation and apply that to your three columns as modeled on sheet 3.
Test by attempting to apply one of the letters that has already been used into cell I3.
Note: the third option does restrict the possible inputs to whatever is put in "WholeList".
If none of these meets your needs, please follow shukla's directions and upload a file.
Let us know if you have any questions.