Suppose we have to compare two lists of data as given below. Basically we want to find which elements in first list is not present in second list and vice versa.
To highlight the data which is not covered in other list follow below steps:
- Select first data range A2:A17
- Go to "Home" tab >>> Conditional Formatting >>> Click "New Rule…"
- Select the option "Use a formula to determine which cells to format"
- Enter the formula =COUNTIF ($B$2:$B$25, A2) = 0
- Go to "Format...” to select required highlighting format and click OK
Explanation: The COUNTIF function applied here will count all the instances of A2 in entire second list, if the same is not found, the count will be zero and hence the entry will be highlighted. Similarly, it will check for all the instances in first list.
Similarly, we can highlight cells in second list which are not in the first list by following steps:
- Select second data range B2:B25
- Go to "Home" tab >>> Conditional Formatting >>> Click "New Rule…"
- Select the option "Use a formula to determine which cells to format"
- Enter the formula =COUNTIF ($A$2:$A$17, B2) = 0
- Go to "Format...” to select required highlighting format and click OK
No comments:
Post a Comment