If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
I'm sorry David.........I thought you had gotten to the point where you had
only names in one column, with their data in the columns to the right.........like ColumnA................ColumnB.....ColumnC Name........................Data1.........Data2 Brown,Charlie..........address1....address2 Brown,Charlie..........car1...........car2 Brown,Charlie..........wife1..........wife2 Brown,Charlie...........kids1.........kids2 Armstrong,Jack.........address1....address2 etc etc And was hand-entering Brown,Charlie1....... Brown,Charlie2.........etc Then Armstrong,Jack1.......Armstrong,Jack2........etc.. ....... I was only trying to point out that Armstrong,Jack11....Armstrong,Jack12.....etc would sort just as well, as long as the data within each group was sequential, it not have to start again with 1,2,etc.......this way it could be concatenated in instead of having to be typed in............. If your data was in that order, you could have just put sequential numbers in a helper column, starting with 101 at the top and incrementing down as far as you have data(actually101 instead of 1 in case you have more than 9 rows for a name to prevent it sorting 1,10,2, etc)..........then in the next helper column you could have CONCATENATED column A (the name) with the sequential number in that helper column, and then copied this formula down. This method will give you a unique name-number combination for each row, sequenced within each group, and they will sort and stay together. If you can get to that point on your own, that would probably be the easiest way to be able to sort by name and keep the associated data together with the name. I've done this with several thousand row databases with varying numbers of data lines for each principal. It works good. If you cannot get to that name/data configuration described above then send me a sample of your worksheet ( to my home addy, not the newsgroup), and I will try to bust it out for you. If you do, send enough lines as to be typical of the whole thing........ Basically what I'm trying to describe is something like this........ NameA.....data.....data.....data.....101 NameA.....data.....data.....data.....102 NameA.....data.....data.....data.....103 NameK.....data.....data.....data.....104 NameK.....data.....data.....data.....105 NameK.....data.....data.....data.....106 NameB.....data.....data.....data.....107 NameB.....data.....data.....data.....108 NameB.....data.....data.....data.....109 Then in the column after the sequential numbers, concatenate the Name and the sequential number so it reads NameA101, NameA102, NameA103, NameK104, etc down that column........then sort on that column and NameB will follow NameA, etc and the data will stay together...... I've also seen posts in these newsgroups whereby Macros were offered to break data similar to yours out to all read on one row.........that might be an alternative.......unfortunately, not my area of expertise, but if you are interested in that approach, post back in a new thread and someone will no doubt be able to help with that. Otherwise, we'll keep at this one until you have something you can use. Vaya con Dios, Chuck, CABGx3 "David" wrote in message ... CLR wrote Followup on your followup g Although the concatenated suffix's must be in sequence for each group, they need NOT start with 1 each time..........sooo, you could just use another helper column with numbers sequenced from 1-XXXX, and then concatenate the names and the respective row from this new column, so it might read, Brown,Charlie1....Brown,Charlie2......Armstrong,Ja ck12......Armstrong,J ack13 , etc etc..........would make the initial setup easier, just type in one formula and copy down................ Ok, I follow the first part (sequenced numbers in another helper column), but then I get lost with 2nd (concatenate names and the respective row from this new column). Test data: Col A = names followed by 3 rows of data in Col A, B & C Col D = 1st helper column with ??? formula to copy down Col E = 2nd helper oolumn with sequenced numbers I'm lost. -- David |
#12
|
|||
|
|||
Hi David Turner,
How about the alternative, easy enough to sort the worksheets http://www.mvps.org/dmcritchie/excel...#sortallsheets and to have a master sheet and total on the master sheet http://www.mvps.org/dmcritchie/excel/buildtoc.htm I don't know what you mean by a subtotal in the sheets, as long as the total is for just that sheet you simply use SUM or even SUBTOTAL To me the original seems so unwieldy maintaining a page worth of data (many rows) for each student and every student within a single worksheet. If it were one row of data per student then you would not have a problem with sorting. But from what I can get from your arrangement it seems it would be difficult to find where to update a student. HTM, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David" wrote in message news:uva% Acceptable alternative: Have each student on their own page with sheets named Lastname Firstname, sort those, and still be able to Subtotal in cells on those sheets, then Grand Total on a separate sheet. |
#13
|
|||
|
|||
CLR wrote
I'm sorry David.........I thought you had gotten to the point where you had only names in one column, with their data in the columns to the right.........like ColumnA................ColumnB.....ColumnC Name........................Data1.........Data2 Brown,Charlie..........address1....address2 Brown,Charlie..........car1...........car2 Brown,Charlie..........wife1..........wife2 Brown,Charlie...........kids1.........kids2 Armstrong,Jack.........address1....address2 etc etc And was hand-entering Brown,Charlie1....... Brown,Charlie2.........etc Then Armstrong,Jack1.......Armstrong,Jack2........etc.. ....... I was only trying to point out that Armstrong,Jack11....Armstrong,Jack12.....etc would sort just as well, as long as the data within each group was sequential, it not have to start again with 1,2,etc.......this way it could be concatenated in instead of having to be typed in............. Basically what I'm trying to describe is something like this........ NameA.....data.....data.....data.....101 NameA.....data.....data.....data.....102 NameA.....data.....data.....data.....103 NameK.....data.....data.....data.....104 NameK.....data.....data.....data.....105 NameK.....data.....data.....data.....106 NameB.....data.....data.....data.....107 NameB.....data.....data.....data.....108 NameB.....data.....data.....data.....109 Then in the column after the sequential numbers, concatenate the Name and the sequential number so it reads NameA101, NameA102, NameA103, NameK104, etc down that column........then sort on that column and NameB will follow NameA, etc and the data will stay together...... Yeah, that makes sense if data was laid out as you describe. The sheet layout has to remain as it is (school issued). My followup solution works fine for me. Thanks for your help and offer. -- David |
#14
|
|||
|
|||
David McRitchie wrote
Hi David Turner, How about the alternative, easy enough to sort the worksheets http://www.mvps.org/dmcritchie/excel...#sortallsheets and to have a master sheet and total on the master sheet http://www.mvps.org/dmcritchie/excel/buildtoc.htm I don't know what you mean by a subtotal in the sheets, as long as the total is for just that sheet you simply use SUM or even SUBTOTAL To me the original seems so unwieldy maintaining a page worth of data (many rows) for each student and every student within a single worksheet. If it were one row of data per student then you would not have a problem with sorting. But from what I can get from your arrangement it seems it would be difficult to find where to update a student. HTM, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm David, thanks for jumping in. The workbook was provided to me by another school. Users there have to update it manually and I'm trying to simplify the process to properly handle adding/removing students, yet maintain the integrity of the workbook. I came across a snipit of code to handle regenerating page breaks, but needed something to sort the results of adding a student before running it. As for the alternative, I did grab the sortallsheets routine in case I get permission to alter the layout so each student has their own sheet. That does seem like it would be easier to maintain. I've never used a TOC, so I'll have to have a look. Each student has several classes with a total for hours spent in each class and total class time for the month, and then another page at the bottom that totals those totals for a grand total of hours spent in each class by ALL students during the month. The original file uses SUM for each grand total #, and the formulas are looonnnggg and they now have to be updated manually if a new student is added or a student is removed, so I'm thinking SUBTOTALs in place of SUMs would be better. To give you a brief picture of original layout: Page 1 Dates across row, starting in Col2 StudentName1 ClassName1 TotalHrs1 ClassName2 TotalHrs2 ClassName3 TotalHrs3 SUM Monthly Student1 class time Page 2 Dates across row, starting in Col2 StudentName2 ClassName1 TotalHrs1 ClassName2 TotalHrs2 ClassName3 TotalHrs3 SUM Monthly Student2 class time Page X Class1 SUM monthly hrs Class1 all students Class2 SUM monthly hrs Class2 all students Class3 SUM monthly hrs Class3 all students Page for each student printed monthly and filed in their chart. Page X for Utilization Review. I'm tackling simplifications one at a time, so I may be seeking others in the future g. -- David |
#15
|
|||
|
|||
Hi David,
Perhaps the example at http://www.mvps.org/dmcritchie/excel/buildtoc2.htm might be helpful. I emailed an example based on sketchy details you posted. Don't know if all sheets have the same format, if a date is one day or a semester. Don't know if all students take the same classes that was my assumption; otherwise, the class report is not going to work. I think you might be able to do this with a couple of Pivot Tables if the data is strictly studentname, class, date see http://www.contextures.com/tiptech.html HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David" wrote in message ... David McRitchie wrote Hi David Turner, How about the alternative, easy enough to sort the worksheets http://www.mvps.org/dmcritchie/excel...#sortallsheets and to have a master sheet and total on the master sheet http://www.mvps.org/dmcritchie/excel/buildtoc.htm I don't know what you mean by a subtotal in the sheets, as long as the total is for just that sheet you simply use SUM or even SUBTOTAL To me the original seems so unwieldy maintaining a page worth of data (many rows) for each student and every student within a single worksheet. If it were one row of data per student then you would not have a problem with sorting. But from what I can get from your arrangement it seems it would be difficult to find where to update a student. HTM, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm David, thanks for jumping in. The workbook was provided to me by another school. Users there have to update it manually and I'm trying to simplify the process to properly handle adding/removing students, yet maintain the integrity of the workbook. I came across a snipit of code to handle regenerating page breaks, but needed something to sort the results of adding a student before running it. As for the alternative, I did grab the sortallsheets routine in case I get permission to alter the layout so each student has their own sheet. That does seem like it would be easier to maintain. I've never used a TOC, so I'll have to have a look. Each student has several classes with a total for hours spent in each class and total class time for the month, and then another page at the bottom that totals those totals for a grand total of hours spent in each class by ALL students during the month. The original file uses SUM for each grand total #, and the formulas are looonnnggg and they now have to be updated manually if a new student is added or a student is removed, so I'm thinking SUBTOTALs in place of SUMs would be better. To give you a brief picture of original layout: Page 1 Dates across row, starting in Col2 StudentName1 ClassName1 TotalHrs1 ClassName2 TotalHrs2 ClassName3 TotalHrs3 SUM Monthly Student1 class time Page 2 Dates across row, starting in Col2 StudentName2 ClassName1 TotalHrs1 ClassName2 TotalHrs2 ClassName3 TotalHrs3 SUM Monthly Student2 class time Page X Class1 SUM monthly hrs Class1 all students Class2 SUM monthly hrs Class2 all students Class3 SUM monthly hrs Class3 all students Page for each student printed monthly and filed in their chart. Page X for Utilization Review. I'm tackling simplifications one at a time, so I may be seeking others in the future g. -- David |
#16
|
|||
|
|||
David McRitchie wrote
Perhaps the example at http://www.mvps.org/dmcritchie/excel/buildtoc2.htm might be helpful I'll have a look. I emailed an example based on sketchy details you posted. Sorry, I'll never get it. Address used here is bogus. Don't know if all sheets have the same format, if a date is one day or a semester. Don't know if all students take the same classes that was my assumption; otherwise, the class report is not going to work. All sheets (pages in my single sheet scenario) have same format, dates are all weekdays in month, students take same classes. I think you might be able to do this with a couple of Pivot Tables if the data is strictly studentname, class, date Data is set up as described in my last post. Just wasn't enough room to show 25 date (C:AA) and 3 name/classname/total (A:B & AB) columns in a post. I left out 3 titles for ColA and 1 Total in ColB, though: Page 1 This: SUM Monthly Student1 class time should be Total Class Hrs SUM Monthly Student1 class time Same for Page 2 Page X Add this after last item Grand Total SUM all monthly class hours for all students I know Pivot Tables can be powerful tools, based on all the suggestions to use them in NG's, but every time I've tried to experiment, I get totally lost and confused by the results, probably because test data is from current files not suitable for Pivot Tables. I think by now I've thought through this thoroughly enough to set things up the way I need and maintain the integrity of the original file. One change I'll make is to use SUBTOTAL formulas in place of current SUM formulas in ColB. -- David |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Number of pages excluding content pages | Number of pages excluding content pages | General Discussion | 4 | January 11th, 2005 02:21 PM |
Create New Dictionary Problem Sort by Position | Jean Dophin | General Discussion | 8 | September 21st, 2004 06:33 PM |
Wrong sort order of report's details section | dsjohn_242 | Setting Up & Running Reports | 6 | July 12th, 2004 10:21 PM |
Sort forgets last sort and forgets I have header rows | medlint | General Discussion | 6 | June 19th, 2004 12:09 AM |
Can't get it to print different multiple pages | Markis Gardner | Publisher | 2 | May 10th, 2004 04:20 AM |