Extract unique values from a range in excel

  • Hi to everyone, I need your help. I have two columns like this:


    Col A (table) Col B (name)
    1 Jack
    1 Jack
    1 Jack
    1 Jack
    2
    2
    2
    2 Ben
    3 Ben
    3
    3
    3
    ... ...


    I need to create a new list in which I resume the data like this:


    Col A (table) Col B (name)
    1 Jack
    2 Ben
    3 Ben
    ... ...


    So I need to delete duplicates and write 2 new columns in which there are the name of the person and his table. Is it possible? Maybe with a macro?


    Thanks for the help

  • Re: Extract unique values from a range in excel


    C1=SUM(IF(FREQUENCY(IF($A$1:$A$32&$B$1:$B$32<>"",MATCH("~"&$A$1:$A$32&$B$1:$B$32,$A$1:$A$32&$B$1:$B$32&"",0)),ROW($A$1:$A$32)-ROW($A$1)+1),1)) with C+S+E


    and in D1=IF(ROWS(E$8:E8)>$C$1,"",INDEX($A$1:$A$32&"-"&$B$1:$B$32,SMALL(IF(FREQUENCY(IF($A$1:$A$32&$B$1:$B$32<>"",MATCH("~"&$A$1:$A$32&$B$1:$B$32,$A$1:$A$32&$B$1:$B$32&"",0)),ROW($A$1:$A$32)-ROW($A$1)+1),ROW($A$1:$A$32)-ROW($A$1)+1),ROWS(E$8:E8)))) with C+S+E and fill down

  • Re: Extract unique values from a range in excel


    or

    or


  • Re: Extract unique values from a range in excel


    Great! The VBA code was perfect, and works great! :wowee: Thanks a lot! :)


    I have one more question. I know thats it's quite difficult but I want to try. Now I have this situation.


    Col A (table) Col B (name)
    1 Jack
    1 Jack
    1 Jack
    1 Jack
    2 Jack
    2
    2
    2 Ben
    3 Ben
    3 Ben
    3
    3
    ... ...


    Col D (table) Col E (name)
    1 Jack
    2 Jack
    2 Ben
    3 Ben
    ... ...


    Is there a way to create even a third column where there are the number of times that the Name appears in each group of table (every table has 4 seats), I want to obtain something like this


    Col D (table) Col E (name) Col F (Seats)
    1 Jack 4
    2 Jack 1
    2 Ben 1
    3 Ben 2
    ... ... ...

  • Re: Extract unique values from a range in excel


    Try

  • Re: Extract unique values from a range in excel


    Hi ginobuonpanino,
    You just need to add another dimension to the array like..

  • Re: Extract unique values from a range in excel


    if you need to sort on table number you can add the bubble sort


  • Re: Extract unique values from a range in excel


    OK, Pike's code, seems to work better, just a 2 last question. As you can see, in the new table the title of the third row is 1, can I write seats instead of 1? and why it is considered the last table (280), even if there is nothing on his cells? I give you a screen of the new table generated by the code of Pike


    Thanks again to everyone for the help.. :)

  • Re: Extract unique values from a range in excel


    no screen shoots example workbook


    you can take out the sort stuff .. it sorts by Table number then Name then seats

  • Re: Extract unique values from a range in excel


    try.. this one for speed

  • Re: Extract unique values from a range in excel


    Ok, I made some changes, because I don't need to sort results, that's what I'm using now:



    And that's what I found with this code. As You can see It works perfectly, but I can't understand why it continues to write even a blank cell at table 280, I check this cells and they are the same of the others. Every cells in A column contains this formula, in A3, for example, I have put =IF(Name!C5<>0;Name!C5;""), to avoid zero value, I don't if it's because of this formula.



    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Table

    [/td]


    [td]

    Seats

    [/td]


    [/tr]


    [tr]


    [td]

    Jake

    [/td]


    [td]

    1

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [tr]


    [td]

    Jake

    [/td]


    [td]

    1

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    Mark

    [/td]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    Peter

    [/td]


    [td]

    2

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    280

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [/TABLE]

  • Re: Extract unique values from a range in excel


    VBA says that myArray in Next myArray it's a not defined variable.


    Quote from pike;751479

    try.. this one for speed

  • Re: Extract unique values from a range in excel


    hi ginobuonpanino


    with the formula there is no need to look in the mycell offset (One column to right)
    Try this it look in the mycell for each value in column A

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!