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:

    [/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:

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

    Sub snb2()
       For Each sh In Sheets(Array("june", "july"))
            For Each cl In sh.Columns(1).SpecialCells(2)
                If InStr(c01, cl.Value) = 0 Then c01 = c01 & "|" & cl.Value
        Sheets("Animals").Cells(1, 12).Resize(UBound(Split(c01, "|"))) = Application.Transpose(Split(Mid(c01, 2), "|"))
    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

Participate now!

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