I am trying to create a database that shows when someone has been seen more than one time in 7 days.
Column a is a unique identifer (10 numbers) and column b is the date (01/01/2012 format)
I want to highlight a "yes" (in column c) if the duplicate has occured within the last 7 days )based on the entry of A and B). I also don't want to use a macro if possible.
Any help would be very welcome.
You can use conditional formatting for that. Post a data sample in an Excel file, so we don't stab in the dark.
Like a post? Click the star below it!
hI
Do you mean(in C1 and copy down)?
=IF(AND(COUNTIF($A$1:$A2,A1)>1,B1<TODAY()+7),"YES","")
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
A b c
12345 12/03/2012
12346 13/03/2012
12347 14/03/2012
12345 16/03/2012 Yes
12345 25/03/2012
Hi - Hope the example is sufficient. Column A is the numerical identifier. Column B is the date they attended. I want column c to show red if the entry in column a has repeated within the last 7 days. As the entry in the 5th column is not within 7 days of a previous entry is has not been highlighted.
Last edited by kurgon; 07-02-2012 at 08:36 AM.
..And
D e f
67890
HiOriginally Posted by Fotis1991
This works really well to highlight all cells where this value is true. Is there a way to only highlight the last example?
The example from my excel sheet showing the correct columns etc is:
=IF(AND(COUNTIF($A$3:$A600,A2)>1,B2<TODAY()+7),"YES","")
..or, at least that is what I changed it to.
Many thanks again.
Hi
Sorry, I've just worked out how to attach the file. Column A is the unique number, column b the date and column N is where I want to hgihlight the 2nd example if the rules have been met (i.e. 2nd presentation with 7 days)
In N2 and copy down
=IF(AND(COUNTIF($A1:$A2,A2)>1,B2<TODAY()+7),"YES","")
This seems to work some of the time, but not all of the time. In the attached example it has worker for 2 occasions but missed on 4. I am also getting the dtaa error warning sign. Any ideas?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks