Extracting duplicate values into a list

[msche09]

Hello,

I was hoping someone could let me know a formula or other way to extract all of the duplicate values and list them in another list.

For example:
B2 = Jeff
B3 = Jack
B4 = Gill
B5 = Jeff
B6 = Rob
B7 = Peter
B8 = Jenny
B9 = Peter

In this case, Jeff and Peter are duplicated. I would like their names to automatically appear in another list that is located in column R. The first duplicated name would go into R3, the second would go into R4, the third into R5, etc.

Thanks in advance.

[MarvinP]

Hi msche,

How about using a Pivot Table and filter with count of names greater than 1? See the attached. You could also use a helper column and advanced filter but I like Pivots.

PT count GT 1.xlsx

[msche09]

A pivot table could work I believe.

The other thing I forgot to mention is that the names would be inputted throughout the year. So I won't have any names in January and by December I may have up to 400 names (many being duplicated). Would this still work with a pivot table?

[MarvinP]

I don't see why not. You may need to refresh the Pivot Table after new data is entered. You might also need to expand the range of names that the Pivot Table come from

[msche09]

Wonderful.

This is working great.

Thanks for the help.