if value exists in all 3 columns

gtd526

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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
gtd526 said:
How can I highlight cells if value exists in ALL 3 columns?
Click to expand...
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
StephenCrump said:
I'm guessing:
Click to expand...
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
StephenCrump said:
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.
Click to expand...
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
Peter_SSs said:
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
Click to expand...
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
gtd526 said:
The values will only appear once in each column.
Click to expand...
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
StephenCrump said:
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
Click to expand...
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
StephenCrump said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
317
gtd526
gtd526
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
340
Tetra201
Tetra201
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
348
gtd526
gtd526
gtd526
  • Solved
Count consecutive value
Replies
5
Views
361
gtd526
gtd526
T
  • Solved
Adding additional text to a value pulled from another sheet
Replies
8
Views
449
Snakehips
Snakehips

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

Share this page

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