Generate a list of unique values

Sissyfoo · Oct 6th 2005

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

Monkey
Monkey
Turnip
Dinosaur
Dinosaur
Dinosaur
Wallaby
Pirate

List B

Monkey
Turnip
Dinosaur
Wallaby
Pirate

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.

James

**Krishnakumar** · Oct 6th 2005

Re: Generate a list of unique values

Hi,

Try advanced filter.
there is an option for unique records only.

HTH

Dave Hawley · Oct 6th 2005

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

Sissyfoo · Oct 7th 2005

Re: Generate a list of unique values

Hi,

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

Code

Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
'   Accepts an array or range as input
'   If Count = True or is missing, the function returns the number
'   of unique elements
'   If Count = False, the function returns a variant array of unique
'   elements
    
    Dim Unique() As Variant ' array that holds the unique items
    Dim Element As Variant
    Dim i As Integer
    Dim FoundMatch As Boolean
        
'   If 2nd argument is missing, assign default value
    If IsMissing(Count) Then Count = True
        
'   Counter for number of unique elements
    NumUnique = 0
        
'   Loop thru the input array
    For Each Element In ArrayIn
        FoundMatch = False


'       Has item been added yet?
        For i = 1 To NumUnique
            If Element = Unique(i) Then
                FoundMatch = True
                GoTo AddItem '(Exit For-Next loop)
            End If
        Next i
       
AddItem:
'       If not in list, add the item to unique list
        If Not FoundMatch Then
            NumUnique = NumUnique + 1
            ReDim Preserve Unique(NumUnique)
            Unique(NumUnique) = Element
        End If
    
    Next Element
    
'   Assign a value to the function
    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

Display More

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.

**Krishnakumar** · Oct 7th 2005

Re: Generate a list of unique values

Hi,

See the attached file.

HTH

ujambor · Jul 2nd 2010

Re: Generate a list of unique values

Try this one:

Code

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

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

huvanile · Jul 12th 2010

Re: Generate a list of unique values

This worked for me:

Code

'returns an array of unique values within a specified range
Private Function uniqueValues(InputRange As Range)
    Dim cell As Range
    Dim tempList As Variant: tempList = ""
    For Each cell In InputRange
        If cell.Value <> "" Then
            If InStr(1, tempList, cell.Value) = 0 Then
                If tempList = "" Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
            End If
        End If
    Next cell
    uniqueValues = Split(tempList, "|")
End Function

Display More

snb · Jul 13th 2010

Re: Generate a list of unique values

or 1 of these 2 methods:

Code

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) = "#"
    Next
    sq = Split(Join(sq, "|") & "|" & Join(Filter(sn, "#", False), "|"), "|")
    Sheets("Animals").Cells(1, 10).Resize(UBound(sq) + 1) = Application.Transpose(sq)
End Sub

Code

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
        Next
    Next
    Sheets("Animals").Cells(1, 12).Resize(UBound(Split(c01, "|"))) = Application.Transpose(Split(Mid(c01, 2), "|"))
End Sub

CFABS · Aug 1st 2010

Re: Generate a list of unique values

Hello

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

=IF(A2=A1,"DUPLICATE")

To get the following results

List A and B sortedDinosaurFALSEDinosaurDUPLICATEDinosaurDUPLICATEDinosaurDUPLICATEMonkeyFALSEMonkeyDUPLICATEPirateFALSEPirateDUPLICATETurnipFALSETurnipDUPLICATEWallabyFALSEWallabyDUPLICATE

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
http://www.versiontracker.com/dyn/moreinfo/win/75157580

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

dra_red · Feb 27th 2011

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

Wigi · Feb 27th 2011

Re: Generate a list of unique values

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

Wigi

Generate a list of unique values

Participate now!

Share