Indicate Duplicate Entries Across Multiple Columns
 

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.

Indicate Duplicate Entries Across Multiple Columns
 

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.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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.

Indicate Duplicate Entries Across Multiple Columns
 

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" wrote:

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.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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.

Indicate Duplicate Entries Across Multiple Columns
 

Sure, just change the 1 to 3

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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?

Indicate Duplicate Entries Across Multiple Columns
 

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
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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" wrote:

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.


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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.

Indicate Duplicate Entries Across Multiple Columns
 

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" wrote:

Sure, just change the 1 to 3

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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?

Indicate Duplicate Entries Across Multiple Columns
 

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_...E ffects.html

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Cameron" wrote:

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?