Problem: Conditional formatting Match 2 Columns

Arnolf

Board Regular
Joined
Sep 18, 2005
Messages
78
Hello,
I have the next conditional formatting in the range A4:A16
=COUNTIF(B$4:B$16,A4)>0
(that identify any matches between columns A and B)

The problem I have, is that I need check one match by instance.
My problem with number 3.


I need the result of column D.

Or, if I have two instances of 3, I need the result of column G.


If I would have 3 instances of 3 or another number, the conditional formatting should identify the same instances of that number in the adjacent column.

rgds,
Arnolf
cformat.xls
ABCDEFGH
1
2
3
4919191
5828282
6131313
77773
8222
9333
10333
11333
12444
13555
14666
15
16
17
Sheet5
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Arnolf

I cannot understand exactly what you are after. No replies after 30 'hits' probably means others are having the same problem. Could you try to explain again in different words?
 
Upvote 0
Peter,

Thank you for your attention.

I have 2 columns of data. Column A and Column B.
I want Conditional Formatting highlight all the numbers of column A that are identical/match in column B.
I did this with the formula: in range A4:A16 =COUNTIF(B$4:B$16,A4)>0

So, I've got this with conditional formatting: (problem with number 3)
cformat.xls
ABCD
1
2
3
491
582
613
77
82
93
103
113
124
135
146
15
16
17
Sheet5



But, I need this with conditional formatting:
cformat.xls
ABCD
1
2
3
491
582
613
77
82
93
103
113
124
135
146
15
16
17
Sheet5
 
Upvote 0
Book8
ABCD
3XY
491
582
613
77
82
93
103
113
124
135
146
Sheet1


The non-numeric label in A3 is required.

The formula for the Formula Is option is:

=ISNA(MATCH($A4,$A$3:A3,0))*ISNUMBER(MATCH($A4,$B$4:$B$6,0))
 
Upvote 0
Aladin,
Thank you for the solution. It certainly does what I was looking for.
But, here starts my second problem that is crashing my head:
If I enter another number 3 in column B, the conditional formatting formula doesnt highlight that second number 3 in column A.

I also need this:
cformat.xls
ABCD
1
2
3XY
491
582
613
773
82
93
103
113
124
135
146
15
16
17
Sheet5



And I need this (if I have 3 numbers 3 in column B)
cformat.xls
ABCD
1
2
3XY
491
582
613
773
823
93
103
113
124
135
146
15
16
17
Sheet5
 
Upvote 0
Try...

Formula Is:

=(COUNTIF($A$4:A4,A4)<=COUNTIF($B$4:$B$8,A4))*ISNUMBER(MATCH($A4,$B$4:$B$8,0))

Hope this helps!
 
Upvote 0
Thank you Domenic!!!
That is it.
It is exactly what I am looking for.

Cheers,
Arnolf
:biggrin:
 
Upvote 0
Would the shorter
=(COUNTIF($A$4:A4,A4)<=COUNTIF($B$4:$B$8,A4))*ISNUMBER(A4)
do the same job?
 
Upvote 0
Peter_SSs said:
Would the shorter
=(COUNTIF($A$4:A4,A4)<=COUNTIF($B$4:$B$8,A4))*ISNUMBER(A4)
do the same job?

Yep, definitely! In fact, so would...

=(A4<>"")*(COUNTIF($A$4:A4,A4)<=COUNTIF($B$4:$B$8,A4))
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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