Search column and delete duplicates

unreasonable

Board Regular
Joined
Feb 10, 2011
Messages
53
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

Board Regular
Joined
Feb 10, 2011
Messages
53
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
unreasonable,



Excel Workbook
ABC
1harrytomtom
2brian********
3ashleyharrydoug
4racheldougchris
5ashley
6brian
7rachel
8chris
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))),"")

 
Last edited:

unreasonable

Board Regular
Joined
Feb 10, 2011
Messages
53

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

Board Regular
Joined
Feb 10, 2011
Messages
53
Just tested it with over 500 rows of names. Looks good! Thanks for the help, I greatly appreciate it! :D
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top