Forum

Notifications
Clear all

From Mitul - Highlight conditional formatting that exist in list 1 and list 2 - sample data

4 Posts
2 Users
0 Reactions
141 Views
(@mitulpar1964)
Posts: 64
Estimable Member
Topic starter
 
List 1 List 2
Highlight cells  That meet certain criteria Highlight 3 smallest values with criteria
Highlight  cells based on  the value of another cell Conditional formatting dates overlap
Highlight  values That Exist  in List1 , but not List 2 Conditional formatting gantt chart
Highlight values That Exist in List 1  and List 2 Conditional formatting gantt chart weekends
Highlight  Weekend dates Conditional formatting highlight target percentage
Highlight  Dates  between two Dates Find duplicate values in two columns
Highlight Dates on Due Dates Highlight approximate match lookup conditional formatting
Highlight Data based on Percentile Rank Highlight blank cells
Highlight statistical  outliers Highlight bottom values
  Highlight cells that begin with
  Highlight cells that contain
  Highlight cells that contain one of many
  Highlight  cells that end  with
  Highlight  cells that equal
  Highlight column differences
  Highlight data by quartile
  Highlight Dates between two Dates
  Highlight dates greater than
  Highlight  dates in same month and year
  Highlight  dates in Next N days
  Highlight Weekend Dates
  Highlight duplicate columns
  Highlight duplicate rows
  Highlight  duplicate values
 
Posted : 20/03/2018 4:29 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hi Mitul,

I am confused. What is your question?

Br,
Anders

 
Posted : 20/03/2018 3:32 pm
(@mitulpar1964)
Posts: 64
Estimable Member
Topic starter
 

Hi;  Anders;

Good Morning

This is Mitul.

How are you ?

Thank you  for sending me correct GETPIVOTDATA function and  article written by Mynda, which is very nice.

Now  in Highlight conditional formatting that exist in list 1 and list 2   above I like to learn what conditional formula to write to highlight  2 items 

 

Thank you very much.

Have a great day.

 
Posted : 21/03/2018 7:39 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello Mitul,

I hope you're good.

The easiest way to compare two columns and highlight the cells where you have same data is to select the range of data you want to compare, in Conditional Formatting choose Highlight Cells Rules and pick Duplicate Values.

If you want to use a formula, use COUNTIF.

Let's say you have your data which you want to compare in columns A2:A11 and C2:C11, select the range of data in column A and go to Conditional Formatting --> New Rule --> Use a formula to determine which cells to format. Type in following formula:

=COUNTIF($C$2:$C$11,$A2)>0

Add the needed format to how the cell/-s are to be highlighted and save. Do the same for the next column, select the range of data in column C and create a new rule, now the formula is:

=COUNTIF($A$2:$AC$11,$C2)>0

If you want to highlight those cells that have individual values that do not exist in the other column, just change the greater than symbol (>) to equal symbol (=) and you are good to go. Below picture shows both of those Conditional Formattings. In column A I highlight in red those cells which have individual values, no duplicates exists in column C. In column C I highlight in green those cells where we have duplicates in column A.

Example1-1.JPG

But the easiest way is to use Duplicate Values formatting rule, you can after all change so it also highlights the individual cells, where there are no duplicate values.

Example2-1.JPG

I hope this answers your question.

Br,
Anders

 
Posted : 21/03/2018 6:19 pm
Share: