[ms office - 2010] Excel Formula/macro Request Please. Reorder and copy data

[tj2419]

Hi

 

I have a large table of data like the dummy data below that i am trying to reorganise by using a formula or macro (which ever is best).

 

 

The data consists of students and registration groups etc. I want to split the data into registration groups to ultimately be able to print the data with one registration group per page. Any suggestions how i would achieve this?

 

Thanks in advance.

[tj2419]

Is it possible to have some sort of formula that would carry out an operation like:

 

IF CELL IS DIFFERENT TO CELL ABOVE INSERT 3 ROWS

 

Thanks

[pcstru]

You could create a macro which would insert a pagebreak as the value of the group changes. Assuming the data is sorted and the data you want to create the page break on is in column 3, something like :

 

Sub PageBreak()

   Dim rData As Range, _
       iIdx As Integer, _
       sComp As String
           
   Worksheets("Sheet1").ResetAllPageBreaks
   
   iIdx = 1
   Set rData = Worksheets("Sheet1").Range("A2:C1000")
   
   ' We don't want a break for the first instance of the value
   sComp = rData(iIdx, 3).Value
   
   ' Loop through all data in the range while the cell is not empty    
   While rData(iIdx, 3).Value <> ""
       ' Has the grouo changed?
       If sComp <> rData(iIdx, 3).Value Then
          rData(iIdx, 1).Select
          ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
          ' Set for next group
          sComp = rData(iIdx, 3).Value
       End If
       iIdx = iIdx + 1
   Wend

End Sub

[vikpaw]

If this is out of SIMS, there is an option to group into separate sheets, so long as you've ordered by the field you want to group by, and its the last column in the returned data. This may not work exactly how you wish, if you are processing the output data after, but may do the trick. If you get an error out of SIMS then you need a new template.

 

Otherwise, try this: ASAP Utilities - Download this popular Excel add-in - Recommended by several magazines - Free edition for home use, students and charitable organizations - Excel software

 

EDIT: I think once its in sheets, it will print each as a separate page.

Edited October 30, 2011 by vikpaw
add

[Stuart_C]

Depends on how much automation you want.

 

Bassically you would highlight the whole selection of data, Sort on Reg, Surname, forname. Then go to the data tab, use "Subtotal" (towards the right end) Put in a count at each change in year group and tick the page break between each change. Job done.

 

If you struggle a bit like me you can record a macro to do this then edit it a bit to clear it up.

[mac_shinobi]

You could use the auto filter to list out the registration groups one registration group at a time and from there you could either copy them across into a new work sheet and then delete them from the original worksheet and format and then print or you could just literally set the print area on the original worksheet ( where you have the auto filter set ) and print the selected area.

 

Rinse lather repeat for the next registration groups

 

How to Filter Records in an Excel 2010 Table with AutoFilter - For Dummies