Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Unique numbers in 3 columns (Excel xp)

    Hi!

    I have a set of 50 values 1 to 100 in col A, B and C. I need to made COL D, list the unique values that are in EITHER cols A, B or C. There will be about 80 numbers that are in either of the 3 columns. Is there a function that will do this in excel? Thank you for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Unique numbers in 3 columns (Excel xp)

    Try this UDF. It is an array formula confirm with ctrl-shift-enter:
    You must select the range in D where you want the values (the entire range, eg D180) if you go too much you will get NAs for those.
    Then enter:
    <pre>=UniqueList(A1:C50, True)</pre>

    or just:
    <pre>=UniqueList(A1:C50)</pre>

    for a unique list sorted ascending

    or
    <pre>=UniqueList(A1:C9, FALSE)</pre>

    for descending
    confirm with ctrl-shift-enter

    You could modify it to be a subroutine to spit out the values without using a formula, if desired.

    It uses a trick I found at John Walkenbach's site and he credits a tip J.G. Hussey, published in "Visual Basic Programmer's Journal" to get a unique list by putting it into the key of a collection (which must be unique).

    Steve

    <pre>Function UniqueList(rng As Range, Optional bAscending As Boolean = True)
    Dim vTemp
    Dim vSwap
    Dim vArray()
    Dim NoDupes As New Collection
    Dim i As Long
    Dim j As Long
    Dim x As Long
    Dim y As Long
    Dim lCount As Long
    vTemp = rng.Value

    On Error Resume Next
    For j = 1 To UBound(vTemp, 2)
    For i = 1 To UBound(vTemp, 1)
    NoDupes.Add vTemp(i, j), CStr(vTemp(i, j))
    Next
    Next
    On Error GoTo 0

    lCount = NoDupes.Count
    ReDim vArray(1 To lCount)
    For i = 1 To lCount
    vArray(i) = NoDupes(i)
    Next

    For i = 1 To lCount - 1
    For j = i + 1 To lCount
    x = IIf(bAscending, i, j)
    y = IIf(bAscending, j, i)

    If vArray(x) > vArray(y) Then
    vSwap = vArray(y)
    vArray(y) = vArray(x)
    vArray(x) = vSwap
    End If
    Next j
    Next i
    UniqueList = Application.Transpose(vArray)
    End Function</pre>


  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique numbers in 3 columns (Excel xp)

    Thank you for the help. I've tried it, attached. Should the range in the arrage be absolute or relative. Thank you for the help. (there will be 84 unique values, but i need to list them out in COL D if that's possible).

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Unique numbers in 3 columns (Excel xp)

    You didn't follow all the instructions:
    <hr>You must select the range in D where you want the values (the entire range, eg D180) if you go too much you will get NAs for those.
    <hr>

    FYI you only have 82 values not 84, you do not have a "19" or "82" in your list.
    Select D283
    Go into edit mode (<F2>)
    Ctrl-shift-enter
    Now the range will be filled with the unique entries.

    The result is an array of 82 values. You put the array in different cells, and since a cell could only hold one value, each one showed only the first one.

    FYI
    =COUNTA(UniqueList($A$2:$C$51, TRUE))
    [Note This is NOT an array formula]
    will give you the count of the unique items. You can also use other stat functions (average, stdev, etc). Min and max are available but are equal to the min/max of the entire dataset.

    If you do not want to use an array formula, you could use in D2:
    =INDEX(UniqueList($A$2:$C$51, TRUE),ROW()-1)
    and copy it down d383. If you copy it to D84 you will get an error since there are only 82 values and you tried an index for the 83rd.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •