Comments on: Distinct and unique values in Excel: how to find, filter, select and highlight

Comments page 2. Total comments: 50

1. 

   chandrasekar says:
   2017-08-02 at 9:42 am

   Sir,

   Pls give solution. Particular one cell in more data value (exp: 45,56,45,56)how to find duplicate value in one cell.

2. 

   carwell says:
   2016-12-27 at 5:16 am

   Dear Sevtlana,

   This is my second request. Sorry. I am just trying to ask some help regarding a formula a for a cell using conditional formatting, i badly need this for my training matrix which covers different trainings and different expiry dates.

   Training dates that will highlight if it will expire in 1,2,3 years

   1 yr ( example 01-Aug-2016 will expire in 01-Aug-2017 onward ) and ( 1 year before the current date) highlighting in red with white font

   2 yr ( example 01-Aug-2016 will expire in 01-Aug-2018 onward ) and ( 2 year before the current date) highlighting in red with white font

   3 yr ( example 01-Aug-2016 will expire in 01-Aug-2019 onward ) and ( 3 years before the current date) highlighting in red with white font

   And it highlight on different color if cell value is blank/empty

   Please help me on this. i have been trying to ask help to all my friends but no one knows.

   Thanks and God bless

   Carwell

   1. 

      Svetlana Cheusheva says:
      2016-12-27 at 9:15 am

      Dear Carwell,

      You can create a few conditional formatting rules with the following formulas:

      Expire in 1 yr: =DATEDIF($A1, TODAY(), "y")<=1
      Expire in 2 yr: =DATEDIF($A1, TODAY(), "y")<=2
      Expire in 3 yr: =DATEDIF($A1, TODAY(), "y")<=3

      To highlight blank cells, use this formula: =$A1=""

      Where A1 is the topmost cell with a date.

      The detailed instructions on conditionally formatting dates can be found here:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-dates/

3. 

   Rhonda McDermitt says:
   2016-04-20 at 4:47 pm

   I need help with the following =If(B2:B11)="Pass","Completed" it works if I just us B2 but when I want to see if "Pass" is in each field that is when I get an error.

   1. 

      Svetlana Cheusheva says:
      2016-04-21 at 9:50 am

      Hello Rhonda,

      If you want to show "Completed" in each row, if a cell in column B in that row is "Pass", you can enter the following formula in row 2, and then copy it down to row 11:

      =IF(B2="Pass","Completed", "")

      If you want to show "Completed" if all 10 cells (B2:B11) contain "pass", then use this formula:

      =IF(COUNTIF(B2:B11, "pass")=10, "completed", "")