Unique values in a list

Hi all

How can I get a unique list of names on to a sheet that is based on data available from another sheet in the same workbook where the names may be repeated multiple times. I've tried to use the formula below which I obtained from another site but I can't make it work. I have substituted 'List_with_duplicates' with a name range and entered it as an array formula but I can't make it work. Any help appreciated. I'm using Excel 2010.

{=IFERROR(INDEX(List_with_duplicates,SMALL(IF(FREQUENCY(MATCH(List_with_duplicates,List_with_duplicates,0),MATCH(List_with_duplicates,List_with_duplicates,0))>0,ROW(INDIRECT("1:"&ROWS(List_with_duplicates)))),ROWS($1:1))),"")}

Many thanks

John

Oh Come on..!

2 Suggestion

01.Just matter of copying all your data and past new sheet then goto DATA tab there is a option called Remove Duplicates..

02.On you are List apply conditional formatting there is called highlight unique then put filter on your list then filter based on color .. you can see unique list can copy and past new worksheet as well (values only recommeneded)

Hope this will help you

Thanks for your help guys, I should have mentioned that the list that I am trying to get the unique values from is forever being added to so what I need is a dynamic way of updating my unique list whenever the main list changes. Can I use a name range that includes the whole column?

Thanks again

Thanks for that I'll give it a try and come back to you later. I'm impressed with the fast response. Excellent forum.

All the best.

Thanks Icestationzebra, I have another question what does COUNTIF(Sheet2!$A$1:A1 refer to? I might be a bit slow with this one.

I've tried the evaluate formula and I'm getting an error on every line which returns a '-' on the sheet. This is the formula I'm using

=IFERROR(INDEX(NAME,MATCH(0,INDEX(COUNTIF(Rental_Summary!$A$7:A7,NAME),0),0)),"-")

NAME is the name range and Rental_Summary is the page I want to put the unique names from the name range. I want to start in A7. Can you see if I'm doing something wrong?

Thanks again, the name range is column C in ED Rent and I'm trying to get the unique names into Rental Summary at A7. Here's the file to look at.

yes it worked. Thanks for your patience. Great forum.

@ myobreportguru

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks for reminding me. I will take care of this in the future.