Count occurances in a range.

[Brian]

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 firstcolumn 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.

 

[Don Guillett]

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 appearbut 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

 

[Vacuum Sealed]

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

 

[Vacuum Sealed]

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.

 

[Brian]

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 stopon 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 inU1. That is the intention. By the way, there could be empty cells in the range.

 

[Brian]

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 thetime 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 therange.