+ Post New Thread
Results 1 to 6 of 6
Office Software Thread, Excel Formula/macro Request Please. Reorder and copy data in Technical; Hi I have a large table of data like the dummy data below that i am trying to reorganise by ...
  1. #1

    Join Date
    May 2011
    Location
    United Kingdom
    Posts
    440
    Thank Post
    112
    Thanked 13 Times in 13 Posts
    Rep Power
    8

    Excel Formula/macro Request Please. Reorder and copy data

    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).

    Screen Shot 2011-10-30 at 08.51.14.png

    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.

  2. #2

    Join Date
    May 2011
    Location
    United Kingdom
    Posts
    440
    Thank Post
    112
    Thanked 13 Times in 13 Posts
    Rep Power
    8
    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

  3. #3


    Join Date
    May 2009
    Posts
    2,502
    Thank Post
    200
    Thanked 635 Times in 486 Posts
    Rep Power
    229
    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 :

    Code:
    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

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,601
    Thank Post
    640
    Thanked 1,309 Times in 1,091 Posts
    Rep Power
    337
    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.
    Last edited by vikpaw; 30th October 2011 at 01:05 PM. Reason: add

  5. #5

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,666
    Thank Post
    119
    Thanked 126 Times in 102 Posts
    Rep Power
    45
    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.

  6. #6

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,244
    Thank Post
    2,785
    Thanked 937 Times in 877 Posts
    Rep Power
    343
    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

SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 06:18 AM
  2. [MS Office - 2007] Excel formulas and formatting not working
    By reggiep in forum Office Software
    Replies: 2
    Last Post: 6th October 2009, 02:21 PM
  3. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 05:31 PM
  4. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM
  5. Replies: 4
    Last Post: 25th December 2006, 09:15 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •