0:00
hi in this video we'll learn how to
0:02
compare two lists and highlight the
0:04
items that are unique to each list first
0:06
let's set up our data we have list one
0:08
in column A and list 2 in column B for
0:11
this example I'll assume our lists are
0:13
located from A1 to A10 and B1 to be 10
0:16
as you can see we have some items in
0:18
list one that may not be present in list
0:20
2 and vice versa in this video we'll use
0:23
the ctive function to identify unique
0:25
items in each list by counting how many
0:27
times an item from one list appears in
0:29
the other the C Tiff function has two
0:31
parameters the first parameter is the
0:33
range of cells where the function will
0:34
count occurrences based on the criteria
0:36
defined in the second parameter to find
0:38
unique values in column A we'll enter
0:41
the counti formula in cell C2 for the
0:43
first parameter we'll use an absolute
0:45
reference to column B ensuring that the
0:47
formula counts all items in that column
0:50
for the second parameter we use a
0:51
relative reference to A2 which allows
0:53
the function to check if the value in A2
0:55
is found in column B when we drag the
0:57
fill handle from C2 down to C10 the
1:00
relative reference will automatically
1:01
update to A3 A4 A5 and so on this means
1:05
each cell in column c will check the
1:07
corresponding value in column A against
1:09
the entire column B while the absolute
1:11
reference to column B remains unchanged
1:14
the ctif returns zero for unique items
1:17
by comparing the formula result to zero
1:19
we create a condition that returns a
1:20
Boolean value of true or false in
1:23
conditional formatting your formula must
1:25
return true or false to apply the
1:27
formatting if the return value is true
1:29
this specified formatting will be
1:31
applied now let's highlight the items in
1:33
list one that are not found in list two
1:36
select the range of list one go to the
1:38
Home tab click on conditional formatting
1:40
and select new rule choose use a formula
1:43
to determine which cells to format and
1:45
in the formula box enter formula shown
1:47
on screen next click on the format
1:49
button to choose a fill color and click
1:51
okay to apply the formatting now all
1:54
items in list one that are not in list
1:56
two are highlighted next let's do the
1:58
same for list two we want to highlight
2:00
items that are in list two but not in
2:02
list one select the range of list two
2:05
again go to conditional formatting and
2:07
select new rule choose use a formula to
2:10
determine which cells to format and
2:11
enter the formula shown on screen click
2:13
on the format button and choose a
2:15
different color like light blue and
2:17
click okay to apply the formatting now
2:19
we can see all the unique items in list
2:21
2 highlighted in blue if your lists are
2:24
located on two different worksheets you
2:26
can easily modify your formula to
2:27
reference the other sheet since office
2:30
2010 Excel has enabled users to
2:32
reference cell ranges from different
2:33
sheets in conditional formatting rules
2:36
to do this simply add the sheet name
2:38
followed by an exclamation mark before
2:40
referencing the cell range for example
2:42
if list 2 is located on sheet 2 you
2:44
would modify the first parameter of the
2:46
counti function to include the sheet
2:48
name so your formula would look like
2:50
this in this formula the first parameter
2:52
refers to the cell range in sheet two
2:55
allowing you to check for the presence
2:56
of each item in list one against list
2:58
two located on a different sheet
3:00
this method is efficient and can save
3:02
you a lot of time when working with
3:03
large data sets if you found this video
3:05
helpful please give it a thumbs up and
3:08
consider subscribing for more Excel tips
3:09
and tricks thanks for watching
