removing entries

dilli · Mar 17th, 2002, 08:20 PM

i have a long column of phonenumbers(A) and a shorter column(B)

how do i remove phonenumbers in columnB from A?

Dave Hawley · Mar 17th, 2002, 08:25 PM

Hi Dilli

Best way (and quickest) is this.

Copy # in Column B to the end of Column A so that you have one long list of numbers.Ensure you have a heading in Cell A1

Select any single cell in column and go to Data>Filter>Advanced filter. Choose "Copy to another location" Choose a range to "Copy To:" tick "Unique Records only" and click Ok

That's it.

dilli · Mar 17th, 2002, 08:47 PM

Tried it, but it led to me getting another list with ALL uniques from A + B , what i seek is a list of listA - listB.

Dave Hawley · Mar 17th, 2002, 08:58 PM

Try

=IF(COUNTIF($B:$B,A1)=0,A1,"")

In column C then sort and change to values via Paste Special.

dilli · Mar 17th, 2002, 09:58 PM

that will only write a1 if not found in columnB right? , but i need it to cover whole column A , not just a1.

Dave Hawley · Mar 17th, 2002, 10:03 PM

Copy it down.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 17th, 2002, 08:20 PM	#1
dilli New Member Join Date: Mar 2002 Posts: 3	i have a long column of phonenumbers(A) and a shorter column(B) how do i remove phonenumbers in columnB from A?

Mar 17th, 2002, 08:25 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Dilli Best way (and quickest) is this. Copy # in Column B to the end of Column A so that you have one long list of numbers.Ensure you have a heading in Cell A1 Select any single cell in column and go to Data>Filter>Advanced filter. Choose "Copy to another location" Choose a range to "Copy To:" tick "Unique Records only" and click Ok That's it.

Mar 17th, 2002, 08:47 PM	#3
dilli New Member Join Date: Mar 2002 Posts: 3	Tried it, but it led to me getting another list with ALL uniques from A + B , what i seek is a list of listA - listB.

Mar 17th, 2002, 08:58 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Try =IF(COUNTIF($B:$B,A1)=0,A1,"") In column C then sort and change to values via Paste Special.

Mar 17th, 2002, 09:58 PM	#5
dilli New Member Join Date: Mar 2002 Posts: 3	that will only write a1 if not found in columnB right? , but i need it to cover whole column A , not just a1.

Mar 17th, 2002, 10:03 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Copy it down.