Comparing two colums and KEEPING only the duplicates

[capnqwest]

Hi,

I have two very lengthy columns (A&B) that contain site ID's. They are not lined up or sorted in any way and I'm trying to find a way to have a macro locate the duplicates in column A&B and delete those sites that do not have a twin in the other column. I have found plenty of macros that do the opposite (delete the dupes) but I need to keep them. My attempts at modifying VBA examples were unsuccessful.

Any suggestions?

 

[capnqwest]

I'm trying this:

=IF(COUNTIF($A$2:$A$1700,B2)>0,B2,"")

Change B1 and $A$1:$A$10   to the first cells in the ranges from which data that you want to extract common items.  Then, use Fill Down (from the Edit menu) to fill the formula down to as many rows as you need to hold the common entries (i.e., up to as many rows as there are in the original range.)

but I can't get it to work.

 

[capnqwest]

Actually, it did work but it put the columns all the way down at the very bottom of the worksheet?

 

[just_jon]

Well, you could do something very low-tech like this as Step 1 --

Book1
	A	B	C	D
1	111	666	DeletefromA
2	222	123		DeletefromB
3	333	456		DeletefromB
4	444	789		DeletefromB
5	555	198	DeletefromA	DeletefromB
6	666	222
7	777	323	DeletefromA	DeletefromB
8	888	333	DeletefromA
9	999	444	DeletefromA
Sheet1

Next step: via Copy | Edit | Paste special | select Values, convert columns C & D to values. Then sort on column C, deleting the column A block of values associated with Delete from A; re-sort on column D and delete column B values. Columns C and D can then be deleted.