Excel conditional formatting

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3159276%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EExcel%20conditional%20formatting%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3159276%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20have%20a%20column%20that%20is%20time%20and%20a%20column%20with%20item%20x%26nbsp%3B%20I%20cannot%20have%20two%20of%20the%20same%20item%20in%20a%20two%20hour%20period.%26nbsp%3B%20I%20have%20used%20conditional%20formatting%20to%20highlight%20for%20a%20two%20hour%20block%20from%201-3%2C4-6%2Cetc.%20works%20great%20except%20it%20lets%20me%20put%20a%203%20and%204%20of%20the%20same%20item%20x%20in%20the%20column.%26nbsp%3B%20I%20tried%20adding%20another%20conditional%20formatting%20but%20then%20it%20starts%20highlighting%20everything.%26nbsp%3B%20Is%20there%20any%20other%20way%20to%20format%20this%20to%20highlight%20a%20duplicate%20just%20within%20a%20six%20cell%20block%20of%20itself%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ETime%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BItem%20X%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bg%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bnot%20ok%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20OK%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20c%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20f%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bg%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BNot%20ok%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20I%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BNot%20ok%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E4%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bg%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20OK%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Not%20ok%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E4%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E5%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E5%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3159276%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

I have a column that is time and a column with item x  I cannot have two of the same item in a two hour period.  I have used conditional formatting to highlight for a two hour block from 1-3,4-6,etc. works great except it lets me put a 3 and 4 of the same item x in the column.  I tried adding another conditional formatting but then it starts highlighting everything.  Is there any other way to format this to highlight a duplicate just within a six cell block of itself?

Time                   Item X

1                           g                   not ok                      OK

1                            c                      I                              I

2                            f                      I                               I 

2                                                    I                              I

3                             g                   Not ok                      I                       Not ok

3                                                                                   I                             I

4                             g                                                  OK                      Not ok

4

5

5

12 Replies

@eatonad 

You may use conditional formatting rule with formula like

=COUNTIFS(
 itemX, currentValue,
 time, "<=" & thisTime + 1 hour
 time, ">=" & thisTime - 1 hour
)

@Sergei Baklan 

I am trying to teach myself Excel and have been at it a couple days this is by far the hardest thing I am trying to do....

Will this work if item x is any value at any time?  Essentially If item x is a at 3 and 4 both need to be highlighted but not at 3 and 5 but if item x is b at 3 and 4 both b's need to be highlighted but not at 3 and 5, or if item x is c at 10 and 11 both c's  to be highlighted but not at 1 and 10. Sorry if this is confusing it is for me. I know what I want it to do I am just trying to figure out how to get it to do it.  If I need a condition for each Item X at each time that will not be fun.

@eatonad 

It's better with sample. Could you please illustrate with something like this.

image.png

I cannot screen shot or cut on this computer where I am doing the project as it is blocked for sensitive info. I put that formula in for my values of L is my column c and 2-49 and b is my time and it always highlights any duplicates still. I see that it does not on yours I will keep messing around with it. =COUNTIFS($L$2:$L$49,$L2,$B$2:$B$49,"<="&($B2+1),$B$2:$B$49,">="&($B2-1))>1 is the formula I am using.,
2 to 49 for the time column? the number is not about the "hour"?

Back to your first post, why does time1-g and time3-g is not ok?
is "time1 to time3" a two-hour block?
and does it mean that "time2 to time4" a two-hour block too?
You are correct that was supposed to be up on the second 2 in the 4th row. Each time frame is a 2 hour block 24 hours a day plus a header makes my rows 2-49 that need to highlight the square in a two hour time frame to acknowledge an overlap.
so my excell is more like
Date time C D E F G H I J K L M N O P
0 x
0 z
1
1 y
2
2 x
3
3 x
4
4
All the way down to 23:00 and in this one X needs to highlight if x=0 and x=2 or x=2 and x=3 but not if x =0 and x=3

@eatonad 

We don't need your actual date. You may generate sample Excel with fake data, that's 5-10 minutes job.

@Sergei Baklan 

eatonad_0-1644863625040.png

The formula is highlighting every one that is the same

=COUNTIFS($L$2:$L$49,$L2,$B$2:B$49,"<="&($B2+1),$B$2:$B$49,">="&($B2-1))>1
This is the formula in case you cannot read it

@eatonad 

As a minimum you shall use $B2+1/24 and $B2-1/24

since in column B you use time as the time (1:00 = 1/24 of the day), not as general numbers as in the post (1 hour, 2 hours, etc)

Works perfectly thank you people so much and not just solving it explaining it so I can understand why.