Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Create Unique List From List With Duplicates

  1. #1
    Join Date
    28th November 2007
    Posts
    13

    Create Unique List From List With Duplicates

    I have a list of data which looks like this

    Column B

    Name
    ---------
    Jones, Bob
    Jones, Bob
    Jones, Bob
    Smith, Mike
    Smith, Mike
    Smith, Mike
    Calai, Dave
    Calai, Dave
    Calai, Dave
    Etc...

    What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Distinct Data List

    I'm guessing you mean unique list with only 1 occurences of each. Use AdvancedFilter to create a unique list.

  3. #3
    Join Date
    9th August 2006
    Posts
    155

    Re: Create Unqiue List From List With Duplicates

    pivot table also produce a quick list of unique values

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th November 2007
    Posts
    13

    Re: Create Unqiue List From List With Duplicates

    Thanks for the replies.
    I need to be able to do it within the sheet and have the range of the list be on a seperate sheet. I was hoping I could use a formula so that I don't have to go into VBA.

    Thank You again

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,413

    Re: Create Unqiue List From List With Duplicates

    Hi Jerry - welcome to the forum. You dont need VBA or Formulas... as Dave suggested AdvancedFilter should be able to do what you need. Hover the mouse over the word and take a look at the advanced filter link towards the bottom.

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  6. #6
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Create Unqiue List From List With Duplicates

    Here is a formula solution though for your interest (I would use Advanced Filter, however):

    Assume your list of names in B2:B10 then in C2 type the following formula:

    =B2

    Then in C3 type:

    =IF(ISNA(MATCH(0,COUNTIF($C$2:$C2,$B$2:$B$10),0)),"",INDEX($B$2:$B$10,MATCH(0,COUNTIF($C$2:$C2,$B$2:$B$10),0)))

    and confirm with Ctrl+Shift+Enter (it's an array formula). Copy this down as far as required.

    See the attached workbook for an example.

    Richard
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th November 2007
    Posts
    13

    Re: Create Unique List From List With Duplicates

    Thanks guys,
    I will mess with both the advanced filter and the formula tonight and let you know how it goes. I truely appricate everyones help. I'm glad I found this site.


    Thanks
    Jerry

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Create Unique List From List With Duplicates

    Jerry, please consider dropping your assumed experience from above average. Also, when suggestions are made, at least try them BEFORE deciding they are no good

  9. #9
    Join Date
    28th November 2007
    Posts
    13

    Re: Create Unique List From List With Duplicates

    Dave,
    My experience with excel is above average considering I mostly use it for automation using C# and VB. I don't have the experience with advanced formulas and such. I don't see where in any of my posts I said somones suggestion wasn't good. I said that I needed to do it using a formula.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Create Unique List From List With Duplicates

    You might be a master of automation using C# and VB, but apparently not the basics-intermediate uses of Excel. The Assumed Experience is for YOUR benefit mainly and lets those TRYING to help you how much detail they need to supply. Please lower it so those trying to help are NOT wasting their time.

    You did decide AdvancedFilter was no good before even trying and your reply above is testament to that.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Create Unique List From Column With Duplicates
    By gsandy in forum EXCEL HELP
    Replies: 8
    Last Post: May 28th, 2012, 17:28
  2. Replies: 3
    Last Post: January 15th, 2009, 16:11
  3. Replies: 6
    Last Post: May 14th, 2008, 11:34
  4. Unique List With No Duplicates
    By MartinKoch in forum EXCEL HELP
    Replies: 3
    Last Post: January 11th, 2007, 19:38
  5. Get unique values from list with duplicates
    By bdbmf in forum EXCEL HELP
    Replies: 1
    Last Post: March 13th, 2005, 00:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno