# formula to get duplicate entries

#### asyamonique

##### Well-known Member
Hi,
Is there any formula to place columnB which I can get the duplicate data entries from columnA?
Many Thanks

 COLUMN A COLUMN B 100007211 100007211 100007291 100007291 100001196 100001196 100007214 100007214 100007235 100007235 100007205 100007205 100007211 100007291 100001196 100007214 100007235 100007205 100007211 100007291 100001196 100007214 100007235 100007205 100007211 100007291 100001196 100007214 100007235 100007205 100007211 100007291 100001196 100007214 100007235 100007205 100007211 100007291 100001196 100007214 100007235 100007205 100007211 100007291 100001196

<tbody>
</tbody>

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Eric W

##### MrExcel MVP
Try:

ABC
1COLUMN ACOLUMN BColumn B
2100007211100007211100001196
3100007291100007291100007205
4100001196100001196100007211
5100007214100007214100007214
6100007235100007235100007235
7100007205100007205100007291
8100007211
9100007291
10100001196
11100007214
12100007235
13100007205
14100007211
15100007291
16100001196
17100007214
18100007235
19100007205
20100007211
21100007291
22100001196
23100007214
24100007235
25100007205
26100007211
27100007291
28100001196
29100007214
30100007235
31100007205
32100007211
33100007291
34100001196
35100007214
36100007235
37100007205
38100007211
39100007291
40100001196

</tbody>
Sheet4

Worksheet Formulas
CellFormula
C2=IF(C1="","",IFERROR(SMALL(\$A\$2:\$A\$40,COUNTIFS(\$A\$2:\$A\$40,"<="&C1)+1),""))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B2{=IF(B1="","",IFERROR(INDEX(\$A\$2:\$A\$40,SMALL(IF(MATCH(\$A\$2:\$A\$40,\$A\$2:\$A\$40,0)=ROW(\$A\$2:\$A\$40)-ROW(\$A\$2)+1,ROW(\$A\$2:\$A\$40)-ROW(\$A\$2)+1),ROWS(\$B\$2:\$B2))),""))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula in B2 is an array formula, confirm with Control+Shift+Enter and drag down. Header in B1 is required. If the data are numeric, then you can use the shorter non-array formula in C2, which returns the data sorted.

#### Marcelo Branco

##### MrExcel MVP
Hi,
Is there any formula to place columnB which I can get the duplicate data entries from columnA?
Many Thanks

Do you want a list of unique values or only those values that appear more than once in column A?

Assuming the latter, try this array formula in B2 copied down
=IFERROR(INDEX(\$A:\$A,SMALL(IF(FREQUENCY(\$A\$2:\$A\$40,\$A\$2:\$A\$40)>1,ROW(\$A\$2:\$A\$40)),ROWS(B\$2:B2))),"")
Ctrl+Shift+Enter

Hope this helps

M.

Last edited:

#### asyamonique

##### Well-known Member
Hi,
Thanks for the help Eric,
So far
Code:
``[COLOR=#333333]=IF([/COLOR][COLOR=Blue]C1="","",IFERROR([COLOR=Red]SMALL([COLOR=Green]\$A\$2:\$A\$40,COUNTIFS([COLOR=Purple]\$A\$2:\$A\$40,"<="&C1[/COLOR])+1[/COLOR]),""[/COLOR])[/COLOR][COLOR=#333333])[/COLOR]``
that formula works great or me...
Marcelo thanks for your help also..

Replies
5
Views
956
Replies
11
Views
1K
Replies
3
Views
302
Replies
2
Views
385
Replies
3
Views
190

1,190,624
Messages
5,981,998
Members
439,750
Latest member
creaseA

### 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.

### Which adblocker are you using?

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

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