if value exists in all 3 columns

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
675
Office Version
  1. 2019
Platform
  1. Windows
Hello,
How can I highlight cells if value exists in ALL 3 columns?
conditional formatting??
thx
NBA.xlsm
ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131 OKCOKC
Favs
Cell Formulas
RangeFormula
AC128:AC131AC128=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$128:AC128))),"")
AD128:AD131AD128=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$128:AD128))),"")
AE128:AE131AE128=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$128:AE128))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How can I highlight cells if value exists in ALL 3 columns?
Is this what you mean?

24 02 28.xlsm
ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131OKCOKC
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC128:$AE128,"?*")=3textNO
 
Upvote 0
Yes, the question is ambiguous. I'm guessing:

ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131OKCOKC
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC$128:$AE$131,AC128)=3textNO

which is based on the assumption that a value may appear either 0 or 1 times (no more!) in each column.
 
Upvote 0
I'm guessing:
I like your guess better but could be foiled if a value can occur more than once in a column like this.

24 02 28.xlsm
ACADAE
128ORLMINCLE
129MINORLOKC
130OKCBOSMIN
131OKC
CF (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC$128:$AE$131,AC128)=3textNO


This covers that case but can it be done in a simpler way?

24 02 28.xlsm
ACADAE
128ORLMINCLE
129MINORLOKC
130OKCBOSMIN
131OKC
CF (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC$128:$AC$131,AC128)*COUNTIF($AD$128:$AD$131,AC128)*COUNTIF($AE$128:$AE$131,AC128)textNO
 
Upvote 0
I have a feeling there will be lots more than three columns, so perhaps:

ACADAE
128ORLMINCLE
129MINORLOKC
130OKCBOSMIN
131OKC
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=AND(MMULT(TRANSPOSE(ROW($AC$128:$AC$131)),--($AC$128:$AE$131=AC128)))textNO
 
Upvote 0
Yes, the question is ambiguous. I'm guessing:

ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131OKCOKC
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC$128:$AE$131,AC128)=3textNO

which is based on the assumption that a value may appear either 0 or 1 times (no more!) in each column.
Yes, if the value is repeated in each column (3 Times).
The values will only appear once in each column.
How can we eliminate blank cells?
Row 134 will be the end of the range. And each cell contains a formula.

NBA.xlsm
ACADAE
128INDLACCHI
129MIN  
130   
131   
132   
133   
134   
Favs
Cell Formulas
RangeFormula
AC128:AC134AC128=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$128:AC128))),"")
AD128:AD134AD128=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$128:AD128))),"")
AE128:AE134AE128=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$128:AE128))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE134Expression=AND(MMULT(TRANSPOSE(ROW($AC$128:$AC$131)),--($AC$128:$AE$131=AC128)))textNO
AC128:AE134Expression=COUNTIF($AC$128:$AC$134,AC128)*COUNTIF($AD$128:$AD$134,AC128)*COUNTIF($AE$128:$AE$134,AC128)textNO
 
Upvote 0
I like your guess better but could be foiled if a value can occur more than once in a column like this.

24 02 28.xlsm
ACADAE
128ORLMINCLE
129MINORLOKC
130OKCBOSMIN
131OKC
CF (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC$128:$AE$131,AC128)=3textNO


This covers that case but can it be done in a simpler way?

24 02 28.xlsm
ACADAE
128ORLMINCLE
129MINORLOKC
130OKCBOSMIN
131OKC
CF (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE131Expression=COUNTIF($AC$128:$AC$131,AC128)*COUNTIF($AD$128:$AD$131,AC128)*COUNTIF($AE$128:$AE$131,AC128)textNO
If the value is repeated in each column (3 Times).
The values will only appear once in each column.
How can we eliminate blank cells?
Row 134 will be the end of the range. And each cell contains a formula.


NBA.xlsm
ACADAE
128INDLACCHI
129MIN  
130   
131   
132   
133   
134   
Favs
Cell Formulas
RangeFormula
AC128:AC134AC128=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$128:AC128))),"")
AD128:AD134AD128=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$128:AD128))),"")
AE128:AE134AE128=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$128:AE128))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE134Expression=COUNTIF($AC$128:$AC$134,AC128)*COUNTIF($AD$128:$AD$134,AC128)*COUNTIF($AE$128:$AE$134,AC128)textNO
AC128:AE134Expression=AND(MMULT(TRANSPOSE(ROW($AC$128:$AC$131)),--($AC$128:$AE$131=AC128)))textNO
 
Upvote 0
The values will only appear once in each column.
In that case, try this variation on Post #3

ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131LACOKCOKC
132   
Sheet1
Cell Formulas
RangeFormula
AC132:AE132AC132=IFERROR(1/0,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE132Expression=COUNTIF($AC$128:$AE$132,IF(LEN(AC128),AC128))=COLUMNS($AC$128:$AE$132)textNO
 
Upvote 0
Solution
In that case, try this variation on Post #3

ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131LACOKCOKC
132   
Sheet1
Cell Formulas
RangeFormula
AC132:AE132AC132=IFERROR(1/0,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE132Expression=COUNTIF($AC$128:$AE$132,IF(LEN(AC128),AC128))=COLUMNS($AC$128:$AE$132)textNO
Perfect. thx

NBA.xlsm
ACADAE
127@ Home
128Q >= 55%AL >=60%AM >=60%
129ORLORLCHA
130BRK ORL
131   
132   
133   
134   
135   
Favs
Cell Formulas
RangeFormula
AC129:AC135AC129=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AC$129:AC129))),"")
AD129:AD135AD129=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AD$129:AD129))),"")
AE129:AE135AE129=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$129:AE129))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC129:AE135Expression=COUNTIF($AC$129:$AE$135,IF(LEN(AC129),AC129))=COLUMNS($AC$129:$AE$135)textNO
 
Upvote 0
In that case, try this variation on Post #3

ACADAE
128ORLCLECLE
129MINORLBOS
130OKCBOSMIN
131LACOKCOKC
132   
Sheet1
Cell Formulas
RangeFormula
AC132:AE132AC132=IFERROR(1/0,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AC128:AE132Expression=COUNTIF($AC$128:$AE$132,IF(LEN(AC128),AC128))=COLUMNS($AC$128:$AE$132)textNO
I've expanded the formula you supplied. And its working well. thx.
AB:AC and AD:AE, 2 working formulas were supplied, so I used both.

NBA.xlsm
ABACADAEAF
126Possible Candidates for Odd Ball
127@ Home
128Q >= 55%AL >=60%AM >=60%AR=TRUEAll 4
129BOSDALDALORLORL
130ORLBOSBOSTOR 
131 MINMIN  
132 ORLORL  
133     
134     
135     
Favs
Cell Formulas
RangeFormula
AB129:AB135AB129=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*ISODD(ROW($Q$4:$Q$33)-ROW(Q$4)),ROW($Q$4:$Q$33)-ROW($Q$4)+1),ROWS(AB$129:AB129))),"")
AC129:AC135AC129=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($AL$4:$AL$33>=0.6)*($AL$4:$AL$33<>"")*ISODD(ROW($AL$4:$AL$33)-ROW(AL$4)),ROW($AL$4:$AL$33)-ROW($AL$4)+1),ROWS(AC$129:AC129))),"")
AD129:AD135AD129=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AD$129:AD129))),"")
AE129:AE135AE129=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/((AR$4:AR$33=TRUE)*(AR$4:AR$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AE$129:AE129))),"")
AF129:AF135AF129=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(A$4:A$33)-ROW(A$4)+1)/(($Q$4:$Q$33>=0.55)*($Q$4:$Q$33<>"")*(AM$4:AM$33>=0.6)*(AM$4:AM$33<>"")*(AL$4:AL$33>=0.6)*(AL$4:AL$33<>"")*(AR$4:AR$33=TRUE)*(AR$4:AR$33<>"")*ISODD(ROW(A$4:A$33))),ROWS(AF$129:AF129))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AF129:AF135Expression=COUNTIF($AB$129:$AF$135,IF(LEN(AF129),AF129))=COLUMNS($AB$129:$AF$135)textNO
AB129:AF135Expression=COUNTIF($AB$129:$AF$135,IF(LEN(AB129),AB129))=COLUMNS($AB$129:$AF$135)textNO
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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