Extract Duplicate & Missing Hard Names from 2 tables

Hello guys,
I need a formula in excel to compare between 2 columns to find Duplicate & Missing Names and do the following
1-put the Duplicate names in Table 1 & 2 in new column called Duplicate Names
2-put the Unique names that exists in Table 1 and not exists in Table 2 in column called Unique Names In Table 1
3-put the Unique names that exists in Table 2 and not exists in Table 1 in column called Unique Names In Table 2

Note : I tried conditional format to highlight the duplicate & Unique names, But it did not success to find all Duplicate names also tried different equations

Originally Posted by bebo021999

Try in E3:

Please Login or Register  to view this content.

D3:

Please Login or Register  to view this content.

C3:

Please Login or Register  to view this content.

All are:
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

They might cause your PC slow down abit due to array formula. Just waiting for the calculating finished.

Thanks for replay, but still not working
in column C it suppose to extract over 790 duplicate names but nothing with this formula

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

I think the formula in C3 should be:

=IFERROR(IFERROR(INDEX($A$3:$A$791,MATCH(0,IF(COUNTIF($A$3:$A$791,$B$3:$B$997)>1,COUNTIF($C$2:C2,$A$3:$A$791),""),0)),INDEX($B$3:$B$997,MATCH(0,IF(COUNTIF($B$3:$B$997,$A$3:$A$791)>1,COUNTIF($C$2:C2,$B$3:$B$997),""),0))),"")

I tried this, found some duplicates but my computer "stopped" as this formula is very resource-hungry! Nearly 2 million comparisons!

@Glenn
You must have a turbo-charged m/c as mine virtual stopped using your AGGREGATE formula. I had the old "Calculating (4 processors) x%" message - time for cofffee!

Using VBA, results was "instanteous" !

Be interested to know the results you got so I can check my VBA results.

Thanks But still some duplicated names did not appear in column C
I think if you notice there are difference in names lengths between two column a & b
If you could write a formula to compare fixed equal names length between two column a & b to extract full duplicated name
Please if you could help me with this issue

This is a VBA compare using your data

Sorry there is still duplicated names in Column D & E
if you notice original Table 1 number of cell is 789 and Table 2 is 992
all 789 is duplicated in both tables, the difference name must be in maximum 203