formula to get duplicate entries

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,280
Office Version
  1. 2013
Platform
  1. Windows
Hi,
Is there any formula to place columnB which I can get the duplicate data entries from columnA?
Many Thanks


COLUMN ACOLUMN B
100007211100007211
100007291100007291
100001196100001196
100007214100007214
100007235100007235
100007205100007205
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

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

<thead>
</thead><tbody>
</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))),""))}

<thead>
</thead><tbody>
</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.
 
Upvote 0
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:
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top