Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances in a range.
I am using Excel 2007 and windows 7
I have a range of cells C5:F32 which is full of numbers. I am running code which steps me through every cell starting with the first column C5 to C32, moving to the next column and doing the same and ending with the last Number in column F. When it stops at each cell I need to determine how many times the same number has appeared in that range in rows prior to the row of the cell I am currently in. That determines if the number of the cell I am currently in is the 1st 2nd, 3rd or 4th time that number has appeared. The same number is never repeated in the same row so it only has to count the number of times it appears in previous rows and then obviously add 1 to determine the 1st 2nd etc. Numbers appearing in the first row will always be the first time they appear but the first time a number appears could also be in any other row.The result of the number of times it has occured is posted in cell U1 Can anyone help with the code I need. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances in a range.
On Friday, April 20, 2012 12:55:28 PM UTC-5, Brian wrote:
I am using Excel 2007 and windows 7 I have a range of cells C5:F32 which is full of numbers. I am running code which steps me through every cell starting with the first column C5 to C32, moving to the next column and doing the same and ending with the last Number in column F. When it stops at each cell I need to determine how many times the same number has appeared in that range in rows prior to the row of the cell I am currently in. That determines if the number of the cell I am currently in is the 1st 2nd, 3rd or 4th time that number has appeared. The same number is never repeated in the same row so it only has to count the number of times it appears in previous rows and then obviously add 1 to determine the 1st 2nd etc. Numbers appearing in the first row will always be the first time they appear but the first time a number appears could also be in any other row.The result of the number of times it has occured is posted in cell U1 Can anyone help with the code I need. Hard to visualize so send file to dguillett1 @gmail.com with this msg and examples |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances in a range.
On 22/04/2012 2:05 AM, Don Guillett wrote:
On Friday, April 20, 2012 12:55:28 PM UTC-5, Brian wrote: I am using Excel 2007 and windows 7 I have a range of cells C5:F32 which is full of numbers. I am running code which steps me through every cell starting with the first column C5 to C32, moving to the next column and doing the same and ending with the last Number in column F. When it stops at each cell I need to determine how many times the same number has appeared in that range in rows prior to the row of the cell I am currently in. That determines if the number of the cell I am currently in is the 1st 2nd, 3rd or 4th time that number has appeared. The same number is never repeated in the same row so it only has to count the number of times it appears in previous rows and then obviously add 1 to determine the 1st 2nd etc. Numbers appearing in the first row will always be the first time they appear but the first time a number appears could also be in any other row.The result of the number of times it has occured is posted in cell U1 Can anyone help with the code I need. Hard to visualize so send file to dguillett1 @gmail.com with this msg and examples Question: What if there is multiple instances of differing numerals, eg if 5 rows have the number 6 and 5 rows have the number 8, how then will you expect to translate the count outcome of both sets of numbers into a single cell output at U1. Also Is there any specific pattern to the size of the numbers, be it variable or preset, by that I mean if you are looking for specific multiple instances of certain numbers in each row. HTH Mick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances in a range.
Question:
What if there is multiple instances of differing numerals, eg if 5 rows have the number 6 and 5 rows have the number 8, how then will you expect to translate the count outcome of both sets of numbers into a single cell output at U1. Also Is there any specific pattern to the size of the numbers, be it variable or preset, by that I mean if you are looking for specific multiple instances of certain numbers in each row. HTH Mick OK With my question relating to if the numbers you are searching for are preset, I had a play and came up with a quick and dirty solution that highlights counts of multiple preset numbers. A B C D F G H I 1 1 2 3 4 5 6 7 8 2 1 1 1 2 1 1 4 0 3 4 The above is the result of this: A B C D E F G H 10 1 3 5 7 9 11 13 15 11 2 4 6 8 10 12 14 16 12 4 21 22 23 24 25 26 7 13 14 30 31 32 33 7 36 37 14 50 51 52 53 54 55 56 57 15 90 91 92 93 94 95 96 97 THE CODE: Sub GetNumbers() Dim MyRng As Range Dim a As Range Dim n As Range Set MyRng = Range("A10:J15") Set a = [A2] For Each n In MyRng Select Case n Case 1 a = Application.CountIf(MyRng, 1) Case 2 a.Offset(0, 1) = Application.CountIf(MyRng, 2) Case 3 a.Offset(0, 2) = Application.CountIf(MyRng, 3) Case 4 a.Offset(0, 3) = Application.CountIf(MyRng, 4) Case 5 a.Offset(0, 4) = Application.CountIf(MyRng, 5) Case 6 a.Offset(0, 5) = Application.CountIf(MyRng, 6) Case 7 a.Offset(0, 6) = Application.CountIf(MyRng, 7) Case 8 a.Offset(0, 7) = Application.CountIf(MyRng, 8) Case 9 a.Offset(0, 8) = Application.CountIf(MyRng, 9) Case 10 a.Offset(0, 9) = Application.CountIf(MyRng, 10) End Select Next End Sub This is tested and worked well, although the overall layout of the code could possible use a "Guru's Touch". HTH Mick. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances in a range.
On Sunday, April 22, 2012 4:54:31 AM UTC+2, Vacuum Sealed wrote:
On 22/04/2012 2:05 AM, Don Guillett wrote: On Friday, April 20, 2012 12:55:28 PM UTC-5, Brian wrote: I am using Excel 2007 and windows 7 I have a range of cells C5:F32 which is full of numbers. I am running code which steps me through every cell starting with the first column C5 to C32, moving to the next column and doing the same and ending with the last Number in column F. When it stops at each cell I need to determine how many times the same number has appeared in that range in rows prior to the row of the cell I am currently in. That determines if the number of the cell I am currently in is the 1st 2nd, 3rd or 4th time that number has appeared. The same number is never repeated in the same row so it only has to count the number of times it appears in previous rows and then obviously add 1 to determine the 1st 2nd etc.. Numbers appearing in the first row will always be the first time they appear but the first time a number appears could also be in any other row.The result of the number of times it has occured is posted in cell U1 Can anyone help with the code I need. Hard to visualize so send file to dguillett1 @gmail.com with this msg and examples Question: What if there is multiple instances of differing numerals, eg if 5 rows have the number 6 and 5 rows have the number 8, how then will you expect to translate the count outcome of both sets of numbers into a single cell output at U1. Also Is there any specific pattern to the size of the numbers, be it variable or preset, by that I mean if you are looking for specific multiple instances of certain numbers in each row. HTH Mick As usual maybe more of an explanation is needed. This is being used for a competition. The heading of each column is a range number and the content of the cells is the squad number which is on that range. The moment I stop on each cell and establish if this is that squad's first second third fouth round etc. the value is put onto a scoresheet which then determines the time and range as well and prints the scoresheet. So it does not matter that when I move to the next cell in the table that it overwrites the value in U1. That is the intention. By the way, there could be empty cells in the range. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances in a range.
On Monday, April 23, 2012 8:28:05 AM UTC+2, Brian wrote:
On Sunday, April 22, 2012 4:54:31 AM UTC+2, Vacuum Sealed wrote: On 22/04/2012 2:05 AM, Don Guillett wrote: On Friday, April 20, 2012 12:55:28 PM UTC-5, Brian wrote: I am using Excel 2007 and windows 7 I have a range of cells C5:F32 which is full of numbers. I am running code which steps me through every cell starting with the first column C5 to C32, moving to the next column and doing the same and ending with the last Number in column F. When it stops at each cell I need to determine how many times the same number has appeared in that range in rows prior to the row of the cell I am currently in. That determines if the number of the cell I am currently in is the 1st 2nd, 3rd or 4th time that number has appeared. The same number is never repeated in the same row so it only has to count the number of times it appears in previous rows and then obviously add 1 to determine the 1st 2nd etc. Numbers appearing in the first row will always be the first time they appear but the first time a number appears could also be in any other row.The result of the number of times it has occured is posted in cell U1 Can anyone help with the code I need. Hard to visualize so send file to dguillett1 @gmail.com with this msg and examples Question: What if there is multiple instances of differing numerals, eg if 5 rows have the number 6 and 5 rows have the number 8, how then will you expect to translate the count outcome of both sets of numbers into a single cell output at U1. Also Is there any specific pattern to the size of the numbers, be it variable or preset, by that I mean if you are looking for specific multiple instances of certain numbers in each row. HTH Mick As usual maybe more of an explanation is needed. This is being used for a competition. The heading of each column is a range number and the content of the cells is the squad number which is on that range. The moment I stop on each cell and establish if this is that squad's first second third fouth round etc. the value is put onto a scoresheet which then determines the time and range as well and prints the scoresheet. So it does not matter that when I move to the next cell in the table that it overwrites the value in U1. That is the intention. By the way, there could be empty cells in the range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count occurances of a specific character in a range? | Excel Worksheet Functions | |||
Count occurances within a date range | Excel Discussion (Misc queries) | |||
count number of occurances of a word in a range | Excel Worksheet Functions | |||
Count occurances in range of cells | Excel Worksheet Functions | |||
count date occurances in range of dates... | Excel Worksheet Functions |