Formula to find and list duplicates

[Charlotte B]

I have two columns consisting of 1000 cells that each have 7 digits. For
example

8956743 8658237
8967842 8753689
8974389 8896536

These two columns share some of the same numbers. I want to find and LIST
all of these shared numbers. Some of these numbers are the same and some are
different. I just need a formula to find the duplicates. HELP ME!!

 

[T. Valko]

Try this...

List 1 is in the range A1:A1000. Refered to as rng1
List 2 is in the range B1:B1000. Refered to as rng2

Enter this array formula** in D1:

=INDEX(rng1,SMALL(IF(ISNUMBER(MATCH(rng1,rng2,0)),ROW(rng1)),ROWS(D$11))-MIN(ROW(rng1))+1)

Copy down until you #NUM! errors meaning all the dupes have been extracted.

Or, this array formula** is a a few keystrokes shorter and will list the
*numbers* in ascending order:

=SMALL(IF(ISNUMBER(MATCH(rng1,rng2,0)),rng1),ROWS(D$11))

Copy down until you #NUM! errors meaning all the dupes have been extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ashish Mathur]

Hi,

In cell A2, enter the following formula in Formats > Conditional formatting

Formula is

=vlookup(A2,list2,1,0)

Choose a color for the cell. All duplicate values in list 1 will be
highlighted.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Charlotte B]

somehow that didn't work for me. I must be missing a step. I'm new to excel
so I need exact steps. In columb A there are 1239 different numbers and in
columb B there are 119 numbers. Can you explain how you do theses formulas?

 

[T. Valko]

In columb A there are 1239 different numbers

and in columb B there are 119 numbers

Ok, that makes it better since now all you need to do is compare the short
list to the long list (less formulas needed).

Let's assume the lists are in the ranges A2:A1200 and B2:B120

Create 2 named ranges.

If the list in column A is in the range A2:A1200, click inside the name box.
The name box is that little space directly above column A that shows you
which cell is selected. Type in the name box A2:A1200 and hit the ENTER key.
This will select the range A2:A1200. Click in the name box again and type in
a name for that range then hit the ENTER key. For this example I'll use the
name rng1. Repeat this process for the list in column B. For this example
I'll use the name rng2.

Now, enter this array formula** in C2:

=INDEX(rng2,SMALL(IF(ISNUMBER(MATCH(rng2,rng1,0)),ROW(rng2)),ROWS(C$2:C2))-MIN(ROW(rng2))+1)

** Do not hit the ENTER key. Instead, hold down both the CTRL key and the
SHIFT key then hit the ENTER key. When done properly Excel will enclose the
formula in squiggly brackets { }. You *can not* just type these brackets in.
You *must* use the key combination to produce them. Also, if you edit or
change the formula later on you *must* re-enter it using the key
combination.

Drag copy the formula in C2 down column C until you get results of #NUM!.
This means all the dupes have been extracted and the data has been
exhausted.