# Thread: Unique numbers in 3 columns (Excel xp)

1. ## 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. ## 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)
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. ## 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. ## 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
•