Compare names on 2 columns

[sulbaran]

Hello to all

I need to compare names in two columns, task looks simple but I can't figure out using conditional formating or some solutions around internet? I have one Column ( said A) with names on completed procedures and another column ( said B ) with names on procedures charged, my goal is to find out what names on column A don't show in column B and if possible listed in a another column ( said C )
In my case will be valid for another report that shows instead of name, have procedures names...
As in the below example: the name " Calling John is on Column A but not on the Column B and I'll like that name in column B and will like that in another column, ( said C ), also the names Cook, Oscar and Salders, Paul are in column B but not on A and I'll like that in another column ( said D )

The report include thousands of rows.
Thanks in advance for you help ...
Sulbaran

Names Completed	Names Charged
Calling, John <TBODY> </TBODY>	Brown, Henry <TBODY> </TBODY>
Vince, Sunday <TBODY> </TBODY>	Walker, Hardy <TBODY> </TBODY>
Williams, Anthony <TBODY> </TBODY>	Morgan, Oliver <TBODY> </TBODY>
Taylor, Bruce <TBODY> </TBODY>	Taylor, Bruce <TBODY> </TBODY>
Brown, Henry <TBODY> </TBODY>	Reed, Dustin <TBODY> </TBODY>
Walker, Hardy <TBODY> </TBODY>	Williams, Anthony <TBODY> </TBODY>
Reed, Dustin <TBODY> </TBODY>	Cook, Oscar <TBODY> </TBODY>
Morgan, Oliver <TBODY> </TBODY>	Sanders, Paul <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[CodeNinja]

If you have excel 2010 (or 2013), you can use the aggregate function to avoid using an array formula to solve this problem...

In Row 2 of Column C, use the following formula and copy down:
=IFERROR(INDEX($A$1:$A$9,AGGREGATE(15,6,ROW($A$2:$A$9)/(COUNTIF($B$2:$B$9,$A$2:$A$9)=0),ROWS($C$1:C1))),"")

In Row 2 of Column D use the following formula and copy down:
=IFERROR(INDEX($B$1:$B$9,AGGREGATE(15,6,ROW($B$2:$B$9)/(COUNTIF($A$2:$A$9,$B$2:$B$9)=0),ROWS($C$1:C1))),"")

 

[Akashwani]

Hi Sulbaran

Take a look here.....

Excel Magic Trick 540: Extract New Records Not In Old List - Array Formula Method - YouTube

Ak