return a list of names, from a large list of repeated names.

[The Fru Fru]

I have started a book club and made an excel sheet for all of the details
including the books owner. If a member has put on multiple books, their name
appears multiple times. What i am trying to do is generate a list with
everyones name on it only once, from the long list of repeated names. This
short list will then be linked to the drop down box, so that a new member to
the book club only has to enter their name once, then it appears in the drop
down list.

 

[Pete_UK]

You can use Advanced Filter to do this very quickly. Debra Dalgleish
shows how here:

http://www.contextures.com/xladvfilter01.html#FilterUR

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

You can also create a pivot table for the names. This will list all the
unique names

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Max]

An alternative formulas play, which gives full dynamic on-the-fly convenience

Assume source names will be in Sheet1, in A2 down
In another sheet,
Put in A2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet1!A2)>1,"",ROW()))

Put in B2:
=INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1)))
Copy A2:B2 down to cover the max expected extent of source data, say, down
to B100?

Click Insert > Name > Define
Name: MyR
Refers to:
=OFFSET(Sheet2!$B$2,,,SUMPRODUCT(--(NOT(ISERROR(Sheet2!$B$2:$B$100)))))

Then just go back to Sheet1,
select the source range A2:A100, apply Data>Validation:
Allow: List, Source: =MyR
Click OK (Ignore the error prompt)

Test it out .. Start entering names in A2 down. As you enter, the DV will
start to populate its uniques list. If you already have names in A2 down, you
won't get the closing DV error prompt, and the DV will immediately display
the uniques list.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[The Fru Fru]

Thanks, this does work as i have a few pivot tables in the workbook already,
but i need a list that can be tagged to a drop down box. Using the advanced
filter has done that.

The Fru Fru

 

[The Fru Fru]

Awesome and easy (once i read it correctly!!) Thanks.

 

[The Fru Fru]

Thanks, but i got a bit lost, not really paying attention as i'd already done
the advanced filter above and updated it by inserting the step into a macro.
would need more time and sleep before I attack this.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[The Fru Fru]

BRILLIANT! It worked great, and with a bit more thinking I could tag it
anywhere, worked perfectly first time around, no error messages. Thanks
heaps Max, oh and coffee!!

 

[Max]

Delighted to hear. Please take a moment to press the YES button in that
earlier response, won't you? Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik