Summary Report Using VB code

[lostinformulas]

HI,

I have a report that I need to summarize, here is some sample data.

Dealer Code Participant Modules
F23CH ROB MASSON 10
F23CH ROB MASSON 7W
F23CH ROB MASSON 8
F23CH ROB MASSON 9
F32C5 JOHN COUTTS 16
F32C5 JOHN COUTTS 17
F32C5 JOHN COUTTS 21
F32C8 SCOTT PLAKHOLM 7
F32C8 SCOTT PLAKHOLM 7S
F32C8 SCOTT PLAKHOLM 7W
F32C8 SCOTT PLAKHOLM 8

Desired results would be column A Dealer Code, Column B Particpant, Column C a summary of Modules taken, Column D a count of Modules

Ex:
F23CH Rob Masson 10,7W,8,9 4
F32c5 John Coutts 16,17,21 3
F32C8 Scott Plakholm 7,7s,7w,8 4

Is there a easy fix using VB code? The report is usually between 1500 lines and 2000.


Thanks for any ideas
Lostinformulas

[mrice]

Please Login or Register  to view this content.

[lostinformulas]

Martin,

Perfect, Thanks,Thanks,Thanks.

You don't know how much time you save me.


Thanks
Lostinformulas

[mrice]

Happy to help

[lostinformulas]

Martin,

If your out there this code has worked great, however, i need to add a condition. The field that the summary goes into can not be over 30 characters. Is it possible to tell the code to divide the summary and only count each column. I would need up to 5 column each for summary and count.

Ex:
dealer code participant sum count sum count sum count sum count sum count.


Any help is always appreciated.


Thank in advance
Lostinformulas

[mrice]

Not sure I fully understand this. Can you please provide an example?

[lostinformulas]

Martin

Here is a sample in a smaller version.
I need the exact cell size to except up to 30 characters in the module summary column. In the example I cut it off at 12 and I need it to go up to 5 columns for the summary and count columns.

Data: Column A-C
Results: E2:H10

Desired results below highlighted in yellow.

I hope this is clear.

Thanks
Lostinformulas

[mrice]

Try this...


Sub Test()
Cells(1, 5) = "Dealer code"
Cells(1, 6) = "Participant"
Cells(1, 7) = "Module summary 1"
Cells(1, 8) = "Count 1"
Cells(1, 9) = "Module summary 2"
Cells(1, 10) = "Count 2"
Cells(1, 11) = "Module summary 3"
Cells(1, 12) = "Count 3"
Cells(1, 13) = "Module summary 4"
Cells(1, 14) = "Count 4"
Cells(1, 15) = "Module summary 5"
Cells(1, 16) = "Count 5"



For N = 2 To Cells(65536, 1).End(xlUp).Row
Composite = Cells(N, 1) & " " & Cells(N, 2)
NeedNewRow = True
For M = 2 To Cells(65536, 5).End(xlUp).Row
NeedNewRow = True
If Composite = Cells(M, 5) & " " & Cells(M, 6) Then
NeedNewRow = False
TargetRow = M
Exit For
End If
Next M
If NeedNewRow = True Then
TargetRow = Cells(65536, 5).End(xlUp).Row + 1
TargetColumn = 7
End If
Cells(TargetRow, 5) = Cells(N, 1)
Cells(TargetRow, 6) = Cells(N, 2)
If Len(Cells(TargetRow, TargetColumn)) + Len(Cells(N, 3)) > 30 Then TargetColumn = TargetColumn + 2
Cells(TargetRow, TargetColumn) = Cells(TargetRow, TargetColumn) & Cells(N, 3) & ","
Cells(TargetRow, TargetColumn + 1) = Cells(TargetRow, TargetColumn + 1) + 1
Next N
For M = 2 To Cells(65536, 5).End(xlUp).Row
For N = 7 To 15 Step 2
If Cells(M, N) <> "" Then
Cells(M, N) = Left(Cells(M, N), Len(Cells(M, N)) - 1)
End If
Next N
Next M
End Sub

[lostinformulas]

Martin,

I tried it on two different sets of data.

#1 it put all of the summary data in one cell regardless of how many characters there were (40) counting ,

#2 I received a compile error: Syntax err (and this line was highlighted

1) +1 next N From = 2 cells (65536,5) . end (xUp).row


I copied and paste the code directly out of the email

Thanks
Lostinformulas

[lostinformulas]

attached is a screen print of the error message. Hope it helps

[mrice]

Hmmmm....

From the screen shot, it looks like a carriage return hasn't copies correctly.

The code should move onto a new line after

Next N

This will stop the code compiling.

Please let me know if this does fix the problem. The code seemed to work OK for me. Maybe there is something subtly different about the data we are using - could you send a SS with real data in it?

[lostinformulas]

Martin,

I copied the code from this thread and pasted into my spreadsheet and that seems to have fixed the problem.

The code works exactly how I needed it to.

Thank you, now I won't have to edit this report by hand.

Lostinformulas