# Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

1. ## Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

This seemed simple a few hours ago.

A log of invoice details MAY contain duplicate entries. If the columns for INVOICE and DATE and AMOUNT on one row are repeated on another row, the duplicates need to be highlighted. Highlight can be applied to any or all three columns or even the entire row.

Duplicates in any one of the three columns is common (even Invoice number). It is when the same value appears in all three columns in multiple rows that a duplicate invoice has been entered and highlighting is needed.

Alternatively, I could use an IF and COUNTIFS formula but that would take up an extra column, but I would prefer conditional formatting if possible...or possibly a VBA solution.

2. ## Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

For a CF formula you could try something like this:
(Assuming A2:A1000 = Invoice; B2:B1000 = Date; C2:C1000 = Amount)
Select Range A2:C1000
and use this formula in the CF,New Rule,Use Formula :
=COUNTIFS(\$A\$2:\$A\$1000,A2,\$B\$2:\$B\$1000,B2,\$C\$2:\$C\$1000,C2)>1
Format,Fill...select color,OK,OK

Hope this helps

EDIT-
For a large set of data, this may slow down processing time when you make changes to your workbook...

3. ## Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

Hi dredwolf,

Your suggestion works perfectly! I can only make it format the first column. It would be nice to highlight all three elements or the whole row but I think I may be pushing my luck!

thanks again for the speedy and most helpful response.

Best regards,
tom

4. ## Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

aah, Maybe this then:
=COUNTIFS(\$A\$2:\$A\$1000,\$A2,\$B\$2:\$B\$1000,\$B2,\$C\$2:\$C\$1000,\$C2)>1

that should highlight all 3 elements, I missed the "\$" in the original solution, sorry about that

Hope that helps

5. ## Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

Aha! That highlights the entire row...perfect. Thanks again, dredwolf! tom

6. ## Re: Conditional Formatting: Find When Values in 3 Columns Are Repeated in Rows

You are welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.05890 seconds
• Memory Usage 8,959KB
• Queries Executed 14 (?)
Template Usage (31):
• (1)footer
• (1)forumrules
• (1)gobutton
• (6)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (6)postbit_legacy
• (6)postbit_onlinestatus
• (6)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./includes/functions_notice.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php

Hooks Called (47):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit
• tag_fetchbit_complete
• forumrules