highlight duplicate cells on the same row-conditionally?

[John Caines]

Hello All.

I have a spreadsheet with a range of cells that contain data (nearly always text).
Range is: J5:O2004

Is there anyway I can use a formula in conditional formatting to highlight any duplicate cells ONLY on the same row?

As you can see, there are only 6 cells across... but I might enter the same text by mistake in 1 cell on the same row & so have a duplicate then on that row.
So,, I really need to highlight the duplicate on that same row.

If anybody can advise please.
I did google & find a vb solution on excelforum here;
Highlight cells if duplicate found on the same row
but, I think this can be achieved without VB????
I did try the duplicate highlight in the conditional formatting in excel (2010),, but it highlighted every duplicate in the whole range!

So,, I think it needs a formula to do this?
If anybody can advise me here it would be a great help.

Many thanks
JC

 

[John Caines]

I'm completely stumped!!!!
I found a website that I thought explained it,,, I just tried the following formula:

=COUNTIFS($J$5:$J$2004, $J5, $K$5:$K$2004, $K5,$L$5:$L$2004, $L5,$M$5:$M$2004, $M5,$N$5:$N$2004, $N5,$O$5:$O$2004, $O5)>1

that just hightlights duplicate ROWS!!

I'm just looking to highlight duplicate cells within each row (that only is 6 cells across)

Help!!!
Driving me crazy again, hehehe,
I need a coffee I think
JC

 

[chuggins143]

I'm assuming you want to do this just as a self check as you're typing in data... If you're wanting it to do it with Conditional Formatting, then that's pretty simple... you'll have 6 rules... does the first cell = any of the other 5 cells, if so highlight... does the second cell = any of the other 5 cells, if so highlight, and does the third cell = any of the other 5 cells, if so highlight.... and so forth.

Edit.... you will have to do all of the cells, my inital response I was typing faster than I was thinking!

 

[FormR]

Try this formula:

=COUNTIF($J5:$O5,J5)>1

 

[John Caines]

Thanks for your reply chuggins143,,,
Yes,, I kind of understand it,,,,

but is there a COUNTIF formula,, or MATCH formula or some way to do this in conditional formatting just with a formula?

The range is; J5:O2004
So,, it's only 6 cells wide.
I just want the duplicates for each row highlighted (within the range,,& not including blanks)

I've been googling the last 30minutes,, I've typed things like;
"find duplicate cells within a row & range excel"

I just can't find anything?
You have any idea how this could be done in a formula Chuggins143?

Many thanks for your reply.
Again,, I kind of get it,, but ,, well, I not 100% sure how to.

I think 1 formula is the best way,, if I could find it!
All the best
JC

 

[John Caines]

FormR!!!!!!!!!!!!!!!!!!!!!
That's it!!!!!!!
Brilliant,,,,

I don't believe this,,, I did try something so so close to this,,, but I somehow got it ever so slightly wrong
& in excel ,,, slightly wrong isn't any good

Many thanks for this..
It's 100% what I needed.

I'm very grateful.

All the best
A very happy
JC!!!!
(I still can't believe what I tried was so so close,, dame ,, hay ho)
###
I think I tried;

=COUNTIF($J5:$O2004,J5)>1

 

[FormR]

No problem, happy I was able to help.

 

[chuggins143]

What's going to happen when you split the range on the COUNTIF? Doesn't want to do that on my machine... Seems like that would work fine on the first and last columns, but not inbetween. Correct me if I'm wrong here guys.

Edit... now I did get the middle columns to work with something like this...
=IF(OR(CM50=CL50, CM50=CN50, CM50=CO50, CM50=CP50), 1,0)>0 Where CM50 is my cell of interest that I'm checking against all the other cells in that row.