Unique value list
 

Hi,

I have found the following formula which I'm trying to use to try to sort a
table of four columns into a list of unique values - but I can't get it to
work!

My data is in cols Z3 to AE100, its a mix of text and numbers. It looks
something like

A3 BB A4 DD
BB A4 CC EE
A3 A4 EE A1

I would like a list outputted that reads (in any order whatsoever)

A3
BB
A4
DD
EE
A1
CC

Is this possible?

Thanks in advance

Unique value list
 

Hi,

The following macro works but will delete values from your range, so if you
want to keep the original grid then copy it somewhere else. It'll put the
output in AG3 downwards...


Sub temp()

Dim rng As Range
Set rng = Range("Z3:AE100")

Dim i As Integer

Dim c As Range

For Each c In rng
If WorksheetFunction.CountIf(rng, c) 1 Then
c.ClearContents
Else
Range("AG3").Offset(i, 0).Value = c.Value
i = i + 1
End If
Next c

End Sub


Sam

"LiAD" wrote:

Hi,

I have found the following formula which I'm trying to use to try to sort a
table of four columns into a list of unique values - but I can't get it to
work!

My data is in cols Z3 to AE100, its a mix of text and numbers. It looks
something like

A3 BB A4 DD
BB A4 CC EE
A3 A4 EE A1

I would like a list outputted that reads (in any order whatsoever)

A3
BB
A4
DD
EE
A1
CC

Is this possible?

Thanks in advance

Unique value list
 

Hi,

Download and install the following addin -
http://download.cnet.com/Morefunc/30...-10423159.html and then use the
=uniquevalues() multi cell array formula

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LiAD" wrote in message
...
Hi,

I have found the following formula which I'm trying to use to try to sort
a
table of four columns into a list of unique values - but I can't get it to
work!

My data is in cols Z3 to AE100, its a mix of text and numbers. It looks
something like

A3 BB A4 DD
BB A4 CC EE
A3 A4 EE A1

I would like a list outputted that reads (in any order whatsoever)

A3
BB
A4
DD
EE
A1
CC

Is this possible?

Thanks in advance

Unique value list
 

On Fri, 10 Jul 2009 05:25:01 -0700, LiAD
wrote:

Hi,

I have found the following formula which I'm trying to use to try to sort a
table of four columns into a list of unique values - but I can't get it to
work!

My data is in cols Z3 to AE100, its a mix of text and numbers. It looks
something like

A3 BB A4 DD
BB A4 CC EE
A3 A4 EE A1

I would like a list outputted that reads (in any order whatsoever)

A3
BB
A4
DD
EE
A1
CC

Is this possible?

Thanks in advance

This Sub will output your data, sorted either by frequency or alphabetically,
into a desired range.

See the notes within the macro.

As written, both the source and results are "hard-coded" but can be changed
easily enough. Also, the results can be sorted either alphabetically or
numerically -- again, see the notes within the macro for instructions.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro
by name, and <RUN.

========================================
Option Explicit
Option Compare Text
Sub Uniques()
Dim rDest As Range
Dim rg As Range

'There are many ways to define the
' range to process.
Set rg = Range("Z3:AE100")

'There are also many ways to define
' the output range
Set rDest = Range("A1")

Dim cWordList As Collection
Dim str As String
Dim sRes() As Variant
Dim i As Long, J As Long
Dim c As Range

'get list of unique words
Set cWordList = New Collection

On Error Resume Next
For Each c In rg
cWordList.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0

ReDim sRes(0 To 1, 1 To cWordList.Count)
For i = 1 To cWordList.Count
sRes(0, i) = cWordList(i)
Next i

'get word count for each word
For i = 1 To UBound(sRes, 2)
sRes(1, i) = Application.WorksheetFunction.CountIf(rg, sRes(0, i))
Next i

'Reverse sorting order if you want the words alphabetically
'without respect to the counts

'Sort words alphabetically A-Z
BubbleSortX sRes, 0, True

'then sort by Count highest to lowest
BubbleSortX sRes, 1, False

For i = LBound(sRes, 2) To UBound(sRes, 2)
rDest(i, 1).Value = sRes(0, i)
rDest(i, 2).Value = sRes(1, i)
Next i

End Sub
'--------------------------------------------------------------
Private Sub BubbleSortX(TempArray As Variant, d As Long, _
bSortDirection As Boolean)
'bSortDirection = True means sort ascending
'bSortDirection = False means sort descending
Dim Temp1 As Variant, Temp2
Dim i As Long
Dim NoExchanges As Boolean
Dim Exchange As Boolean

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 1 To UBound(TempArray, 2) - 1

' If the element is greater/less than the element
' following it, exchange the two elements.

Exchange = TempArray(d, i) < TempArray(d, i + 1)
If bSortDirection = True Then Exchange = _
TempArray(d, i) TempArray(d, i + 1)
If Exchange Then
NoExchanges = False
Temp1 = TempArray(0, i)
Temp2 = TempArray(1, i)
TempArray(0, i) = TempArray(0, i + 1)
TempArray(1, i) = TempArray(1, i + 1)
TempArray(0, i + 1) = Temp1
TempArray(1, i + 1) = Temp2
End If
Next i
Loop While Not (NoExchanges)
End Sub
========================================
--ron