Excel 2007 sort and group data macro

Posted on 2009-04-22
Last Modified: 2012-05-06
I'm not too familar with excel or macros but i think i need one to format the attached Excel spreadsheet (original.xlsx) to convert and change the layout of the data to look like the second attached spreadsheet (new.xlsx)

The 'transpose' function for converting Columns to Row and vice versa is not doing what i need it to.

Essentially, the original has columns laid out like this:
Associate Name, Associate ID, Community, Hire Date, Job Title, CNA Status, State, course name, course name, course name, course name...
Course names are columns and the dates represent when they were completed

I just need all the data in rows so it can be read by a script

The course names are column heading and for each 'course name' column the value in the row is a completion date
I would like to display for each student in rows for each course like this so that the student record is repeated in rows for each course listed:
Associate Name, Associate ID, Community, Hire Date, Job Title, CNA Status, State, course name, completion date, e.g.,
Annie Newcomb, 000202849, 3310, 2/8/2002/ RA, yes, NC, Diversity, 1/14/03
Annie Newcomb, 000202849, 3310, 2/8/2002/ RA, yes, NC, Compliance, 1/14/03
Annie Newcomb, 000202849, 3310, 2/8/2002/ RA, yes, NC, Sexual Harrassment, 1/16/04, etc.

Is there a way to achieve this?

Any help is appreciated

Question by:garrethmcadam
    LVL 12

    Expert Comment

    One question: Do you want any changes in the "Inservices" sheet as well?

    Author Comment

    Hi sstampf,
    No, I do not want any changes on the "inservices" sheet.
    I just sent those as examples and didn't realize that there was a second sheet.


    Accepted Solution

    Because all the sheets will have different name
    it would be cool to convert the name to 'Sheet1' before the loop
    i need to figure out how to do that :)
    Sub generatefile()
    Dim i, x, j, r, k As Integer
    Dim strFileName As String
    strFileName = "C:\Temp\Output.xls"
    Dim xlw_source As Excel.Workbook
    Dim xlw_dest As Excel.Workbook
    Dim xls_source As Excel.Worksheet
    Dim xls_dest As Excel.Worksheet
    Dim xlr_source As Excel.Range
    Dim xlr_dest As Excel.Range
    Set xlw_source = Excel.ActiveWorkbook
    Set xlw_dest = Excel.Workbooks.Add
    xlw_dest.SaveAs Filename:=strFileName
    Set xls_source = xlw_source.Worksheets("Sheet1")
    Set xls_dest = xlw_dest.Worksheets("Sheet1")
    k = InputBox("How many rows in this table?")
    With xls_source
        r = 1
        For x = 2 To k  '1 for each row
             For j = 8 To 28  '1 for each column with data
                For i = 1 To 7  'column
                   xls_dest.Cells(r, i) = .Cells(x, i)
                xls_dest.Cells(r, 8) = .Cells(1, j)
                xls_dest.Cells(r, 9) = .Cells(x, j)
                r = r + 1
    End With
    Set xlw_dest = Nothing
    MsgBox strFileName & " saved"
    End Sub

    Open in new window


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now