Can this be done - continued
Author Message
Can this be done - continued

I want to emerge with a list of all dates that had a 1(for unexcused
absence)
in their cell.

Dates go  across the first row of the spreadsheet. Students occupy rows
beneath the date row in which each cell represents a date school met. I
only
have Monday to Friday listed. Under each date, in the student's row,
there is
either a 1 (unexcused absent) or .5 (late) or 0 or nothing. So John
Jones
might have 1 0   .5 1 1 1    1 for about 11 school days. And I'd need
the
dates he had the 1 for.

Somewhere in the spreadsheet, and I thought of starting somewhere low
like
cell A100 since I probably would print the result and then delete the
generated list, I want the list of dates for which he had a 1. If a cell
did
not have a 1 in it, I don't want a blank space left in the list of dates

generated. so the outcome would look like
9/15/00
9/23/00
10/17/00

and not
9/15/00

9/23/00

10/17/00

Do I make any more sense?

Thanks for caring enough to reply to me at least :-)

Quote:

> Don't understand what you mean by "going down only when a date is
> copied".

> > I need a listing of
> > all dates that had 1's.

> Dates in Column 1? 2? both?
> 1's in column 5? 6? Both?

> > I can't figure out how to reference an increasing column or row
number
> > with a variable in a for next loop.

> for i = 101 to rows.count
>    range("A"&i).formula = "=if(F" & i-1 & ", F\$1,"""")"
> next i

> > Thanks if you can do this!

> maybe if you provided some additional info?

> Or am I missing something obvious?

> --

> ROT13 encoding, decode for real mail

Fri, 07 Nov 2003 03:06:10 GMT
Can this be done - continued

Quote:

> I want to emerge with a list of all dates that had a 1(for unexcused
> absence)
> in their cell.

> Dates go  across the first row of the spreadsheet. Students occupy rows
> beneath the date row in which each cell represents a date school met. I
> only
> have Monday to Friday listed. Under each date, in the student's row,
> there is
> either a 1 (unexcused absent) or .5 (late) or 0 or nothing. So John
> Jones
> might have 1 0   .5 1 1 1    1 for about 11 school days. And I'd need
> the
> dates he had the 1 for.

> Somewhere in the spreadsheet, and I thought of starting somewhere low
> like
> cell A100 since I probably would print the result and then delete the
> generated list, I want the list of dates for which he had a 1. If a cell
> did
> not have a 1 in it, I don't want a blank space left in the list of dates

> generated. so the outcome would look like
> 9/15/00
> 9/23/00
> 10/17/00

> and not
> 9/15/00

> 9/23/00

> 10/17/00

> Do I make any more sense?

Yes! Here's a routine which will do what you want. There are a couple of
caveats:

1) Student names are in column A, row 2, and proceed down the rows.
There must be nothing under the last student's name.

2) The dates must start in Column B, Row 1 and proceed across the rows.
There must be at least one blank column after the last date.

3) If the above constraints don't work, you can set r and c manually
(but r will affect where the subsequent data is pasted.

4) Anticipating you'll need to use this for Tardy, as well, you can
substitute TARDY for ABSENT in the if cell.value = ABSENT statement.

This is quick and dirty. I've tested it with 52 columns and 10 names,
using semi-random data and it works. If I was doing for a client, I'd
pretty it up, put the lists on a separate sheet, etc. But you may be
able to do that.

Here's how it works:

A) Determine the range of students and dates
B) Copy this range, then PasteSpecial with transpose, 2 rows below last
student. This gets the dates in columns.
C) Loop through the attendance data and change any ABSENTs (1s) to the
date it occurred on. Change any non-ABSENTs to NULL.
D) Select all the blank/NULL cells, and delete them, shifting up.

You'll be left with a list of students across the row, a bogus column of
dates in column A, and the absence dates under the student name.

Is this close?

Sub AbsentDates()
Const ABSENT = "1"
Const TARDY = "0.5"

Dim rng As Range
Dim cell As Range
Dim r As Long
Dim c As Long

r = Range("A1").End(xlDown).Row
c = Range("A1").End(xlToRight).Column

Set rng = Range("A1").Resize(r, c)
rng.Copy
Range("A" & r + 2).PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Set rng = Selection.Offset(1, 1).Resize(c - 1, r - 1)
For Each cell In rng
If cell.Value = ABSENT Then
cell.Value = Range("A" & cell.Row).Value
Else
cell.Value = Null
End If
Next cell
rng.SpecialCells(xlBlanks).Delete shift:=xlShiftUp
End Sub

--

ROT13 encoding, decode for real mail

Fri, 07 Nov 2003 04:47:01 GMT
Can this be done - continued
Hi Alynn,

If you want a formula try this example.
Dates in cells  B1:M1   "across the first row of the spreadsheet"
Student's names listed in column A
In each cell under the date the student's  record,
Let's assume John Jones is in row 3. ( range  B3:M3)

In cell  A100 *array enter* this formula:

=IF(ROW(1:1)>COUNTIF(\$B\$3:\$M\$3,1),"",INDIRECT(ADDRESS(1,SMALL(IF(\$B\$3:\$M\$3=
1,COLUMN(B:M),""),ROW(1:1)))))

Now drag fill the formula down the column until the cell looks empty, this
indicates  there are no more dates  = 1.  Format the cells to your date
format.

*Array formula Must be entered by holding down the Ctrl & Shift keys then
hit Enter. Do this after you select or edit the formula. *Excel* will
enclose the formula in { } if it's entered correctly.

*Only* change the references to   \$B\$3:\$M\$3  and  B:M  or use a Named
Range, also don't use the complete row as a reference.

Hope that's what you want.

All the Best

George Simms
Microsoft MVP - Excel

Newcastle upon Tyne
England.

Quote:

> I want to emerge with a list of all dates that had a 1(for unexcused
> absence)
> in their cell.

> Dates go  across the first row of the spreadsheet. Students occupy rows
> beneath the date row in which each cell represents a date school met. I
> only
> have Monday to Friday listed. Under each date, in the student's row,
> there is
> either a 1 (unexcused absent) or .5 (late) or 0 or nothing. So John
> Jones
> might have 1 0   .5 1 1 1    1 for about 11 school days. And I'd need
> the
> dates he had the 1 for.

> Somewhere in the spreadsheet, and I thought of starting somewhere low
> like
> cell A100 since I probably would print the result and then delete the
> generated list, I want the list of dates for which he had a 1. If a cell
> did
> not have a 1 in it, I don't want a blank space left in the list of dates

> generated. so the outcome would look like
> 9/15/00
> 9/23/00
> 10/17/00

> and not
> 9/15/00

> 9/23/00

> 10/17/00

> Do I make any more sense?

> Thanks for caring enough to reply to me at least :-)

> > Don't understand what you mean by "going down only when a date is
> > copied".

> > > I need a listing of
> > > all dates that had 1's.

> > Dates in Column 1? 2? both?
> > 1's in column 5? 6? Both?

> > > I can't figure out how to reference an increasing column or row
> number
> > > with a variable in a for next loop.

> > for i = 101 to rows.count
> >    range("A"&i).formula = "=if(F" & i-1 & ", F\$1,"""")"
> > next i

> > > Thanks if you can do this!

> > maybe if you provided some additional info?

> > Or am I missing something obvious?

> > --

> > ROT13 encoding, decode for real mail

Fri, 07 Nov 2003 04:58:32 GMT
Can this be done - continued

Quote:
> =IF(ROW(1:1)>COUNTIF(\$B\$3:\$M\$3,1),"",INDIRECT(ADDRESS(1,SMALL(IF(\$B\$3:\$M\$3=
> 1,COLUMN(B:M),""),ROW(1:1)))))

Wow.

I'd tried something like this, but got wrapped around the axle, so I
wrote the macro instead.

I...think...I understand...

--

ROT13 encoding, decode for real mail

Fri, 07 Nov 2003 06:33:50 GMT
Can this be done - continued
WOW! and THANK YOU! You made my school-related life so much easier. I spend so
much time checking and rewriting these dates when students and parents
complain about my attendance letters and now, with a little patience as it
runs, I get the list printed for the whole class. I can't thank you enough.
When I retire (13 months) I have to learn this language. (I used to write
macros in the old days - but not the new Windows things).

Thanks again and again.

Quote:

> Is this close?

> Sub AbsentDates()
> Const ABSENT = "1"
> Const TARDY = "0.5"

> Dim rng As Range
> Dim cell As Range
> Dim r As Long
> Dim c As Long

> r = Range("A1").End(xlDown).Row
> c = Range("A1").End(xlToRight).Column

>      Set rng = Range("A1").Resize(r, c)
>      rng.Copy
>      Range("A" & r + 2).PasteSpecial Paste:=xlAll, Operation:=xlNone, _
>          SkipBlanks:=False, Transpose:=True
>      Set rng = Selection.Offset(1, 1).Resize(c - 1, r - 1)
>      For Each cell In rng
>           If cell.Value = ABSENT Then
>                cell.Value = Range("A" & cell.Row).Value
>           Else
>                cell.Value = Null
>           End If
>      Next cell
>      rng.SpecialCells(xlBlanks).Delete shift:=xlShiftUp
> End Sub

> --

> ROT13 encoding, decode for real mail

Fri, 07 Nov 2003 09:51:30 GMT
Can this be done - continued

Quote:

> WOW! and THANK YOU! You made my school-related life so much easier. I spend so
> much time checking and rewriting these dates when students and parents
> complain about my attendance letters and now, with a little patience as it
> runs, I get the list printed for the whole class. I can't thank you enough.
> When I retire (13 months) I have to learn this language. (I used to write
> macros in the old days - but not the new Windows things).

> Thanks again and again.

There are probably half-a-dozen ways to speed it up, but I was trying
for ease of understanding. The biggest speedup will probably be to put
this statement right after the "Dim x as y" statements:

Application.ScreenUpdating = False

Then just before End Sub, put:

Application.ScreenUpdating = True

I didn't include them in the original because their behavior often
confuses beginners - the screen seems to "freeze" for a short while,
even though the routine is running. You should see a fairly significant
improvement just from these two statements.

VBA is a pretty forgiving language to learn - you can usually find
enough between the macro recorder and Help to get you going, and these
ng's (.programming, .worksheet.functions, etc.) are a tremendous source
of expertise. Good luck!

--

ROT13 encoding, decode for real mail

Fri, 07 Nov 2003 11:38:24 GMT
Can this be done - continued
Thanks again - it did speed things up.

FYI your output came in handy already. I had 3 parents on the phone (out of 150
students I see daily) and one guidance counselor today who asked about attendance. I
whipped out your output and offered them a chance to copy the dates. Super!

Quote:

> > WOW! and THANK YOU! You made my school-related life so much easier. I spend so
> > much time checking and rewriting these dates when students and parents
> > complain about my attendance letters and now, with a little patience as it
> > runs, I get the list printed for the whole class. I can't thank you enough.
> > When I retire (13 months) I have to learn this language. (I used to write
> > macros in the old days - but not the new Windows things).

> > Thanks again and again.

> There are probably half-a-dozen ways to speed it up, but I was trying
> for ease of understanding. The biggest speedup will probably be to put
> this statement right after the "Dim x as y" statements:

> Application.ScreenUpdating = False

> Then just before End Sub, put:

> Application.ScreenUpdating = True

> I didn't include them in the original because their behavior often
> confuses beginners - the screen seems to "freeze" for a short while,
> even though the routine is running. You should see a fairly significant
> improvement just from these two statements.

> VBA is a pretty forgiving language to learn - you can usually find
> enough between the macro recorder and Help to get you going, and these
> ng's (.programming, .worksheet.functions, etc.) are a tremendous source
> of expertise. Good luck!

> --

> ROT13 encoding, decode for real mail

Sat, 08 Nov 2003 08:35:58 GMT
Can this be done - continued

Quote:

> Thanks again - it did speed things up.

> FYI your output came in handy already. I had 3 parents on the phone (out of
> 150
> students I see daily) and one guidance counselor today who asked about
> attendance. I
> whipped out your output and offered them a chance to copy the dates. Super!

Glad it was useful!

--

ROT13 encoding, decode for real mail

Sat, 08 Nov 2003 10:12:33 GMT
Can this be done - continued
Actually the biggest speedup would normally be by turning off
calculation.  Turning off screen updating has the most impact where
one is changing the active cell which is seldom necessary.

Sub ......()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'    ooo   Your code here   ooo
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros:  http://www.geocities.com/davemcritchie/excel/excel.htm

Quote:
> Thanks again - it did speed things up.

Sat, 08 Nov 2003 12:08:35 GMT
Can this be done - continued

Quote:
> Actually the biggest speedup would normally be by turning off
> calculation.  Turning off screen updating has the most impact where
> one is changing the active cell which is seldom necessary.

> Sub ......()
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>   '    ooo   Your code here   ooo
> Application.Calculation = xlCalculationAutomatic
> Application.ScreenUpdating = True
> End Sub

Interesting - in this case the primary manipulations were:

1) Copy a large range of almost entirely constants
2) PasteSpecial/Transform
3) Loop through the range, substituting a constant or NULL for constant
4) Deleting Blank Cells

I normally turn off calculation at the same time I turn off
screenupdating, but given the steps above, I (perhaps mistakenly)
assumed that calculation would have little or no effect, given that I
was swapping constants.

Was I incorrect?

I may run a couple of tests tomorrow.

--

ROT13 encoding, decode for real mail

Sat, 08 Nov 2003 13:13:54 GMT
Can this be done - continued
Concerning time savings of  turning off screen updating
versus  turning of  calculation.  (Suggest doing both).

I don't know how much effect turning off calculation
would have if Transpose
is involved that would limit the scope to a maximum of
256 rows and 256 columns (as least prior to 2002).

But turning off calculation does seem to make a difference
even when the cell content does not involve calculations.
(not saying that what I think I see is always true).

In any case I was speaking in general terms, I didn't remember
the specifics of the original post.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros:  http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page:        http://www.geocities.com/davemcritchie/excel/search.htm

Quote:
> Interesting - in this case the primary manipulations were:

> 1) Copy a large range of almost entirely constants
> 2) PasteSpecial/Transform
> 3) Loop through the range, substituting a constant or NULL for constant
> 4) Deleting Blank Cells

> I normally turn off calculation at the same time I turn off
> screenupdating, but given the steps above, I (perhaps mistakenly)
> assumed that calculation would have little or no effect, given that I
> was swapping constants.

> Was I incorrect?

> I may run a couple of tests tomorrow.

> --

> ROT13 encoding, decode for real mail

Sat, 08 Nov 2003 23:18:32 GMT
Can this be done - continued
FYI, ran a few tests with a medium duty data set (185 days, 26 children,
semi random distribution of absences and tardies). (I wasn't worried
about the transpose limits, since most school years are <255 days.)

Ran multiple iterations on a G4/450 Mac, XL2001:

Screenupdating = True, Calculation = Automatic: 19.5 sec/iteration
Screenupdating = False, Calculation = Automatic: 16.6 sec/iteration
Screenupdating = True, Calculation = Manual: 17.3 sec/iteration
Screenupdating = False, Calculation = Manual: 16.3 sec/iteration

This isn't particularly well-controlled - I had to be running other
things in the background at the time, but multiple trials were pretty
consistent.

As I suspected, since most of the work was swapping constants, neither
turning screenupdating off nor setting calculation mode to manual had
the kind of dramatic effect I've sometimes seen.

What I don't understand, however, is why changing from Automatic
Calculation to Manual, which gives better than an 11% speed-up (as
compared to turning off screenupdating which yields nearly 15%), has
such a miniscule effect when screenupdating is off (only an additional
0.7%). And on a data set of about half the size, calc mode had NO effect
when screenupdating was off, although it gave a 14% speed up when
updating was on.

It'll take me some mulling time...

Your observation about manual calculation speeding up even routines that
don't involve calcs is supported, certainly.

Quote:
> Concerning time savings of  turning off screen updating
> versus  turning of  calculation.  (Suggest doing both).

> I don't know how much effect turning off calculation
> would have if Transpose
> is involved that would limit the scope to a maximum of
> 256 rows and 256 columns (as least prior to 2002).

> But turning off calculation does seem to make a difference
> even when the cell content does not involve calculations.
> (not saying that what I think I see is always true).

> In any case I was speaking in general terms, I didn't remember
> the specifics of the original post.

> HTH,
> David McRitchie, Microsoft MVP - Excel
> My Excel Macros:  http://www.geocities.com/davemcritchie/excel/excel.htm
> Search Page:        http://www.geocities.com/davemcritchie/excel/search.htm

> > Interesting - in this case the primary manipulations were:

> > 1) Copy a large range of almost entirely constants
> > 2) PasteSpecial/Transform
> > 3) Loop through the range, substituting a constant or NULL for constant
> > 4) Deleting Blank Cells

> > I normally turn off calculation at the same time I turn off
> > screenupdating, but given the steps above, I (perhaps mistakenly)
> > assumed that calculation would have little or no effect, given that I
> > was swapping constants.

> > Was I incorrect?

> > I may run a couple of tests tomorrow.

> > --

> > ROT13 encoding, decode for real mail

--

ROT13 encoding, decode for real mail

Sun, 09 Nov 2003 01:30:58 GMT

 Page 1 of 1 [ 12 post ]

Relevant Pages

Powered by phpBB® Forum Software © phpBB Group