Finding unique entries in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 31 Aug 2007 17:34 GMT

I have a range of cell entries,

Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowNum, _
Sheet1RespCodeColPointer) & ":" & Cells(Sheet1LastRowNum, _
Sheet1RespCodeColPointer))

in which I need to:

1) count the number of unique entries and store the result in a variable

2) copy each of the unique entries that were found to an array, and then
refer to each entry in the array in a subsequent formula

Any help in showing me how to perform these 2 steps would be greatly
appreciated. Thanks.

Reply to this Message

Bernd P - 31 Aug 2007 17:43 GMT

Hello,

I suggest to use my UDF lfreq:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

Reply to this Message

Bob - 31 Aug 2007 19:12 GMT

Bernd,

Thanks for the URL! I will take a look at your UDF to see if it meets my
needs.

Thanks again,
Bob

> Hello,
>
[quoted text clipped - 3 lines]
> Regards,
> Bernd

Reply to this Message

Dave Peterson - 31 Aug 2007 17:46 GMT

John Walkenbach shows how to loop through a range to get the unique values here:
http://j-walk.com/ss/excel/tips/tip47.htm

And once you do that, you can use yourcollection.count (nodupes.count is what
John used) as the number of unique entries.

> I have a range of cell entries,
>
[quoted text clipped - 11 lines]
> Any help in showing me how to perform these 2 steps would be greatly
> appreciated. Thanks.

Signature

Dave Peterson

Reply to this Message

Bob - 31 Aug 2007 19:14 GMT

Dave,

Thanks for the URL to John Walkenbach's solution. I will take a look at it
to see if it meets my needs.

Thanks again for all your help,
Bob

> John Walkenbach shows how to loop through a range to get the unique values here:
> http://j-walk.com/ss/excel/tips/tip47.htm
[quoted text clipped - 17 lines]
> > Any help in showing me how to perform these 2 steps would be greatly
> > appreciated. Thanks.

Reply to this Message

Joel - 31 Aug 2007 17:54 GMT

You don't need VBA to perform this task

If your numbers arre in column A starting in row 1, then put 1 in cell B1.
the first number is always unique. In cell B2 put in the following formula
=IF(COUNTIF(A$1:A1,A2)=0,1,0)
Notice the $1.
Copy this formula to other cells in column B. The values in column B witth
a 1 is the unique numbers. You can SUM column b to get the number of unique
numbers.

I can show you how to do this in VBA if you don't want to do it in a
spreadsheet formula.

> I have a range of cell entries,
>
[quoted text clipped - 11 lines]
> Any help in showing me how to perform these 2 steps would be greatly
> appreciated. Thanks.

Reply to this Message

Bob - 31 Aug 2007 19:10 GMT

Joel,

Thanks for your suggestion, but I really need to perform the task in VBA
using the range:

Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowNum, _
Sheet1RespCodeColPointer) & ":" & Cells(Sheet1LastRowNum, _
Sheet1RespCodeColPointer))

Thanks again,
Bob

> You don't need VBA to perform this task
>
[quoted text clipped - 24 lines]
> > Any help in showing me how to perform these 2 steps would be greatly
> > appreciated. Thanks.

Reply to this Message