Excel 2003: creating complex report

[nrschmid]

Attached is a spreadsheet. I need a report, if it is even a report, that shows for each transferable skill (top row), which projects (in Column A) I completed (based on which cells have an X).

So looking at the first two columns, I would like the information grouped as follows:

3D Modeling

Streetscape Design Guidelines. City of Wichita, KS
Commerce Street Arts District. City of Wichita, KS
Community Park Ball field Sportsplex. City of Country Club Hills, IL
Copper Slough Planned Unit Development. Champaign, IL
Fireman's Park Master Plan. Elk Grove Park District. Elk Grove, IL
Kendall Farms Recycling Facility. Plano, IL
Kostner Park Master Plan. Midlothian Park District. Midlothian, IL
Landscape Guidelines. Village of Minooka, IL
Landscape Ordinance Update Village of Montgomery, IL
Lemont Park Master Plan. Lemont Township, IL
St. Francis Street Improvements. City of Wichita, KS
Stevenson Park. Burbank Park District. Burbank, IL
"Developing and Implementing Streetscape Design Guidelines in Downtown Wichita"
Udall Park Master Plan. Elk Grove Park District, IL
US-36 Access Management Plan. Kansas DOT
Willow Park Master Plan. Northfield Park District, IL Northfield, IL

Branding:

Streetscape Design Guidelines. City of Wichita, KS
** Label and Jewel case Designs. Comprehensive Plan Update. City of El Dorado, KS
** Label Design. Streetscape Design Guidelines
Interpretative Signage. Naperville Park District
Advice on branding strategies / logo revision
Marketing material
Mock website
Portfolio ** Label and Digipak Designs (4 Panel Die Cut Screened in Color on 30% Recycled Paper)
Marketing / networking
Dining-In (program design, presentation, budget)
Newsletter revisions


How can this be done? Is this a tall order? Can I even have it put into a Word Document or is that pushing it? Let me know what I need to do.

[stanleydgromjr]

nrschmid,

See if this is what you are looking for. The macro will create a new worksheet Report.


Detach/open workbook CreateReport w1 wR VP - nrschmid - EF784488 - SEG12.xls and run the CreateReport macro.


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CreateReport()
' stanleydgromjr, 07/16/2011
' http://www.excelforum.com/excel-general/784488-excel-2003-creating-complex-report.html
Dim w1 As Worksheet, wR As Worksheet
Dim LC As Long, a As Long, NR As Long
Dim c As Range, firstaddress As String
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Report!A1)") Then Worksheets.Add(After:=w1).Name = "Report"
Set wR = Worksheets("Report")
wR.UsedRange.Clear
LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
NR = 0
For a = 2 To LC Step 1
  NR = NR + 1
  With wR.Cells(NR, 1)
    .Value = w1.Cells(1, a)
    .Font.Bold = True
  End With
  firstaddress = ""
  With w1.Columns(a)
    Set c = .Find("X", LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        NR = NR + 1
        wR.Cells(NR, 1) = w1.Cells(c.Row, 1)
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
  NR = NR + 1
Next a
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the CreateReport macro.

[martindwilson]

its easy enough to get into adjacent columns

[6StringJazzer]

Here is a revision of your file with a macro that will generate a Word file, which will be saved to the same directory as your Excel file.

Go the new sheet Sheet2 and press the button. You will see a Word window come up, and stay blank while the macro runs (1-4 minutes, depending on your machine). When it's finished you will see the completed document.

It is possible you may need to do some configuration if you get errors when doing this. If so, from Excel hit ALT-F11. This will open the VBA development window. From the menu, select Tools, References. Scroll down the list until you see Microsoft Word 12.0 Object Library (your version number will probably be different) and check the box. Hit OK, then close the window.

Go back to Excel and try again.

[nrschmid]

Thank you very much everyone! This works fine. It is exactly what I needed.