[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • 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




Original.xlsx
Newl.xlsx
0
garrethmcadam
Asked:
garrethmcadam
  • 2
1 Solution
 
sstampfCommented:
One question: Do you want any changes in the "Inservices" sheet as well?
0
 
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.


0
 
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)
            Next
            xls_dest.Cells(r, 8) = .Cells(1, j)
            xls_dest.Cells(r, 9) = .Cells(x, j)
            r = r + 1
 
         Next
         
    Next
    
End With
 
 
xlw_dest.Close
Set xlw_dest = Nothing
 
MsgBox strFileName & " saved"
 
End Sub

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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