Can i somehow have excel check for duplicate data when populating a cell?

spaghettyhoop

Board Regular
Joined
Jun 10, 2013
Messages
57
Hello. Been away for a couple of months on other projects, but im back to excel and feel like ive forgotten everything!

I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)

One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.

The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.

Hope that makes sense, and if anyone can help me id be eternally grateful, as im thoroughly stuck!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

spaghettyhoop

Board Regular
Joined
Jun 10, 2013
Messages
57
I cant right now due to my workplaces internet policy, I should be able to later once I have had chance to remove the information and create a blank example.

Although to simplify it, if we ignore the fact it links to other sheets, if I could just get it to automatically turn duplicate cells red, thats all it needs to do upon thinking about it. It sounds so simple in my head, but I cannot figure it out.

Essentially I want to be able to conditional format a cell to go "If already exists on a sheet, highlight red"

I have found a possible sollution using COUNTIF

http://office.microsoft.com/en-gb/e...using-conditional-formatting-HA001136616.aspx

So im going to give that a go and see if I can get it working :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,462
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top