Highlight duplicate numbers in rows and coloumns from a different range.

[dazd1970]

Hi,
I shall try to explain this properly.

I have numbers in the range D8 thru I13. (1)
I have numbers in the range D1 thru I5. (2)

Now, what i want to do is have any numbers in range 1 that are also in range 2 highlight themselves. But the highlighting is only to be in range 1, and also, if there are any blank cells the highlighting should not happen. The duplicates that need to be counted are only entered numbers and not blanks.

I have tried to attach an example of the spreadsheet. Hopefully that will help.

Many thanks, in hope.

[Break_Point]

I have used the duplicate conditional formatting provided in excel 2013, however this conditionally formats both tables. AS you only want the 1st range to be highlighted I put a second conditional format on the second range to always show it as having no format. See attached.

[Pete_UK]

Highlight all the cells in Range1, starting from D8. Click on Conditional Formatting | New Rule | Use a formula ...

Put this formula in the dialogue box:

=AND(D8<>"",COUNTIF($D$1:$I$5,D8)>0)

then click on the Format button | Fill tab and choose your colour. Then OK your way out, and the duplicates will be highlighted in your chosen colour.

Hope this helps.

Pete

[Hurricanefly]

I know this isn't what tou quite want but if your ranges could be in columns there is a formula to "pull" the duplicate numbers. Assuming using column A as range 2 and column B as range 2 then use formula

Please Login or Register  to view this content.

That's my help but maybe not very, sorry

[Hurricanefly]

Well done Peteuk that didnt cross my mind, go with Petes formula

[dazd1970]

I have used the duplicate conditional formatting provided in excel 2013, however this conditionally formats both tables. AS you only want the 1st range to be highlighted I put a second conditional format on the second range to always show it as having no format. See attached.

Thanks for this. The initial formatting was the wrong way round (highlighting range 2 instead of range 1) but i sorted that. The only other problem is that duplicate numbers in the same range are highlighting each other. If for example range 1 has the number 49 listed twice it should only highlight if it is also located in range 2, not because it is duplicated in range 1.

[dazd1970]

Highlight all the cells in Range1, starting from D8. Click on Conditional Formatting | New Rule | Use a formula ...

Put this formula in the dialogue box:

=AND(D8<>"",COUNTIF($D$1:$I$5,D8)>0)

then click on the Format button | Fill tab and choose your colour. Then OK your way out, and the duplicates will be highlighted in your chosen colour.

Hope this helps.

Pete

Pete i think you may just have saved any more of my hair being pulled out. lol. Thank You. I shall just have a play about with it and mark this as solved once im certain.