Match Names and Highlight Uniques

[Justinian]

My boss wants to know who we hired from 2001 through current. Each year, I keep a list of all employees so for each year, most names are the same from the previous year except for the new hires. How do I create a formula that will run through the names on all years 2001 through 2017, only highlighting the unique names?

 

[Joe4]

Re: Match ames and Highlight Uniques

If you have a list, and want to just highlight the unique entries, one way is to use Conditional Formatting.

Let's say your list is A2:A1000. Then select the whole range A2:A1000 and apply this Conditional Formatting formula:

=Countif($A$2:$A$1000,$A2)=1

and choose your format color.

 

[Justinian]

Re: Match ames and Highlight Uniques

Sorry, the lists are in separate columns. For example, the current 2017 employee list is A2:A108 and I want to compare that with the list of 2016 employees in C2:C111. Then I want to call attention to only the names in 2016 that are missing from 2017 so I know who to add to 2017.

 

[AhoyNC]

Re: Match ames and Highlight Uniques

Maybe one of the ways below:
To use conditional formatting:
Highlight your range for 2016
Then go to
-CONDITIONAL FORMATTING
-New Rule
-Use formula
formula in example (change ranges to match your data).
=ISERROR(MATCH(C2,$A$2:$A$12,0))

Or if you want a list of names then the formula in E2 of the example (change ranges to match your data). Drag formula down as needed.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	2017		2016		List not in 2017
2	Name1		Name1		Name4
3	Name2		Name2		Name6
4	Name3		Name3		Name28
5	Name44		Name4		Name30
6	Name5		Name5		Name12
7	Name22		Name6		Name13
8	Name7		Name7
9	Name8		Name28
10	Name9		Name9
11	Name10		Name10
12	Name11		Name30
13			Name12
14			Name13
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =ISERROR(MATCH(C2,$A$2:$A$12,0))	Abc

 

[Justinian]

Re: Match ames and Highlight Uniques

Ok, thank you.