If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to find and list duplicates
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!! |
#2
|
|||
|
|||
Formula to find and list duplicates
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) -- Biff Microsoft Excel MVP "Charlotte B" Charlotte wrote in message ... 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!! |
#3
|
|||
|
|||
Formula to find and list duplicates
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" Charlotte wrote in message ... 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!! |
#4
|
|||
|
|||
Formula to find and list duplicates
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" wrote: 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) -- Biff Microsoft Excel MVP "Charlotte B" Charlotte wrote in message ... 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!! |
#5
|
|||
|
|||
Formula to find and list duplicates
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. -- Biff Microsoft Excel MVP "Charlotte B" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "Charlotte B" Charlotte wrote in message ... 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!! |
Thread Tools | |
Display Modes | |
|
|