prevent duplicates in excel is easy but

Question

We all know how to prevent duplicates in excel by using data validation and the custom formula : =COUNTIF($A:$A;A1)=1 for example.

but I need to add a time condition, so every day I'm typing some numbers in a row and these numbers must be unique in the that day.

So there must be no duplicates entries in the same day but can be typed tomorrow also one time and so on.

Store the date with the time and format the cell to not show the date perhaps? — Jonno, Mar 02 '16 at 17:00
the time is not an entry it's only a condition the numbers typed are the entries like if I type in A1 : 67 and I want to type it again in A2 I cant do it only today so if tomorrow I want to type 67 in A2 it's possible and so on — Said Khattabi, Mar 02 '16 at 17:05
So you want it to prevent you entering duplicates on a certain day, but allow them again the next..? — Jonno, Mar 02 '16 at 17:09
Okay, where are you typing these? How many are you typing? What are they? — Raystafarian, Mar 02 '16 at 17:16
@Jonno yes you are exactly right but in the next day also no duplicates — Said Khattabi, Mar 02 '16 at 17:44
@Raystafarian I'm typing only numbers, up to 300 per day, using a simple worksheet — Said Khattabi, Mar 02 '16 at 17:46
@Raystafarian so I'm managing a groupe of people 300 exactly, each one has a number when they come to my shop I type their number to check, every one is allowed to come only once in a day so to prevent any fraud I need to prevent duplicates entries in the same day — Said Khattabi, Mar 02 '16 at 17:53
You have to have the date entered onto your spreadsheet somehow. this post explains how you can do that. After that building the conditional formatting to check for duplicates is a simple manner of converting your countif() to countifs() — Kyle, Mar 02 '16 at 18:05
@Kyle I'm using a VBA code that print automatically the date+time I need only the last step :) — Said Khattabi, Mar 02 '16 at 18:13

Raystafarian · Accepted Answer · 2016-03-03T13:01:05.730

0

Should just work with a countifs

=COUNTIFS($B:$B,B1,$A:$A,A1)=1

Assuming dates are in A and numbers are in B.

Setup like this -

Now, let's see what happens with a repeat -

It works! But wait, what about different dates?

SUCCESS!

edited Mar 03 '16 at 13:01

answered Mar 02 '16 at 18:12

Raystafarian

21,743
12
62
90

I'll try it right now – Said Khattabi Mar 02 '16 at 18:19
so when trying it I selected the A and B rows and applied the formula but not working – Said Khattabi Mar 02 '16 at 18:30
Apply it only to the B range – Raystafarian Mar 02 '16 at 19:06
ok I'll try it now – Said Khattabi Mar 02 '16 at 20:42
tried it but not working also, so I'll try using the and + countif functions instead of countifs – Said Khattabi Mar 02 '16 at 21:18
it works only if you insert the date by hand wich is not a good thing to be doing all the day so the time must be used as a condition not entry somehow – Said Khattabi Mar 02 '16 at 23:02
It works perfectly for me. Make sure all other rules are removed. Even using =today() and =today()-1 works. Try it on a blank sheet, or copy - paste values of your list and test it. – Raystafarian Mar 03 '16 at 12:01
I tried it on a blank sheet but not working sorry can you please upload your example so I can see how it works. thank you very much – Said Khattabi Mar 03 '16 at 12:36
@SaidKhattabi see screenshots. That's the best I can do. – Raystafarian Mar 03 '16 at 13:01
thanks a lot it gave me an idea now, but your solution needs the dates to be written before typing the number, my idea is when typing the numbers then the date + hour show up automatically and that I think is impossible. thank you for your help :) – Said Khattabi Mar 03 '16 at 13:37
That would require a macro – Raystafarian Mar 03 '16 at 14:42
I'm already using a macros to print the date and hour, but couldn't find a real solution so I did the following : since I know there are 300 people I selected 300 cells and applied data validation on them for day 1,then selected other 300 cells and also applied data validation and so on it's working for me :), thank you all for the help :) – Said Khattabi Mar 03 '16 at 21:22
If you're already using a worksheet_change macro, you don't need data validation, you could just validate from the macro. – Raystafarian Mar 04 '16 at 08:53

prevent duplicates in excel is easy but

1 Answers1