Identifying unique entries

[julianhall]

I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).

So, for example, if i had the following values in column C:

• One
• Two
• Two
• One
• One
• Three
• Seven
• Two
• Four
• Seven
• One
• Three

I would want the following values in column E:

• One
• Two
• Three
• Seven
• Four

Is this possible? I've searched around and can't find a solution, but i'm sure you guys will know of one!

 

[SteveO59L]

Try Using the Advanced Filter, with the "Unique Vales" and "Copy to Another Location" options.


Or, you could use a Pivot Table

 

[julianhall]

Hi, thanks for your input.

The advanced filter doesn't work. Where there are multiple instances of one item, all are copied across.

The PivotTable doesn't work either, it throws an error message.

 

[Stormseed]

Advanced Filter should work. There is something you are not doing properly. Can you explain how you go about doing the Advanced Filter thing on your worksheet ?

 

[julianhall]

Advanced Filter should work. There is something you are not doing properly. Can you explain how you go about doing the Advanced Filter thing on your worksheet ?

Sure. I'm selecting the range that will have the duplicate entries (C3:C50), selecting Data > Filter > Advanced Filter. Then selecting the 'Copy to another location' radio button and entering 'E3:E10' in the 'Copy to' field. Finally, i'm checking the 'Unique records only' checkbox and clicking 'OK'.

It's copying data across, but it's not removing duplicates.

 

[jeffreybrown]

What type of data are you filtering? So you know there are only 8 duplicates? Could you post some of the data?

 

[SteveO59L]

Try only entering E3 as the destination

 

[julianhall]

What type of data are you filtering? So you know there are only 8 duplicates? Could you post some of the data?

Text strings.

I'm just doing it as a test initially, and in column C i have:

• One
• Two
• One
• One
• One
• One

Which is filtered across into column E as:

• One
• Two
• One

So it's removing some duplicates, but not all.

Try only entering E3 as the destination

Thanks, that gives me the same problem.

 

[jeffreybrown]

Try putting a header right above the first One and then select the entire range with the header.

 

[julianhall]

Try putting a header right above the first One and then select the entire range with the header.

That throws an error "The extract range has a missing or illegal file name."