Comparing two colums and KEEPING only the duplicates

capnqwest

New Member
Joined
Sep 8, 2003
Messages
32
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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

capnqwest

New Member
Joined
Sep 8, 2003
Messages
32
I'm trying this:

Code:
=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

New Member
Joined
Sep 8, 2003
Messages
32
Actually, it did work but it put the columns all the way down at the very bottom of the worksheet?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Well, you could do something very low-tech like this as Step 1 --
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,411
Messages
5,595,985
Members
414,035
Latest member
billbumkins

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