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

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Names Completed
[/TD]
[TD]Names Charged
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Calling, John
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Brown, Henry
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Vince, Sunday
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Walker, Hardy
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Williams, Anthony
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Morgan, Oliver
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Taylor, Bruce
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Taylor, Bruce
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Brown, Henry
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Reed, Dustin
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Walker, Hardy
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Williams, Anthony
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Reed, Dustin
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Cook, Oscar
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<TBODY>[TR]
[TD]Morgan, Oliver
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 217"]
<TBODY>[TR]
[TD]Sanders, Paul
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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