Forum Home
Press F1
Thread ID: 26796	2002-11-05 11:47:00	Sorting Excel 95 database	Deebee (1184)	Press F1

Post ID	Timestamp	Content	User
96250	2002-11-05 11:47:00	I have an Excel 95 database of the roll of a small school -- 903 lines. Would like to know how to input a particular date and have Excel present me with all pupils on the roll at that date, preferably on another worksheet. I've RTFM, studied the online help and the Microsoft Knowledge Base, and am no wiser about how to do it. Do I do it with a filter, and if so how do I set up the criteria? Do I set up some sort of table? The columns concerned are named "Enrolled" and "Left", where the appropriate dates are shown, but one complication is that 12 per cent of the pupils don't have a leaving date. One way round this would be to say something like "If Left is empty, exclude any pupil where Enrolled is 12 years prior to Date" (the input date). As no pupil was at the school for more than 12 years, this would exclude any who enrolled more than 12 years before input date and don't have a Left date. Trouble is, I don't know how to write this in Excel lingo either. One more thing, I'd also like to get the pupil's age in years and months (plus days if possible) on "Date". Subtracting "Birthdate" from "Date" and formatting it y-m-d gives me an incorrect answer. Would appreciate some help.	Deebee (1184)
96251	2002-11-07 09:04:00	No answers.... Any suggestions for where I might get help on the net?	Deebee (1184)
96252	2002-11-07 09:22:00	Its a bit beyond autofilter, and you need to be an expert in Visual Basic I suspect. The age isnt so much a problem. When you subtract the birthdate from today, you get the number of days they have lived. Formatting that number as a "date" is quite erronious, you should divide it by 365 (or 365.25) to get the years. The fractional part * 12 will give the months. Leap years do not help. Look at YEARFRAC, it may be more accurate.	godfather (25)
96253	2002-11-07 09:29:00	Im not big on Exel, but wouldn't a report wizard handle that? This is a job for... Robo Man! laughter dies... ahem... SiK	SoniKalien (792)
96254	2002-11-07 10:25:00	Hi... I have done something similar a few years back, using "Pivot Tables" in Excel. The easy to use fast wizard allows you to display sub groups easily...vertically or horizontally by dragging, based on various criteria (mainly mathematical though). You drag in the appropriate heading tabs in the main table that is created, to yield want you want as subgroups. A useful feature is to use the options tab (right click...I think on the tabs in the main table) to yield more criteria ...to get customized subgroups. You may have to create a extra column to refer to the year 12 pupils that have not left so that this column is also displayed as a subgroup. Unfortunately I can't recall specifics, so best to use a copy of your database...Then again, your database may be better represented in Microsoft Access... Cheers...	Kahawai_Chaser (166)
96255	2002-11-07 21:28:00	Shouldn't be too difficult. However, it would be much simpler if we could simply exchange spreadsheets rather than try to explain each move here. Another problem I have is I'm not sure I can save in Excel 95 format. Think Excel 2000 only goes back to 97 format? I'll check, in the meantime if you feel comfortable sending me what you've got my E-Mail is BOB-MORRISON@xtra.co.nz	B.M. (505)
96256	2002-11-07 21:48:00	Wouldn't it be easier if you use Access rather than Excel ? :D	mashimaro (2168)
96257	2002-11-08 04:21:00	A macro can be written to automate the extraction from an input date, but it requires an entry to exist for all the "Left" dates. How many columns are there in total, and what are the other column headers required in the extraction?	Russell D (18)
96258	2002-11-08 07:02:00	Coming from a school environment myself, I think you want to get kids ages as at 1 January. If this is what you are after, I solved the problem this way: =DATEDIF(T3,$T$1,"Y")&"y "&DATEDIF(T3,$T$1,"ym")&"m" Where the Column "T" wasthe kids birth dates and the cell T1 was the 1 January date. Returns the data this way: 9y 10m. Hope this is what you are after. Not sure about the problem with enrolment dates.	O M Hubbard (1473)
96259	2002-11-09 07:02:00	There are a number of ways you can sort the data you want. There is a built in data sort using the Auto Filter (I think. It’s been a while since I used Excel of a 95 vintage). Here you can do a custom sort on the Left Date (< Left AND > =Enrolled). If you have missing data in the left date field that wont work so you could insert another field with a formula to determine if the pupil was enrolled on that date [ IF(Enrolled cell date <=query date (absolute value),IF(AND(Left cell date >0,Left cell date>query date (absolute value))),"Y","N"),"N") would look some like this if $G$1 is the query Date and C2 and D2 are the first row of Left and Enrolled data – IF(C2<=$G$1,IF(AND(D2>0,D2>G$1),"Y","N"),"N") ]. That would give a “N” or no value to the missing left date as well as those outside the search criteria. You could change it to IF(C11<=G$1,IF(AND(D11>0,D11>G$1),"Y","No Date"),IF(D11=0,"No Date","N")) to separate those without Left dates further. Once you have done that, use the Auto Filter to sort the data, or you could use a LOOKUP function or create a macro if you wanted to put the data on another sheet. If this is too complicated for you, best to do like the others have suggested and use Access, or find someone who likes messing with spreadsheets (like me) though I’m no ex(cel)pert. The best option if you have the time is to try to do it yourself as that’s the only way to learn how to make it work (and its more fun) :D	Craigb (688)
1 2