I am trying to automate the prodution of my schools exam timetable. The problem i have at the moment is that the exams are either a or p for am or pm. If someone is sitting more than one exam in the same a or p plot i have to assign them an order eg p1 p2 p3 and is determined by the number of people taking the exam. I therefore have columns with the candidate number, name, date. exam, a or p, number doing exam. Does anyone have a way of putting the a1 a2 p1 s2 etc into the next column so it is specific to the candidate and date and in order of number doing exam? I have also posted this on http://www.excelforum.com/showthrea... but not a response which allows me to sort it automatically. I Hope someone can help. Many Thanks Jack

sorry dun understand what you mean

Jack, Do you still need an answer to this post ? if you can live with having your base data reordered on your sheet ,you requirements can be done with a multiple sorting routine Once to find all the exams a given candidate is taking on any given date. Second to put the exam sizes in order Third to allocate a room priority depending on the size of the exam class . If you're not totally confused , you don't fully understand the question.

Hi Jack, The code below the line at the bottome of this text seems to me to do what you want based on the information .I used the example sheet you originally posed on the exceltips website. Its a macro that either needs copying into the visual basic page behind a button you create on your sheet or it can be entered in the worksheet and run manually. Copy and paste it as it is and the correct VB formatting will be restored in excel- it looks a bit weird in this post format. It basically works as follows but the code is annotated with explanations of each part. The code starts at row 2 assuming this is the first candidate number in column A . It works out how many exams in total that candidate is taking on the day. It then applies 2 sorts to that candidates data -first it sorts the exam size in descending order -then it subsorts the data again based on whether the exam is am or pm . This action will change the visual format of the data on the sheet so it appears in the sorted form. Thirdly the macro then looks at the am and pm data for that candidate and applies the priority rating A1 to the biggest exam size , then A2 etc - then it moves on to any exams the candidate is taking in the afternoon and does the same. The exam candidate number date needs to be an unbroken list since there is a section that stops the search if it finds a blank row - you could take this out if you wish I'd suggest you try the code on a test sheet before doing anything to your main data. The only thing it can't cope with is if the candidate number data is split up for the SAME date e.g if candidate 1 on date 11th Jan is formated like this 1 11th Jan 1 11th Jan 2 11th Jan 2 11th Jan 3 11th Jan 1 11th Jan ** code cant handle this without trebling the workload for me, so you need to make sure the candidate numbers are sorted first if this is likely. If its a different date e.g 12th then thats fine. Hope this helps Let me know Andrew. --------------- Private Sub CommandButton1_Click() For RowIndex = 2 To 500 ' search routine will run through rows 2 to 500 -user to change this to whatever is needed 'Initialise the variables examdate = Cells(RowIndex, 6).Value examdate1 = Cells((RowIndex), 6).Value candidateNo = Cells(RowIndex, 1).Value CandidateNo1 = Cells((RowIndex), 1).Value examtime = Cells(RowIndex, 5).Value examtime1 = Cells(RowIndex, 5).Value datecount = 0 Personcount = 0 Order = 1 Order1 = 1 'Firstly the Code looks forward to work out how many exams in total a given candidate is going to take 'on a given day to find the boundaries for the sorting section to work on. It uses column A for the candidate No 'and column F for the exam date . User needs to check and adjust this in the variable above if required. Do While examdate1 = examdate datecount = datecount + 1 If candidateNo = CandidateNo1 Then Personcount = Personcount + 1 End If RowIndex = RowIndex + 1 examdate1 = Cells((RowIndex), 6).Value CandidateNo1 = Cells((RowIndex), 1).Value examtime1 = Cells(RowIndex, 5).Value Loop 'Set the row boundaries for the selected candidates data Startrow = RowIndex - datecount endrow = Startrow + (Personcount - 1) 'This next section selects the exam data for the identified candidate and exam date ' Then sorts the data according to exam size and then AM or PM timing. Range("A" & Startrow & ":F" & endrow).Select 'sort on exam class size Selection.Sort Key1:=Range("D1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' sort on AM or PM exam Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' This section uses the sorted data and applies the priority ratings A1 ,A2 ,A3 P1 etc For i = Startrow To endrow If (Cells(i, 5).Value = "A") Then Cells(i, 7).Value = "A" & Order Order = Order + 1 End If If (Cells(i, 5).Value = "P") Then Cells(i, 7).Value = "P" & Order1 Order1 = Order1 + 1 End If Next i RowIndex = endrow ' resets the row index to the next candidate and loops back to the start. 'this last section stops the search if there is a gap in the candidate numbers in colimn A If Cells((RowIndex + 1), 1).Value = "" Then GoTo finish End If Next finish: End Sub If you're not totally confused , you don't fully understand the question.

Many thanks I have tried it out on some other data the problem i have is that i have is that my data is at least 7000 long rather than 500, I have tried changing the 500 to 7000 at the top but it does not seem to work. Should it work by changing that number or do i have to change anything anywhere else?

Hi There, yes , you are correct , changing the 500 to 7000 means it should keep going until it reaches 7000 or finds a blank row in col A. The line of code should then read; For RowIndex = 2 To 7000 Does it sort out the data in the first 500. Also , just a thought, if you have any data filters on this can upset macros. Can you be more explicit about what messages or errors you are getting? Cheers Andy If you're not totally confused , you don't fully understand the question.