Can this be done  continued
Author 
Message 
Alyn #1 / 12

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" & i1 & ", 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 


J.E. McGimpse #2 / 12

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 semirandom 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 nonABSENTs 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 


George Simm #3 / 12

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" & i1 & ", 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 


J.E. McGimpse #4 / 12

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 


Alyn #5 / 12

Can this be done  continued
WOW! and THANK YOU! You made my schoolrelated 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 


J.E. McGimpse #6 / 12

Can this be done  continued
Quote:
> WOW! and THANK YOU! You made my schoolrelated 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 halfadozen 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 


Alyn #7 / 12

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 schoolrelated 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 halfadozen 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 


J.E. McGimpse #8 / 12

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 


David McRitchi #9 / 12

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 


J.E. McGimpse #10 / 12

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 


David McRitchi #11 / 12

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 


J.E. McGimpse #12 / 12

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 wellcontrolled  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% speedup (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 


