Search column and delete duplicates

[unreasonable]

I have 2 sets of data, column A contains 200 unique names. Column B contains 55 of the names from column A. What I want to do is search column A and remove the names that are present in column B so that the difference would be column A containing 145 unique names and column B would contain 55. I would prefer to do this using a helper column or two and a formula, but if it requires a macro, that will do as well. Any help is greatly appreciated.

 

[hiker95]

unreasonable,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

To attach screenshots, see below: Post a screen shot with one of these:

If you are not able to give us screenshots, see below: You can upload your workbook to Box Net

 

[unreasonable]

I'm using Excel 2007.

columnA columnB
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>harry</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>tom</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>brian</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">****</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ashley</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">harry</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>rachel</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">doug</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ashley</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">brian</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">rachel</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">chris</TD></TR></TBODY></TABLE>

Based on the names in column A, I want column B (or C, since B will likely be a hidden helper column) to read:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>tom</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>****</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>chris</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>doug</TD></TR></TBODY></TABLE>

 

[hiker95]

unreasonable,

Excel Workbook
	A	B	C
1	harry	tom	tom
2	brian	****	****
3	ashley	harry	doug
4	rachel	doug	chris
5		ashley
6		brian
7		rachel
8		chris
9
Sheet1

The formula in cell C1, copied down:

=IF(ROWS($1:1)<=(COUNTA($B$1:$B$8)-COUNTA($A$1:$A$8)),INDEX($B:$B,SMALL(INDEX(ROW($B$1:$B$8)+(COUNTIF($A$1:$A$8,$B$1:$B$8)
>0)*10^10,0),ROWS($2:2))),"")

 

[unreasonable]

Will this also work if the names are formatted as "John" instead of "john" or "Smith, L John" instead of "john"? Thanks for the quick reply btw :D

 

[hiker95]

unreasonable,

Will this also work if the names are formatted as "John" instead of "john" or "Smith, L John" instead of "john"?

Have you tried it with different names?


Adjust the lower range in the formula to fit your actual data:

=IF(ROWS($1:1)<=(COUNTA($B$1:$B$8)-COUNTA($A$1:$A$8)),INDEX($B:$B,SMALL(INDEX(ROW($B$1:$B$8)+(COUNTIF($A$1:$A$8,$B$1:$B$8)>0)*10^10,0),ROWS($2:2))),"")

 

[unreasonable]

Just tested it with over 500 rows of names. Looks good! Thanks for the help, I greatly appreciate it! :D