Indicate Duplicate Entries Across Multiple Columns

[Cameron]

I have a large set of data (30,000+ entries) entered in one sheet across
multiple columns. It is assumed that each column contains a non-repetitive
set of data. I need a function that will cross check each column, and
indicate (either by generated list, highlighting, etc), common entries.
Entries are all text in nature, program is Mac Excel 2008. Any help is
appreciated.

 

[JBeaucaire]

Hope this works the same as PC, conditional formatting can do it.

Highlight the ENTIRE data set, make sure the first cell is the primary cell
(A1?). Let's presume it's A1:G10000 for the range.

Format > Conditional Formatting > Condition1: FormulaIs:
=COUNTIF($A$1:$G$10000,A1)>1

....and set your pattern background color before saving it. Now ALL the
cells that have data appearing more than once will light up with the color
you select.

 

[Cameron]

That seems to have done the trick. Is there anyway to set a threshold? For
example - saying only highlight if the number of occurrences is greater then
3?

 

[JBeaucaire]

Sure, just change the >1 to >3

=COUNTIF($A$1:$G$10000,A1)>3

 

[JBeaucaire]

Actually, you want to get real slick, pick a cell to put a value in, say J1,
then use this formula so you can highlight cells based on the value in that
cell:

=COUNTIF($A$1:$G$10000,A1)>$J$1

 

[Cameron]

So far so good. The only problem I'm having is that it seems it's actively
doing the formatting, causing a serious slowdown on the system with the
amount of data that I have. Is it possible to do the formatting once, and
shut the process off, while still remaining formatted?

 

[JBeaucaire]

There's not a way built into Excel. But here's a page that offers 3 macros
that work together to do what you want. Hope this helps.

http://excel.tips.net/Pages/T001947_Removing_Conditional_Formats_but_Not_the_Effects.html