RATING changing Values in a data block

[Robert_Kroschel]

I have been trying to sort this one out for days now and mind you I am not an Excel power user.
The problem may even be the way I am phrasing the question to myself.
I would like to scan this data block for all the participants numbers an show in a summary a total value for each participant number (I cant use names because there are thousands of them so I just want to use a number to represent the participant. It has to be a number. So the test columns values need to be text values.
Each participant will be assigned a number of points from the cell in the points column in its corresponding points cell.

Eg Test 2 row 1 participant 5 is assigned 3 point and in test3 participant 5 is assigned 4 points

I would like excel to pull every occurrence of the participants numbers (without me typing in a if= value ) out of the data block and total the points assigned to them and them display it in a summary area in a format such as Participant No5 = 7 Points Participant 2 = 3 points and so on until all participants in the data block have had their points added up.
This process will be repeated many, many times , so I just want to be able to punch in the participant number in in any column in any cell and the summary will add up the points for me.
No participant number will be repeated in any column. But the may be repeated across the tests . Like below
Ie the number 1 can only appear once in test1 column but can also appear in test3 column (but only once in that column)

Can someone please help me with this its driving me batty , I though it should be simple but given the dynamic nature of the data in the data block it has turned out to be quite a challenge for me.​

[TABLE="width: 384"]
<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]test1[/TD]
[TD="width: 64, bgcolor: transparent"]points[/TD]
[TD="width: 64, bgcolor: transparent"]test2 [/TD]
[TD="width: 64, bgcolor: transparent"]Points[/TD]
[TD="width: 64, bgcolor: transparent"]test3[/TD]
[TD="width: 64, bgcolor: transparent"]Points[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: #C0C0C0"]3[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: #C0C0C0"]3[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: #C0C0C0"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #C0C0C0"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #C0C0C0"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #C0C0C0"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: #C0C0C0"]1[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: #C0C0C0"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: #C0C0C0"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #C0C0C0"]0[/TD]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: #C0C0C0"]0[/TD]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: #C0C0C0"]1[/TD]
[/TR]
</tbody>[/TABLE]


Regards Robert.

 

[ranman256]

on the data sheet , run SumTestData.

Sub SumTestData()
Dim iCols As Long, iRows As Long, c
Dim shtSrc As Worksheet, shtTarg As Worksheet
Dim sLtr1 As String, sLtr2 As String


Set shtSrc = ActiveSheet
Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
iCols = ActiveSheet.UsedRange.Columns.Count


Sheets.Add
Set shtTarg = ActiveSheet
Range("A1").Value = "Particiapant"
Range("B1").Value = "Points"


shtSrc.Activate
For c = 1 To iCols Step 2
   sLtr1 = getColLtr(c)
   sLtr2 = getColLtr(c + 1)
   
   Range(sLtr1 & "2:" & sLtr2 & iRows).Copy
   shtTarg.Activate
   FindNextFreeRec
   PasteIt
   
   shtSrc.Activate
Next
PivotTbl shtTarg


Set shtSrc = Nothing
Set shtTarg = Nothing
End Sub
Private Sub NextRow()
ActiveCell.Offset(1, 0).Select   'next row
End Sub


Private Sub FarDown()
    Selection.End(xlDown).Select
End Sub


Private Sub FarRight()
    Selection.End(xlToRight).Select
End Sub


Public Sub FindNextFreeRec()
Range("A1").Select
Select Case True
   Case ActiveCell.Value = ""
   Case ActiveCell.Offset(1, 0).Value = ""
        NextRow
   Case Else
        FarDown
        NextRow
End Select
End Sub
Public Sub PasteIt()
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub


Public Function getColLtr(ByVal plColNum As Long)
Dim i As Integer
Dim vAddr


vAddr = Cells(1, plColNum).Address
i = InStrRev(vAddr, "$")
getColLtr = Mid(vAddr, 2, i - 2)
End Function


Sub PivotTbl(pvShtSrc As Worksheet)
Dim r As Long
Dim shtTarg As Worksheet


pvShtSrc.Activate
Range("A1").Select
r = ActiveSheet.UsedRange.Rows.Count


    Range("A1:B" & r).Select
    Sheets.Add
    Set shtTarg = ActiveSheet
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pvShtSrc.Name & "!R1C1:R" & r & "C2", Version:=6).CreatePivotTable TableDestination:=shtTarg.Name & "!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    shtTarg.Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Particiapant")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Points"), "Sum of Points", xlSum
    
Set shtTarg = Nothing
End Sub

 

[Robert_Kroschel]

Thank you for your response ranman256.
As I said I am not an excel power user.
I don’t completely understand the code above but from the little I can decipher are you suggesting I write the above code as a vba script and assign it to a button to excecute it every time I make the caluculations on the array ?

 

[ranman256]

I cannot reply to your email because yours box is full....but
The code scoops up all data on the sheet, puts it all into 1 sheet column , then runs a pivot to produce the stats.

now you can put the code in 1 workbook. in vbe (Alt-F11),
insert Module
then in that module , paste the code.
then run SumTestData from the macros.

Is this what youre asking?

 

[Robert_Kroschel]

I cannot reply to your email because yours box is full....but
The code scoops up all data on the sheet, puts it all into 1 sheet column , then runs a pivot to produce the stats.

now you can put the code in 1 workbook. in vbe (Alt-F11),
insert Module
then in that module , paste the code.
then run SumTestData from the macros.

Is this what youre asking?

Thanks again ranman for you help
I have inserted the module and it works the issue is that it creates a new worksheets every time i run the module.
What I am hoping to achieve is to have the results on the same sheet.
There will be approximately 10 Event tables on the sheet and and I would like a summary along side each table on the same sheet . I understand that I may need to have a separate module for each table. I can create a button along side each table to calculate the results and hook the macro to that button to run the macro.
The worksheet would look something like this.
[TABLE="width: 762"]
<colgroup><col><col><col><col><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD]Event[/TD]
[TD]Paticipant No[/TD]
[TD]Points[/TD]
[TD]Participant No[/TD]
[TD]Points[/TD]
[TD][/TD]
[TD]Summary Event 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Participant [/TD]
[TD]Points[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="colspan: 2"]

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event[/TD]
[TD]Paticipant No[/TD]
[TD]Points[/TD]
[TD]Participant No[/TD]
[TD]Points[/TD]
[TD][/TD]
[TD]Summary Event 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Participant[/TD]
[TD]Points[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]Event[/TD]
[TD]Paticipant No[/TD]
[TD]Points[/TD]
[TD]Participant No[/TD]
[TD]Points[/TD]
[TD][/TD]
[TD]Summary Event 3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Participant[/TD]
[TD]Points[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: left"]

<tbody>
[TD="align: right"]4[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]