Generate a list of unique values

  • Hello again,

    I need to generate a new list of data taken from an existing list of data and then put this list in a new worksheet... but the trouble is that this existing list (call it 'list A') has many repeating values which I do not want in the new list (call this 'list B'). Normally I'd forgo the hassle of fiddling with macros for this but I need this to be a dynamic list so that the user can update list A and then list B will automatically correct itself without the user having to do it themselves.

    For example:

    List A


    List B


    I'm sure that this is very easy to do but I just cannae think how to do it!

    Any help or ideas would be greatly appreciated. :)


  • Re: Generate a list of unique values

    Hi James

    Advanced Filter can do this.

    Select the list range (including a heading) and then go to Data>Filter>Advanded Filter and check Unique Records Only and Copy to a new location

  • Re: Generate a list of unique values


    Thanks for the replies. Unfortunately the advanced filter isn't really adequate for my needs. The list of unique values needs to be dynamic so that if the user updates list A then list B will update accordingly and spare the user the hassle of having to use entering it on both lists.

    I have been playing about with arrays and a macro function which pulls out all the unique values in an array and puts it into a brand new array which can be displayed on the new worksheet. This *almost* solves my problem but has raised a few new ones (typically). I've attached a file which gives an example of the problems I'm having.

    In worksheets 'June' and 'July' I have a list of animals (so to speak) and in the worksheet entitled 'Animals' I have compiled a list of the unique animals in Column E. This was done using the function UniqueItems (see code below).

    The problems that have arisen are as follows:

    I have used the UniqueItems() function to pull unique data from 'June' and place it in Column A in 'Animals' and done the same with 'July' in Column B. I then used UniqueItems() again on Columns A and B and put the final list of unique values in Column E. However, I understand that when using an array to transpose the data the ranges need to be of the same size (rows and columns). This creates a problem if my user only needs to add or remove an animal in either June or July because then the array in 'Animals' column E will be thrown completely out of whack and either be unable to pick up the new entry or return an entire column of errors.

    I'm beginning to think I am barking up the wrong tree by trying to use arrays for this as it seems quite likely that the sizes of the initial arrays are going to vary from one month to the next and this is going to ruin the final array.

    Does anyone have any other ideas which might help as this is really confusing the hell out of me. It's a shame because I really thought I had almost cracked it. :/

    /edit Okay, I was going to attach the file I was talking about but for some reason it has bloated up to 7.30 mb. Grr.

    //edit *sigh* Obviously having a blonde day. File attached. :)

  • Re: Generate a list of unique values

    Try this one:

    1. [/B]Function UNIQUE(InputRange As Range, ItemNo As Long) As VariantDim cl As Range, cUnique As New Collection, cValue As Variant Application.Volatile On Error Resume Next For Each cl In InputRange If cl.Formula <> "" Then cUnique.Add cl.Value, CStr(cl.Value) End If Next cl UNIQUE= "" If ItemNo = 0 Then UNIQUE= cUnique.Count Else If ItemNo <= cUnique.Count Then UNIQUE= cUnique(ItemNo) End If End If On Error GoTo 0End Function[B]


    Apply formula as:
    =UNIQUE("place here a range of cells";"place here a number")
    number 1,2,3.... number tells you the next different unique value.
    When no different values are found, next cell in a row beneath is blank.

    hope this helps anyone...

  • Re: Generate a list of unique values

    This worked for me:

  • Re: Generate a list of unique values

    or 1 of these 2 methods:

    1. Sub snb()
    2. sq = Filter([transpose(IF(countif(Offset(JUne!$A$1,,,ROW(June!A1:A200)),June!A1:A200)=1,June!A1:A200,"#"))], "#", False)
    3. sn = Filter([transpose(IF(countif(Offset(JUly!$A$1,,,ROW(July!A1:A200)),July!A1:A200)=1,July!A1:A200,"#"))], "#", False)
    4. For j = 0 To UBound(sn)
    5. If UBound(Filter(sq, sn(j))) > -1 Then sn(j) = "#"
    6. Next
    7. sq = Split(Join(sq, "|") & "|" & Join(Filter(sn, "#", False), "|"), "|")
    8. Sheets("Animals").Cells(1, 10).Resize(UBound(sq) + 1) = Application.Transpose(sq)
    9. End Sub

    1. Sub snb2()
    2. For Each sh In Sheets(Array("june", "july"))
    3. For Each cl In sh.Columns(1).SpecialCells(2)
    4. If InStr(c01, cl.Value) = 0 Then c01 = c01 & "|" & cl.Value
    5. Next
    6. Next
    7. Sheets("Animals").Cells(1, 12).Resize(UBound(Split(c01, "|"))) = Application.Transpose(Split(Mid(c01, 2), "|"))
    8. End Sub
  • Re: Generate a list of unique values


    Is this a simple case of removing the duplicates?
    Copy paste both your lists together and sort by name

    List A and B sorted (header row in A1)DinosaurDinosaurDinosaurDinosaurMonkeyMonkeyPiratePirateTurnipTurnipWallabyWallaby

    In cell B2 write the following if statement and drag the formula down


    To get the following results


    Select alll data in your spreadsheet and do an autofilter on the word Duplicate (data/filter/autofilter), delete the duplicate rows

    Hope this helps.

    Also if you are looking for a solution that can sum data across unique values try this, CFABS Agent Splitter - 1.01

    This would be for the scenario where you want to generate a grid, that just takes the unique names in your column, e.g. monkey and only inserts the data that relates to monkey into the monkey column, and only inserts data that relates to dinasour in the dinosaur column, etc.,

    I use a formula

  • Re: Generate a list of unique values

    So many ways to do the same thing...

    Here is another. A way to create a dynamic list with Excels internal functions.

    Insert a column (or use a new worksheet) and add a 'countif' function in this column. Use relative referencing so that the countif function only counts the items in the range above the actual item.

    Rows in the new column will show '1' for the first instance and '>1' for any more instances.

    You can then use a filter or a Vlookup (after some manipulation to get it how you want) to create the unique list where you want.

    No macros required.

    Cheers, Dale

  • Re: Generate a list of unique values

    There is no need to post in already very old topics. This one is closed now.




    Excel MVP 2011-2014

    For more Excel memes: visit ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --