Hello all,

I am trying to optimize my database. I need to find the top occurring
words on my excel file.

Is there any freeware or add-ons that can perform word frequency
counting on excel files?

Thanks for any help- Lan

The solution depends upon the organization of your data. For example, if you
have a single column of single words like:

Words
cat
dog
dog
fish
cat
dog
fish
fish
cat
dog
fish
fish
dog
dog
dog
cat
cat
dog
fish
cat
fish
dog
dog
dog
dog

Then a Pivot Table can give the count of each unique enter:

Count of Words
Words Total
cat 6
dog 12
fish 7
Grand Total 25

If your words are in paragraphs within cells, then a different approach
would be taken.

--
Gary''s Student - gsnu2007e


"LAN MIND" wrote:

Hello all,

I am trying to optimize my database. I need to find the top occurring
words on my excel file.

Is there any freeware or add-ons that can perform word frequency
counting on excel files?

Thanks for any help- Lan

On Mar 8, 3:07 am, Gary''s Student
wrote:
The solution depends upon the organization of your data. For example, if you
have a single column of single words like:

Words
cat
dog
dog
fish
cat
dog
fish
fish
cat
dog
fish
fish
dog
dog
dog
cat
cat
dog
fish
cat
fish
dog
dog
dog
dog

Then a Pivot Table can give the count of each unique enter:

Count of Words
Words Total
cat 6
dog 12
fish 7
Grand Total 25

If your words are in paragraphs within cells, then a different approach
would be taken.

--
Gary''s Student - gsnu2007e


Thanks Gary. Yes my words are in paragraphs, what's the solution
then?

On Fri, 7 Mar 2008 22:56:28 -0800 (PST), LAN MIND wrote:

Hello all,

I am trying to optimize my database. I need to find the top occurring
words on my excel file.

Is there any freeware or add-ons that can perform word frequency
counting on excel files?

Thanks for any help- Lan

Here's a UDF that should get you started. There may be more efficient methods,
but I was using some "new to me" techniques here.

It returns a two-dimensional array consisting of the unique words; and the
count of each of those unique words.

By the way, a "word" is defined as a collection of word characters (bounded by
a non-word character or the beginning or end of the line). A word character is
defined as being in the class of the alphabet (A-Za-z), a digit (0-9) or the
underscore (_). If this definition of "word" gives unwanted results, it can be
changed.

To enter this, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

THEN: Select Tools/References and set a reference to Microsoft VBScript
Regular Expressions 5.5

There are several ways to display the results.

Assuming your "data" is in A1:A3, enter a formula into some cell:

First word
D1: =INDEX(uniquecount($A$1:$A$3),1,ROWS($1:1))

Count of first word
E1: =INDEX(uniquecount($A$1:$A$3),2,ROWS($1:1))

Then select D1:E1 and fill down as far as required. If you go to far, you'll
see #REF errors.

This might be better for you sorted, but I don't have time to do that right
now.

Once you have the results, you can copy/paste-special Values to some other area
of your worksheet, and then sort on the values.

If this is going to be used frequently, a sort routine can be incorporated.

===============================================
Option Explicit
Function UniqueCount(rg As Range) As Variant
'Requires reference to Microsoft VBScript Regular Expressions 5.5
'Returns a two dimensional array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range
Dim re As RegExp
Dim mc As MatchCollection, m As Match

'Put all words into a single string
For Each c In rg
Str = Str & c.Value & " "
Next c

'get list of unique words
Set re = New RegExp
re.Global = True
re.Pattern = "\b\w+\b"
Set cWordList = New Collection
On Error Resume Next
'Add method with index=word will give error on duplicates
Set mc = re.Execute(Str)
For Each m In mc
cWordList.Add m.Value, m.Value
Next m
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
re.Global = True
re.IgnoreCase = True
For i = 1 To UBound(sRes, 2)
re.Pattern = "\b" & sRes(0, i) & "\b"
Set mc = re.Execute(Str)
sRes(1, i) = mc.Count
Next i
Set re = Nothing

UniqueCount = sRes
End Function
===============================
--ron

On Sat, 08 Mar 2008 18:51:21 -0500, Ron Rosenfeld
wrote:

On Fri, 7 Mar 2008 22:56:28 -0800 (PST), LAN MIND wrote:

Hello all,

I am trying to optimize my database. I need to find the top occurring
words on my excel file.

Is there any freeware or add-ons that can perform word frequency
counting on excel files?

Thanks for any help- Lan

Here's a UDF that should get you started. There may be more efficient methods,
but I was using some "new to me" techniques here.

It returns a two-dimensional array consisting of the unique words; and the
count of each of those unique words.

By the way, a "word" is defined as a collection of word characters (bounded by
a non-word character or the beginning or end of the line). A word character is
defined as being in the class of the alphabet (A-Za-z), a digit (0-9) or the
underscore (_). If this definition of "word" gives unwanted results, it can be
changed.

To enter this, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

THEN: Select Tools/References and set a reference to Microsoft VBScript
Regular Expressions 5.5

There are several ways to display the results.

Assuming your "data" is in A1:A3, enter a formula into some cell:

First word
D1: =INDEX(uniquecount($A$1:$A$3),1,ROWS($1:1))

Count of first word
E1: =INDEX(uniquecount($A$1:$A$3),2,ROWS($1:1))

Then select D1:E1 and fill down as far as required. If you go to far, you'll
see #REF errors.

This might be better for you sorted, but I don't have time to do that right
now.

Once you have the results, you can copy/paste-special Values to some other area
of your worksheet, and then sort on the values.

If this is going to be used frequently, a sort routine can be incorporated.

===============================================
Option Explicit
Function UniqueCount(rg As Range) As Variant
'Requires reference to Microsoft VBScript Regular Expressions 5.5
'Returns a two dimensional array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range
Dim re As RegExp
Dim mc As MatchCollection, m As Match

'Put all words into a single string
For Each c In rg
Str = Str & c.Value & " "
Next c

'get list of unique words
Set re = New RegExp
re.Global = True
re.Pattern = "\b\w+\b"
Set cWordList = New Collection
On Error Resume Next
'Add method with index=word will give error on duplicates
Set mc = re.Execute(Str)
For Each m In mc
cWordList.Add m.Value, m.Value
Next m
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
re.Global = True
re.IgnoreCase = True
For i = 1 To UBound(sRes, 2)
re.Pattern = "\b" & sRes(0, i) & "\b"
Set mc = re.Execute(Str)
sRes(1, i) = mc.Count
Next i
Set re = Nothing

UniqueCount = sRes
End Function
===============================
--ron

With a little more fooling around, I modified the above to include words with
apostrophe's; and also did a double sort so the most common words would at the
top; and the subsort would be alphabetical.

If you don't want the results sorted, just comment out one or both of the two
sorting lines below.

=========================================
Option Explicit
Option Compare Text
Function UniqueCount(rg As Range) As Variant
'Requires reference to Microsoft VBScript Regular Expressions 5.5
'Returns a two dimensional array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range
Dim re As RegExp
Dim mc As MatchCollection, m As Match

'Put all words into a single string
For Each c In rg
Str = Str & c.Value & " "
Next c

'get list of unique words
Set re = New RegExp
re.Global = True
re.Pattern = "\b[\w']+\b"
Set cWordList = New Collection
On Error Resume Next
'Add method with index=word will give error on duplicates
Set mc = re.Execute(Str)
For Each m In mc
cWordList.Add m.Value, m.Value
Next m
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
re.Global = True
re.IgnoreCase = True
For i = 1 To UBound(sRes, 2)
re.Pattern = "\b" & sRes(0, i) & "\b"
Set mc = re.Execute(Str)
sRes(1, i) = mc.Count
Next i
Set re = Nothing

'you can comment out one or both of the sort lines
' depending on your requirements

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

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

UniqueCount = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSort(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 = IIf(TempArray(d, i) < TempArray(d, i + 1), True, False)
If bSortDirection = True Then Exchange = Not Exchange
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

On Sat, 08 Mar 2008 22:17:50 -0500, Ron Rosenfeld
wrote:

Private Sub BubbleSort(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 = IIf(TempArray(d, i) < TempArray(d, i + 1), True, False)
If bSortDirection = True Then Exchange = Not Exchange
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

It's getting late. Small change in the sort part:

=====================
Option Explicit
Option Compare Text
Function UniqueCount(rg As Range) As Variant
'Requires reference to Microsoft VBScript Regular Expressions 5.5
'Returns a two dimensional array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range
Dim re As RegExp
Dim mc As MatchCollection, m As Match

'Put all words into a single string
For Each c In rg
Str = Str & c.Value & " "
Next c

'get list of unique words
Set re = New RegExp
re.Global = True
re.Pattern = "\b[\w']+\b"
Set cWordList = New Collection
On Error Resume Next
'Add method with index=word will give error on duplicates
Set mc = re.Execute(Str)
For Each m In mc
cWordList.Add m.Value, m.Value
Next m
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
re.Global = True
re.IgnoreCase = True
For i = 1 To UBound(sRes, 2)
re.Pattern = "\b" & sRes(0, i) & "\b"
Set mc = re.Execute(Str)
sRes(1, i) = mc.Count
Next i
Set re = Nothing

'you can comment out one or both of the sort lines
' depending on your requirements

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

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

UniqueCount = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSort(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 = Not Exchange
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

On Sat, 08 Mar 2008 22:25:36 -0500, Ron Rosenfeld
wrote:

It's getting late. Small change in the sort part:

And now that I've had some sleep, I see that neither that change nor the
previous was what I really wanted (they'll both work, but below should be more
efficient):

===========================
Option Explicit
Option Compare Text
Function UniqueCount(rg As Range) As Variant
'Requires reference to Microsoft VBScript Regular Expressions 5.5
'Returns a two dimensional array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range
Dim re As RegExp
Dim mc As MatchCollection, m As Match

'Put all words into a single string
For Each c In rg
Str = Str & c.Value & " "
Next c

'get list of unique words
Set re = New RegExp
re.Global = True
re.Pattern = "\b[\w']+\b"
Set cWordList = New Collection
On Error Resume Next
'Add method with index=word will give error on duplicates
Set mc = re.Execute(Str)
For Each m In mc
cWordList.Add m.Value, m.Value
Next m
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
re.Global = True
re.IgnoreCase = True
For i = 1 To UBound(sRes, 2)
re.Pattern = "\b" & sRes(0, i) & "\b"
Set mc = re.Execute(Str)
sRes(1, i) = mc.Count
Next i
Set re = Nothing

'you can comment out one or both of the sort lines
' depending on your requirements

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

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

UniqueCount = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSort(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

On Sat, 08 Mar 2008 18:51:21 -0500, Ron Rosenfeld
wrote:

Assuming your "data" is in A1:A3, enter a formula into some cell:

First word
D1: =INDEX(uniquecount($A$1:$A$3),1,ROWS($1:1))

Count of first word
E1: =INDEX(uniquecount($A$1:$A$3),2,ROWS($1:1))

I've been doing some timing test, and the above method is very inefficient with
large numbers of words.

The problem is that, in setting up formulas as above, the UDF will be
recalculated for each formula.

The UDF itself takes a while. The part that takes the longest is getting the
count on each unique word; and the sorting routines also take a while.

I examined a web page that had 21,011 words of which 2,526 were unique.

The total time to run the UDF was about 27 seconds.
Getting the count took 14 seconds; the alpha sort took 8.625 seconds, and the
numeric sort 2.8 seconds.

Given that, it will be much more efficient to enter this function as an array,
of the appropriate size and shape. With that sort of entry, the UDF only needs
to be calculated once.

For example, if you wanted to know the ten most frequent words, you could

Select a 2 column x 10 row array
In the upper left cell, enter the formula:

=transpose(uniquecount(rng))

where "rng" is the cells containing the text.

Hold down <ctrl<shift while you hit <enter.

The formula will fill the entire area, and will only need to calculate once.
--ron

On Mar 9, 6:08 am, Ron Rosenfeld wrote:
On Sat, 08 Mar 2008 18:51:21 -0500, Ron Rosenfeld
wrote:

Assuming your "data" is in A1:A3, enter a formula into some cell:

First word
D1: =INDEX(uniquecount($A$1:$A$3),1,ROWS($1:1))

Count of first word
E1: =INDEX(uniquecount($A$1:$A$3),2,ROWS($1:1))

I've been doing some timing test, and the above method is very inefficient with
large numbers of words.

The problem is that, in setting up formulas as above, the UDF will be
recalculated for each formula.

The UDF itself takes a while. The part that takes the longest is getting the
count on each unique word; and the sorting routines also take a while.

I examined a web page that had 21,011 words of which 2,526 were unique.

The total time to run the UDF was about 27 seconds.
Getting the count took 14 seconds; the alpha sort took 8.625 seconds, and the
numeric sort 2.8 seconds.

Given that, it will be much more efficient to enter this function as an array,
of the appropriate size and shape. With that sort of entry, the UDF only needs
to be calculated once.

For example, if you wanted to know the ten most frequent words, you could

Select a 2 column x 10 row array
In the upper left cell, enter the formula:

=transpose(uniquecount(rng))

where "rng" is the cells containing the text.

Hold down <ctrl<shift while you hit <enter.

The formula will fill the entire area, and will only need to calculate once.
--ron

Wow Ron thanks for all that code. I haven't had anytime lately to go
over much of it but I will tonight. Truthfully I'm lost on almost all
of what you've shown as I'm not a programmer and I have very little
experience with excel. A slow count doesn't bother me much as this
will be done locally for in house results.

Thanks : )

On Mon, 10 Mar 2008 18:32:08 -0700 (PDT), Lan Mind wrote:

Wow Ron thanks for all that code. I haven't had anytime lately to go
over much of it but I will tonight. Truthfully I'm lost on almost all
of what you've shown as I'm not a programmer and I have very little
experience with excel. A slow count doesn't bother me much as this
will be done locally for in house results.

I would **strongly** recommend you not use the INDEX(...) formula if you have a
lot of data. The web page I used in my testing would have taken about 20 hours
to complete the calculations!

The array formula approach would take about 30 seconds!


If the array formula approach is not suitable, it would be possible to change
the function to a macro that could write the data out into the workbook.
--ron

Ok I have:

1Opened the VBEditor.

2Selected Insert/Module and pasted this**** (at the bottom of this
post) in the window that opened.

3Selected Tools/References and set a reference to Microsoft VBScript
Regular Expressions 5.5

4Then I went back to my excel sheet and manually highlighted a 2
column x 10 row "box" by clicking on the upper left cell and dragging
and highlighting said "box" (20 cells)

5In the upper left cell I entered:
=transpose(uniquecount(rng))

6Held down <ctrl<shift while you hit <enter

and nothing happens (as I said I am completely a newb on all of this).

So here are some questions:

After step 3 was I supposed to do something besides going straight
back to my excel sheet? Save the whole VBeditor and somehow import it?
Close the VBeditor?

Was I correct to " manually highlighted a 2 column x 10 row "box" "?

Thanks again Ron I appreciate the help. Oh BTW my excel sheet has
10,000 rows and 10 columns, very text heavy.

__________________________________________________ __

****
Option Explicit
Option Compare Text
Function UniqueCount(rg As Range) As Variant
'Requires reference to Microsoft VBScript Regular Expressions 5.5
'Returns a two dimensional array of unique words and count
Dim cWordList As Collection
Dim Str As String
Dim sRes() As Variant
Dim i As Long, j As Long
Dim c As Range
Dim re As RegExp
Dim mc As MatchCollection, m As Match

'Put all words into a single string
For Each c In rg
Str = Str & c.Value & " "
Next c

'get list of unique words
Set re = New RegExp
re.Global = True
re.Pattern = "\b[\w']+\b"
Set cWordList = New Collection
On Error Resume Next
'Add method with index=word will give error on duplicates
Set mc = re.Execute(Str)
For Each m In mc
cWordList.Add m.Value, m.Value
Next m
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
re.Global = True
re.IgnoreCase = True
For i = 1 To UBound(sRes, 2)
re.Pattern = "\b" & sRes(0, i) & "\b"
Set mc = re.Execute(Str)
sRes(1, i) = mc.Count
Next i
Set re = Nothing

'you can comment out one or both of the sort lines
' depending on your requirements

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

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

UniqueCount = sRes
End Function
'--------------------------------------------------------------
Private Sub BubbleSort(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

On Mon, 10 Mar 2008 22:20:30 -0700 (PDT), Lan Mind wrote:

----------------------------------
5In the upper left cell I entered:
=transpose(uniquecount(rng))
------------------------------------


Does "rng" refer to the range containing your text to be analyzed?

For example, if your text is in A1:A10, then you can either NAME
(Insert/Name/Define) rng as refers to: A1:A10, or substitute that in the
formula.
--ron

Aha! I believe I have it functioning Ron. Thanks for helping an Excel
peon!

Did you write all this code yourself? Such a help, sure beats paying
for this: http://www.download3000.com/download_18482.html

If I wanted to do 3 separate rows like columns like A, J and D what is
the syntax? Would I put commas between like so?:

=transpose(uniquecount(A1:A9512,D1:D9512,J1:J9512) )

On Tue, 11 Mar 2008 00:58:57 -0700 (PDT), Lan Mind wrote:

Aha! I believe I have it functioning Ron. Thanks for helping an Excel
peon!

Did you write all this code yourself? Such a help, sure beats paying
for this: http://www.download3000.com/download_18482.html

You're welcome. Glad to help. Thanks for the feedback.

I learn by doing this sort of thing, so it's fun to write it.

However, it can be made to run much more quickly, in part by using native VBA
functions instead of the Regular Expression object; and also by doing the sort
differently.

So since it isn't "for sale" ...

But if the speed is an issue for you, let me know and I can certainly tweak
parts of it.
--ron

On Tue, 11 Mar 2008 01:12:33 -0700 (PDT), Lan Mind wrote:

If I wanted to do 3 separate rows like columns like A, J and D what is
the syntax? Would I put commas between like so?:

=transpose(uniquecount(A1:A9512,D1:D9512,J1:J9512 ))


You're very close. You need to also enclose the entire range within a pair of
parentheses:

=transpose(uniquecount((A1:A9512,D1:D9512,J1:J9512 )))

or you should be able to NAME the range, and refer to it by the Name.
--ron

But if the speed is an issue for you, let me know and I can certainly tweak
parts of it.
--ron

No the speed is fine. Thanks again for all the help see you around!