Output list based on value in a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vegan@ucom.net - 20 Jan 2006 16:44 GMT

I hope someone here can help. I have next-to-zero experience with
Excel macros.

I have a large spreadsheet that was used to track workshop attendance
for several people throughout 2005. It has names in Column A and
workshop names in Row 2.

Throughout the year, if someone attended a workshop, a number was
placed next to their name under the workshop, representing the number
of hours they attended.

Now I need to somehow output to a list each persons name, ONLY the
workshops they attended, and their total hours attended.

For example: If F5 contains a value, I need to output A5, F2, F5 (that
is, Name, Workshop Name, Hours attended.)

Is this possible in Excel? Something you could walk a macro newbie
thorough?

Thanks in advance for any help you can provide.

Brett

Reply to this Message

Executor - 23 Jan 2006 12:56 GMT

Hi Brett,

I have created this macro that does the trick
In short:
It creates a new sheet where the result will come
It places headings on this new sheet.
It walks throu you sheet and copies the
- name
- workshop
- hours
to the new sheet.

Public Sub TotalsPerAttendee()
Dim lRowScan As Long
Dim lColScan As Long

Dim lRowReport As Long
Dim sCurrent As String
'
sCurrent = ActiveSheet.Name
ThisWorkbook.Sheets.Add
ActiveSheet.Name = "AttendeeReport"
Range("A1").Value = "Name"
Range("B1").Value = "WorkShop"
Range("C1").Value = "Hours"
lRowReport = 2
Sheets(sCurrent).Activate

lRowScan = 3

Do
lColScan = 2
Do
If Not IsEmpty(Cells(lRowScan, lColScan)) Then
Sheets("AttendeeReport").Cells(lRowReport, 1) = Cells(lRowScan,
1)
Sheets("AttendeeReport").Cells(lRowReport, 2) = Cells(2,
lColScan)
Sheets("AttendeeReport").Cells(lRowReport, 3) = Cells(lRowScan,
lColScan)
lRowReport = lRowReport + 1
End If
lColScan = lColScan + 1
Loop Until IsEmpty(Cells(2, lColScan))
lRowScan = lRowScan + 1
Loop Until IsEmpty(Cells(lRowScan, 1))

End Sub

Hoop this helps

Executor.

Reply to this Message

vegan@ucom.net - 23 Jan 2006 14:24 GMT

Thank you!

Worked like a charm!

Brett

Reply to this Message