list unique digits

stewart08 · 02-17-2009, 11:52 AM

hi excelers

I being trying to create a simple formula that only lists 3 UNIQUE digits from 5 digits (that may have repeats)

- example:

5 Colums
1,1,2,3,2 = 1,2,3,

any comments, grateful for any solutions....

**solnajeff** · 02-17-2009, 12:18 PM

HI

The following code will list all unique entries based on data in columns A to E

Sub copynodup()

Dim a As Integer, b As Integer

Let a = 1
Do While Cells(a, 1).Value <> ""

For b = 1 To 5
If Application.CountIf(Range(Cells(a, 7), Cells(a, 11)), Cells(a, b).Value) = 0 Then
Let Cells(a, Application.CountA(Range(Cells(a, 7), Cells(a, 11))) + 7) = Cells(a, b).Value
End If
Next b
Let a = a + 1
Loop

End Sub

Regards

Jeff

**Ron Coderre** · 02-17-2009, 12:22 PM

Not exactly sure what you want the result to be...but, here's a guess:

With
A1:E1 containing 3 unique digits....duplicates allowed...no blanks

This regular formula returns the 3 unique digits, in one cell, separated by commas:

F1: =TEXT(SUMPRODUCT(SMALL(INDEX(A1:E1+(MATCH(A1:E1,A1:E1,0)<>COLUMN(A1:E1))*
10^99,0),{1,2,3})*10^{2,1,0}),"0\,0\,0")

If A1:E1 contains: 1 1 2 3 2
The formula returns: 1,2,3

Is that something you can work with?

**DonkeyOte** · 02-17-2009, 12:35 PM

I know this against the rules as I am adding nothing but...

WOW

Ron, very cool indeed... I have another of yours tucked away on a similar theme but that's very neat indeed.

**Ron Coderre** · 02-17-2009, 12:47 PM

Thanks for the kind words, DonkeyOte...much appreciated.

stewart08 · 02-17-2009, 05:51 PM

thanks ron indeed you know your stuff.

I used your the formula its work, but I need it to return 3 single digits in each colum...

**Ron Coderre** · 02-17-2009, 07:51 PM

Originally Posted by stewart08

thanks ron indeed you know your stuff.

I used your the formula its work, but I need it to return 3 single digits in each colum...

OK...using the same set up of values in A1:E1

This formula returns the lowest of the 3 unique values:

F1: =SMALL(INDEX($A1:$E1+(MATCH($A1:$E1,$A1:$E1,0)<>COLUMN($A1:$E1))*10^99,0),
COLUMNS($F:F))

Copy that formula across through G1:H1 for the 2nd and 3rd smallest values.

Does that help?

**JBeaucaire** · 02-17-2009, 07:38 PM

Here's a macro that will do what you want "on-demand". Install this into a standard module.

Sub ListUniqueValues()
'Asks you to first highlight the analysis range, then select results range
'lists the unique values found in a user-defined range into a
'user-defined columnar range (changed to rows in this version)

Dim SearchRng As Range
Dim ResultRng As Range
Dim Cel As Range
Dim iRow As Long

Set SearchRng = Application.InputBox("Select search range", "Find Unique Values", Type:=8)

'Set SearchRng = Selection
Do
Set ResultRng = Application.InputBox("Select results range", "Write Unique Values", Type:=8)
Loop Until ResultRng.Rows.Count = 1

iRow = 0
For Each Cel In SearchRng
If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then
'This value doesn't already exist
iRow = iRow + 1
If iRow > ResultRng.Columns.Count Then
MsgBox "Not enough rows in result range to write all unique values", vbwarning, "Run terminated"
Exit Sub
Else
ResultRng(iRow).Value = Cel.Value
End If
End If
Next Cel

'sort result range
ResultRng.Sort ResultRng

End Sub

Now run the macro, it will ask you for a range of cells, select your range. Then it will ask where you want it, select a range of cells across one row and it will parse out the value for you.

I've tweaked the original code slightly since it was designed originally to return the values in a single column list, and you wanted them all in one row.