Announcement

Collapse
No announcement yet.

Create Unique List From List With Duplicates

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    Re: Distinct Data List

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

    Comment


    • #3
      Re: Create Unqiue List From List With Duplicates

      pivot table also produce a quick list of unique values

      Comment


      • #4
        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

        Comment


        • #5
          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

          Check out our new reputation system. Click on the "star" under the post!
          _______________________________________________

          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

          _______________________________________________

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    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.

                    Comment

                    Working...
                    X