Extract repeated data from a range

I have a range of data where I want to extract all items that exist more than one time. In the example below is that only one item (OBRAUT). Any idea about a good function or VBA-code I can use. Input data can be in column A and output data in column B.

Please Login or Register  to view this content.

Formula wise... if you assume your sample values are in Column A

C1:
=SUMPRODUCT((A1:A100<>"")*(COUNTIF(A1:A100;A1:A100&"")>1))/2

The above gives you count of duplicate terms - thereby allowing you to limit the no. of times you look for duplicated values

B1:
=IF(ROWS(B$1:B1)>$C$1;"";INDEX($A$1:$A$22;MATCH(1;INDEX(($A$1:$A$22<>"")*(COUNTIF($A$1:$A$22;$A$1:$A$22&"")>1);0);0)))
copied down

EDIT: the formula in C1 is not valid... I will post back a revision (see below)

C1:
=SUMPRODUCT((A1:A100<>"")*((COUNTIF(A1:A100,A1:A100&"")>1)/COUNTIF(A1:A100,A1:A100&"")))

probably a better way to calc this though...

Sorry - I didn't think that one through quite as well as I should have !

First - insert a blank header row - ie insert a row into the sheet such that A1:C1 etc are all blank and present formulae have moved to row 2

Then modify B2 to be:

Please Login or Register  to view this content.