Match formula wihin Conditional Formatting

007juk

Board Regular
Joined
May 16, 2007
Messages
95
Hi,

Looking for a formula for the following please.

I have 2 columns - debits and credits. What I want is a conditional format that will look down both columns and highlight duplicate entries within both columns.

The end result being either cells that don't match either formatted a colour or left as 'no fill'

Many thanks,
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Here is one way.

If you are looking to match each column against itself you would use the formulas below with conditional formatting. With the formulas below I named the ranges instead of just selecting the range. Select the Credits range and enter the first formula with Credits and then just reverse the process and select the Debits range and enter the formula with Debits. After you enter the formula select whatever format you want.

Change A2 and B2 to suit your needs. Starting with A2 and B2 assumes A1 and B1 are column headers.

Code:
=IF(COUNTIF(Credits,A2)>1,1)
=IF(COUNTIF(Debits,B2)>1,1)

If you choose not to use named ranges then you just need select the entire range and replace where I have Credits and Debits. It would look like this instead.

Code:
=IF(COUNTIF($A$2:$A$14,A2)>1,1)
 
Upvote 0
Thanks Jeff,

I have that formula and that is good for identifying duplivate entries within a range, but I want to compare to another column, i.e. A & B. Tried using the Match formula, but generates results as number and not within conditional formatting.
 
Upvote 0
How about this...again using named ranges

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Credits</TD><TD style="TEXT-ALIGN: center">Debits</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">6</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #ff0000; BORDER-BOTTOM-COLOR: #ff0000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #ff0000; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>A2</TD><TD>1. / Formula is =COUNTIF(Debits,A2)>0</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Abc</TD></TR><TR><TD>B2</TD><TD>1. / Formula is =COUNTIF(Credits,B2)>0</TD><TD style="BACKGROUND-COLOR: #ffff99">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Jeff,

Tried the revised version in my sheet and unfortunately it didn't work. I tried to apply in a new workbook, but it didn't work either. I changed the formula to:

within cell A2

=countif(Credits,A2)>0

and the above did work, just switch the named ranges about, but when I applied to my original workbook it didn't work, it just formatted each cell containimng a number with the colour I selected, rather than leaving the number that don't match as 'no fill'.
 
Upvote 0
Unfortunately I can't post a screen shot. This is what I'm trying to obtain from the formula:

H605 - H609 (Debit column)
98,238.53
101,562.39
114,992.34
116,910.91
118,800.42

I610 - 614 (Credit column)
101,562.39
113,066.15
114,992.34
116,910.91
118,800.42

When I apply conditional format I want it to format cells H606,H608,H609 as the colour yellow and cells I610, I612, I613, I614 as yellow too, leaving the cells that don't match H605 and I611 as 'no fill'

Thanks,
 
Upvote 0
This works for me...

=COUNTIF($I$610:$I$614,H605) > 0
=COUNTIF($H$605:$H$609,I610) > 0

Remove the spaces from infront and behind the > sign.
 
Upvote 0
Hi,

Yes managed to get that to work - my ranges where the wrong way around.

I can see how this formula works, but I thought it would be a 'match' formula. Never thought of using the 'countif' function.

So is the formula looking for the exact figure in the next column and if 'yes' fills with a colout. Is that why the '0' is used with the '>' sign ?

Thanks again.
 
Upvote 0
Yes you could use Match. I guess since I first learned the countif I just go with what works. If you use Match it would be =MATCH(A9,$B$14:$B$18,0). Change the ranges to match yours. The >0 is looking for a TRUE. If the countif finds the number duplicated it generates a 1 which Excel makes as TRUE. If there is no match it is 0; therefore, FALSE.

HTH
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top