• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing data which may change...

K

karldavey

New Member
Hi all,


I have a database of students at my work place. It is a list of names and their attendance. There is a sheet for each academic week with their name, current week attendance, weekly comparison and their overall attendance (along with other info).


The problem I have is that students may leave the college or sign up to the college throughout the year. This means that I cannot compare data as a student who may have been in row 100 one week might be in row 102 next.


Any ideas how I may be able to get around this without affecting my formula (averages comparisons etc).


Many thanks in advance
 
Hi karldavey,


Welcome to the forum! I think students can be identified by something like Roll No. or General Register No. for their is only one for a student. If a student was present in First week and was absent in Second then in second week this Nos should not be present? Am I understanding thing right? Can you give layout of your data or upload a sample file?


Faseeh
 
Hi Faseeh,


Thanks for getting back to me. Unfortunatly the program from which I extract the attendance data does not give a fixed number such as student ID (annoying I know).The only fixed thing is their name.


I have columns arranged as follows...


Surname - Forename - This Weeks attendance - %change from last - Overall attendance -


There are others after such as whether a legal letter has been sent etc.


There are 39 sheets all formatted the same way so I only have to copy in Surname, forname and this weeks attendance. The spreadsheet does the rest (incl conditional formatting).


The only way I can tell if a student has been added on or taken off is when I compare week by week (eg 516 students one week, 518 the next).


Hope thats a little clearer?


Many thanks!!
 
Hi karldavey,


Ok that's clear now that you have got 39 sheets and their layout with students (..names but not in same order). Now what do you want next? So what do you want in end? The sheet should show which student left?


Regards,

Faseeh
 
Just a thought, but if you could change the way your main data is stored to have two columns "Date Started" and "Date Left", that would enable you to easily determine which students have left/started at different points.
 
Hi!


Thanks again for the reply.

So now I have the columns ordered I want to take the data and compare them week by week. So "week 1" I will have the raw attendance data, "week 2" (sheet 2) will have the new attendance data and will compare that (student by student)to the previous sheet. This way I can see the students overall attendance, whether they have increased or decreased and so on.


Example problem: all is well untill week 9 (sheet 9) when two new students are added in. The students are ordered alphabetically so it knocks out the comparison formula


=(D8-'Week 9'!D8)


and the average formula


=SUM(('Week 1'!D9+'Week 2'!D9+'Week 3'!D9+'Week 4'!D9+'Week 5'!D9+'Week 6'!D9+'Week 7'!D9+'Week 8'!D9+D9/9)


And so on...


Is there anyway to get around this??


Thanks for your help so far and being patient with me!
 
Hi, karldavey!


Kindly see this sheet might be of some help!! BTW SirJB7 is right, uploading file will help alot!


http://dl.dropbox.com/u/60644346/Sample.xlsx


@SirJB7


Hi!!


Understood your concatenate()+Indirect() formula partially, it has been of real help. Unable to accommodate it to make this one a bit larger!! :D


Regards!

Faseeh
 
@Faseeh


Hi!

I didn't get what you were unable to do. Can you write it in other words? If you want do as Elton John said in A Word In Spanish :)

Without trying to hijack this topic from karldavey. Otherwise you should write on the original one or start a new one :)))

Regards!
 
Hi all,


Sorry for the late responce, have been out of the office for a couple of days.

I have uploaded a sample file for you to have a look at and see what I am so poorly explaining!


https://docs.google.com/open?id=0BxwE5_UqQAI5eFg2cHpoaVVUMUdiOTdsdHdSLU42dw


When I copy and paste the next weeks information (week 4 and it has an aditional name in begining with A) it will knock out the order of names so it will not compare the same students information only the information in the Cells.


Is there anyway I can ensure that no matter who's information is added or removed, it will still compare a students information and not just the data in the cell.


I hope I'm making sense here... Really poor at explaining myself!


Thanks!
 
Hi, karldavey!


Firstly, I made many changes to your workbook in order to prepare its structure for being easily used. Here they are:

a) eliminated blank column A in sheet Week1

b) added columns to the right so as to have the same design that other WeekN sheets

c) standardized column headings, capitalization, widths and heights

d) set unmovable columns to first two (surname and forename) and rows to first one (titles)

e) trimmed leading blank space in column B for Victoria and Luke names

f) changed 'Monthly comparison' to 'Monthly comparison (%)'

g) added a Week number in auxiliary column K in cell K1

h) added a helper column in L for the accumulate thru Week n-1

i) changed 'Attendance this month (%)' to 'Attendance this week (%)'


Secondly, a couple of questions:

1) Column E, 'Attendance this month (%)', shouldn't it be 'Attendance this week (%)'? Changed.

2)Column H, Letter, what is it? Because in every WeekN sheet except Week1 you have a formula like:

=IF(ISNA('Week 1'!G2),0,('Week 1'!G2))

And 'Week 1'!G2 cell is empty, so it wouldn't ever have a not available value. That's to say that the always displays the false part of the IF statement. Maybe you wanted to ask if it was empty? If so, correct those formulas to:

=IF(ISBLANK('Week 1'!G2),0,('Week 1'!G2))

If you're not planning to place in sheet Week1 in column H (Letter) a VLOOKUP formula or any other one that could retrieve a N/A value, for WeekN sheets other than Week1 I suggest you to simply leave the formula:

='Week 1'!G2

3) 'Monthly comparison' is always performed against Week1 or should it be done against previous Week? Or both? It's a number, not a percentage? Because it has one decimal digit.

Added vs. 1st week and previous week. Left as percentage expressed as number with no decimal places.

4) 'Attendance to date' is always calculated as the average between Week1 and current week? Shouldn't it be calculated as average from Week1 to current week? It's a percentage? Because of the title, but it's expressed as a number with one decimal place.

Changed to average up to current week. Left as percentage expressed as number with no decimal places.


And at last, your main issue: how to calculate the monthly comparison without displacements in cell references when adding or removing students.

i.) formulas thru WeekN worksheets are feasible to be built, that's how there are calculated columns H:I using INDIRECT and ADDRESS functions, somehow tricky but not much... until now, everything OK

ii.) but the big issue is the row referencing when changing number of students, as you're gonna sort them alphabectically (which wouldn't happen if you simply added at the end, but I understand you can't)... first problem

iii.) using combinations of INDEX and MATCH formulas all could be solved except a clean (i.e., short and understandable formula) solution for the accumulates ranging thru WeekN sheets... second and biggest problem... so...

iv.) I recommend you to join all weeks in one sheet with the following advantages:

- you get rid of issues about referencing changed students

- you don't have to handle tricky formulas for accumulates

- you can sill have the equivalent to each Week sheet by filtering unique sheet excluding blank values

- you don't neither have to care about referencing Letter and Comment from first week nor take care of entering SurName/ForeName exactly as in first sheet... because there's only one sheet!

- I don't find any disadvantage; if you do, please advise


So you may want to analyze how 'Week 1' thru 'Week 4' sheets have been updated, then do the same with suggested 'All weeks' sheet, and after that compare and decide. In the meanwhile feel free to ask what you need.


Here's the link for the dowloadable file:

http://dl.dropbox.com/u/60558749/Comparing%20data%20that%20may%20change%20-%20Weekly%20attendance%20V2%20%28for%20karldavey%20at%20chandoo.org%29.xlsm


Please check macro assigned to button 'Sort', as it splits text from column C into A and B. I guess that it's for building the student list in two fields. You can do it in a work or auxiliary sheet and then copy paste to main sheet.


Regards!


PS: this time I absolutely underestimated the scope of the issue... but it's to late to regret, the job is yet done :)
 
Hi SirJB7,


I have been working yesterday over this sheet but stuck on how to add up a new sheet in formulas so that there remains no need for editing/adding formulas every time. Thanks for writing a macro i will examine it and try to learn how to do that. Thank You.


Faseeh
 
@Faseeh


Hi!


I didn't write any macro, just checked what code was in the VBA project and found two macros: one for sheet selecting, no problem, but the other (called from 'Sort' button) wouldn't work, even with the original files. I think it was for splitting name and surname, that's why I alerted the user.


I only made some order, got all sheets with same structure, and wrote a couple of formulas... then decided to create a new one "All weeks" so as to work the least possible. Sounds familiar?


Regards!
 
Hi SirJB7 (&Faseeh!),


Thank you for your time and effort! I have had a good look through it now and see what you have done!


I'll go through what you have put and reply to each :)


a) week one was different as there was no need to comparison (as it is the first week). it did need cleaning up though, thank you!

b) as above, thank you again,

c) Thanks, had not got around to sorting that out!

d)unmoveable columns make sense!

e)again it was me not being neat (just bunged random names in for the test.

f) I adapted the sheet from an old monthly comparison one and never changed the titles... (that one worked great)

g)never seen that formula before not sure what it does.. will try to figure it out in a while.


The letter part is the legal letters that we have to send out if the attendance is at a certain point. (eventually I will set it to auto generate etc).

The idea of the formula was to carry over the status (eg if legal letter 1 was sent out in week 2 it would display that in all other week sheets after that, week 3, 4 and so on). I hadn't got around to editing the formula on each one so that it would read:


=IF(ISNA('Week 2'!G2),0,('Week 2'!G2)) for week 3

=IF(ISNA('Week 3'!G2),0,('Week 3'!G2)) for week 4...


You are very right about the advantage of putting it all togther in one sheet, I would need to make a few changes to the conditional formatting but its going to make life a hell of a lot easier as you said. You have given me some fresh ideas (and taught me a lot). I will use what you have given me and adapt it further.


The macro sorted names, when they are pasted in they are formatted:


surname,firstname


in one column (B), the macro splits them and pasts the surname into a new column. It only works when you paste new data in and click it, after that it is redundent... I have just found that because of some earlier alterations the Macro is not working properly (my fault should have spotted it :-( )


Right.... I'm going to crack on using your fantastic advise. Been looking and fiddling for so long that its created more problems. Going to use what you have said and start ground up...


Thank you so so much for this!


Regards to all!
 
Hi, karldavey!


Thank you for your kind comments and I hope you'll agree with me in eliminating all the individual WeekN worksheets and keeping only one All Weeks.

With a little filtering and column hiding, you'd be able to reproduce exactly each Week N previous sheet.

Welcome back whenever needed or wanted.


Regards!
 
Hi, karldavey!


Just one thing I forgot to mention or at least I didn't clearly at all.

For still using macro AutoSplit which takes the entire name from column C and splits it in Surname in column A and Forename in column B, just use any auxiliary worksheet, copy from source -whichever it is- to column C, run the macro, and then copy columns A:B from this helper worksheet to sheet 'All weeks' in columns A:B.


Regards!
 
Hi SirJB7,


Due to my tinkering with the layout I forgot to change the Macro's coding.


Origionally it Surname was Column B and Forname was Column C.


I would past the data in, which started in Column C (Surname,Forename) and attandance data (column D). when The button was pressed the Macro Autosplit the names and pasted the Surname into Column B. It actually worked pretty well (I was proud anyway!)


Thank you for your help, it is very much appriceated!


Regards
 
Hi, karldavey!


I'd suggest to do it in an auxiliary worksheet and then copy & paste the values into sheet 'All weeks'. So you don't have to change your macro code, just the worksheet where it acts.


Regards!
 
You must log in or register to reply here.
Back
Top