Excel VBA Beginners problem

K

knoertje

On one sheet I keep track of the lottery numbers every week. On anothe
sheet I try to keep the statistics of these numbers, like how man
times and how many drawings ago a number ........ The how many time
was no problem, with a COUNTIF....but the how many drawings ago
doesn't work. I tried an IF (sheet1!A1:A6="number",sheet2!A1+1
sheet2!=1. But then I get a circular reference statement. How do
proceed????
 
C

Colleyville Alan

knoertje > said:
On one sheet I keep track of the lottery numbers every week. On another
sheet I try to keep the statistics of these numbers, like how many
times and how many drawings ago a number ........ The how many times
was no problem, with a COUNTIF....but the how many drawings ago,
doesn't work. I tried an IF (sheet1!A1:A6="number",sheet2!A1+1,
sheet2!=1. But then I get a circular reference statement. How do I
proceed?????
Click to expand...

I'm not sure if this is what you are hoping to do, but just using worksheet
functions without VBA, this seems to do what I understand you are asking
about.

If you have 5 columns on the worksheet like this:

Col A Col B Col C Col D
Col E
Number Times Drawn Date Weeks Ago #
weeks ago this was drawn

25 2 4/24/04 1
4
31 3 4/17/04 2
3
31 3 4/10/04 3
6
25 2 4/3/04 4
66 3/27/04 5
31 3 3/17/04 6

Col A says what number was drawn. A countif function in col B shows how
many times it has been drawn in previous drawings. Col c is the date of the
drawing, col D is how many weeks agao it the drawing was held and Col E says
if a number was drawn more than once, how many weeks ago from today did it
last get drawn. So the spreadsheet shows that 31 has been drawn 3 times.
The last time it was drawn was 3 weeks ago and the time before that was 6
weeks ago, both calculated from today (i.e. when I say that on April 10th
the number drawn was 31 and the previous instance of 31 was 6 weeks ago, it
is 6 weeks ago today and not 6 weeks ago to April 10).

If this is what you are envisioning, here are the functions I used:
for the countif function in column B:
=IF(COUNTIF(A$2:A$7,A2)>1,COUNTIF(A$2:A$7,A2),"")

For the function in column E:
=IF(ISERROR(VLOOKUP(A2,A3:D$7,4,FALSE)),"",VLOOKUP(A2,A3:D$7,4,FALSE))

CA
 
K

knoertje

Thanks for your fast reply, and I have tried your solution,but I'm no
sure what to do with it. I'm not much good at it. But I will give
more clearer view of what I'm trying to do.
I have sheet nr.1 with column A contain the date of the drawing. Colum
B,C,D,E,F,G contain the numbers from the drawings. The numbers rang
from 1 to 45. On sheet nr.2 I have a column A containing number 1 dow
to 45, column B contains how many weeks ago that number fell. I di
that with a COUNTIF statement. (COUNTIF(sheet1!B:G;A1) This nicely add
up how long ago the drawings was. But say when this week the numbe
draws, the number in column B should go to zero. I don't know to tackl
this problem. As I stated earlier the way I tried it is not a way tha
Excel allows
 
J

John

I think I have a fix for you:

In my workbook I set the following:

weekly number contains the weekly data (sorted with latest
on top in row 5) with the numbers in columns b:f (I
allowed for columns g and h too)

analysis contains the equations for analyzing the data
with cell c3 specifying how many weeks back to look for an
occurance, and cell a1 a count of the total number of
weeks, and the # you are searching for listed in column b


The # of occurrances is"

=COUNTIF('weekly numbers'!$B$5:OFFSET('weekly numbers'!
$B$4,analysis!$A$1,COUNTA('weekly numbers'!
$B$3:$H$3),1,1),analysis!$B6)

The following finds the week of the last occurrance - do
this column by column so if you have 5 weekly numbers you
need this in 5 columns searching the columns of data one
at a time.

IF(ISNA(MATCH($B6,'weekly numbers'!B$5:OFFSET('weekly
numbers'!B$4,analysis!$A$1+1,0,1,1),0)),"",MATCH
($B6,'weekly numbers'!B$5:OFFSET('weekly numbers'!
B$4,analysis!$A$1+11,0,1,1),0))

Then use this to determine the latest occurance of the
five columns (a # could be in column 1 one week and column
2 another)

=IF(MIN(E6:I6)=0,"",MIN(E6:I6))

where columns e:i find the latest occurance for each column

I guess I could just email you the worksheet.

John
 
J

John

sorry - i missed one thing

this limits you to the right number of weeks to search

(use cell c3 to limit you to say 5 last 5 weeks - in my
sheet cell a1 was the total number of weeks of data)

=COUNTIF('weekly numbers'!$B$5:OFFSET('weekly numbers'!
$B$4,analysis!$C$3,COUNTA('weekly numbers'!
$B$3:$H$3),1,1),$B5)

John
 
D

Don Guillett

I looked at your workbook. I would
1. sort by last at the top with one intervening row that is hidden,
inserting each time at row 3
2. use a for each macro to find each number from the other page such as this
example.

Sub findem()
for each c in numberrange
c.offset(,1)=application.CountIf(Range("mt"), c)
c.offset(,2) = Cells.Find(c, after:=Range("a2"), _
lookat:=xlWhole, searchorder:=xlByRows).Row - 2
next
End Sub
 
D

Don Guillett

I defined a name for MyTable and NumberRange and use this to countif and
find last drawing.

Sub Upate()
On Error Resume Next
For Each c In Range("NumberRange")
c.Offset(, 1) = Application.CountIf(Range("MyTable"), c)
c.Offset(, 2) = Range("MyTable").Find(c, after:=Sheets("drawing"). _
Range("b2"), lookat:=xlWhole, searchorder:=xlByRows).Row - 2
Next
End Sub
Sub NewRow()
Sheets("drawing").Rows(3).Insert
End Sub

--
Don Guillett
SalesAid Software
[email protected]
Don Guillett said:
I looked at your workbook. I would
1. sort by last at the top with one intervening row that is hidden,
inserting each time at row 3
2. use a for each macro to find each number from the other page such as this
example.

Sub findem()
for each c in numberrange
c.offset(,1)=application.CountIf(Range("mt"), c)
c.offset(,2) = Cells.Find(c, after:=Range("a2"), _
lookat:=xlWhole, searchorder:=xlByRows).Row - 2
next
End Sub
Click to expand...
 
Top