• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Excel 2007 sort and group data macro

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

  • 2
1 Solution
One question: Do you want any changes in the "Inservices" sheet as well?
garrethmcadamAuthor Commented:
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.

garrethmcadamAuthor Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now