Compare two columns and have data separated

[RobertWC]

I have two large sets of names (23,000 in one column, 18,000 in the other). I need to know what names in column B ARE NOT in column A. I would like to see if all distinct names to be moved to column C. Is this possible?

 

[NARAYANK991]

Hi ,

It is certainly possible , using either formulae or VBA code ; given the size of your data , it may happen that the formula-based solution is time-intensive , and bogs down the workbook , but that will have to be seen.

Can you upload at least a sample workbook with about 50 names in each column ?

Narayan

 

[RobertWC]

Because these are employees, I can't post an actual list. But this does the trick. In Column A are the 45 US presidents. In Column B there are presidents and fictional characters. I am trying to separate the fictional characters into column C. Either way, thank you for taking the time to help.

 

[bosco_yip]

Try,

In C3, formula copied down :

=IFERROR(INDEX(B$3:B$46,AGGREGATE(15,6,ROW(A$3:A$46)-ROW(A$2)/ISNA(MATCH(B$3:B$46,A$3:A$46,0)),ROWS($1:1))),"")

Regards
Bosco

 

[RobertWC]

Thank you very much! I think I can go in and work with that. Just have to expand the selection by 20,000+, but I think I can do that much at least... maybe.

 

[RobertWC]

Well apparently it wasn't as simple as replacing the 46 with 27150. (I had more than I originally thought)

 

[bosco_yip]

Well apparently it wasn't as simple as replacing the 46 with 27150. (I had more than I originally thought)

Try to use helper column way by,

1] In Helper Column C2, enter : 0

2] In Helper Column C3, formula copied down :

=IF(ISNA(MATCH(B3,A$3:A$27200,0)),LOOKUP(9^9,C$2:C2)+1,"")

3] In Result Column D1, enter formula :

=LOOKUP(9^9,C:C)

4] In Result Column D3, formula copied down until blank :

=IF(ROWS($1:1)<=D$1,INDEX(B$3:B$27200,MATCH(ROWS($1:1),C$3:C$27200,0)),"")

Regards
Bosco

 

[RobertWC]

Thank you to everyone who responded. I actually found a work-around without formulas. Feel stupid for not thinking of it earlier.
Used Conditional formatting to highlight the matches between columns, then filtered out all of the highlighted cells in Column B. Copied/pasted to Column C