Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
I have a range of cell entries,
Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowN um, _ 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
John Walkenbach shows how to loop through a range to get the unique values he
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. Bob wrote: I have a range of cell entries, Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowN um, _ 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
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. "Bob" wrote: I have a range of cell entries, Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowN um, _ 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
Joel,
Thanks for your suggestion, but I really need to perform the task in VBA using the range: Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowN um, _ Sheet1RespCodeColPointer) & ":" & Cells(Sheet1LastRowNum, _ Sheet1RespCodeColPointer)) Thanks again, Bob "Joel" wrote: 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. "Bob" wrote: I have a range of cell entries, Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowN um, _ 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
Bernd,
Thanks for the URL! I will take a look at your UDF to see if it meets my needs. Thanks again, Bob "Bernd P" wrote: Hello, I suggest to use my UDF lfreq: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding unique entries in a column
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 "Dave Peterson" wrote: John Walkenbach shows how to loop through a range to get the unique values he 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. Bob wrote: I have a range of cell entries, Worksheets(Sheet1Name).Range(Cells(Sheet1FirstRowN um, _ 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding unique entries among two columns of alphanumeric data | Excel Worksheet Functions | |||
Finding Unique Entries Among Two Columns | Excel Programming | |||
Finding Unique Entries Among Two Columns | Excel Programming | |||
Finding Unique Entries Among Two Columns | Excel Programming | |||
Restrict a column to allow only unique entries? | Excel Programming |