formula to get duplicate entries

[asyamonique]

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>

 

[Eric W]

Try:

	A	B	C
1	COLUMN A	COLUMN B	Column B
2	100007211	100007211	100001196
3	100007291	100007291	100007205
4	100001196	100001196	100007211
5	100007214	100007214	100007214
6	100007235	100007235	100007235
7	100007205	100007205	100007291
8	100007211
9	100007291
10	100001196
11	100007214
12	100007235
13	100007205
14	100007211
15	100007291
16	100001196
17	100007214
18	100007235
19	100007205
20	100007211
21	100007291
22	100001196
23	100007214
24	100007235
25	100007205
26	100007211
27	100007291
28	100001196
29	100007214
30	100007235
31	100007205
32	100007211
33	100007291
34	100001196
35	100007214
36	100007235
37	100007205
38	100007211
39	100007291
40	100001196

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

Sheet4​

Worksheet Formulas Cell Formula C2 =IF(C1="","",IFERROR(SMALL($A$2:$A$40,COUNTIFS($A$2:$A$40,"<="&C1)+1),"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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.

 

[Marcelo Branco]

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.

 

[asyamonique]

Hi,
Thanks for the help Eric,
So far

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