macro to create pivot table(s) and sort out data in other worksheet

Hi,

Attached is my workbook.
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros.

I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet.

How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think.

An example of what I want the pivot table to do is:
in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5
then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way)
then do the same all over again for value in L2
etc etc etc

this should be done for the 6 values in column L, each having values up to 7 in the J column.

Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.

Thanks in advance for the help!!

This is the same as your other thread isn't it?

The difficulty here seems to be the formatting of your results sheet. Is that set in stone? Pivot tables won't give you the precise formatting but you can opt to output the results on an existing sheet.

Also, how much of this is constant - are there always values in L1:L6 or can that vary?

Formulae might be another way to go. To be honest I'm not sure which is the best approach here, and I have never automated pivot tables.

yes it is the same but the other thread is now in page 7 or 8 or something like that and I thought it was too far away to get an answer.

the row data always comes the same way. The only thing they don't give me and that I added my self is the column named Pit with all the 1s and 2s, ... which I added to help finding the data I wanted for my other macros.

I manually enter the values in L1:L6 so they will always be there yes.

the problem with formulas (that I understand as being excel code, not VBA) is that my row data is never quite the same. The number of different cars will change, the number of laps as well, the number of stints too.... and it is very very long to analysis manually, that's why I wanted macros.

And also I could be able to do with a manual pivot table but the chief engineer is using an older version of excel and fancy features are not really his thing....

For future reference you can bump threads to bring them to the top.

I realise this is only a small part of what you want done, but see if the below at least starts you off. If this is on the right track - forgive the pun - then I might see if I can finish it off.

Sub x()

Dim rTeam As Range, rPit As Range, rSector As Range, rng As Range
Dim r As Long, c As Long, r1 As Long, c1 As Long

r = 5: c = 6

With Sheets("Race Results")
    Set rTeam = .Range("C2", .Range("C2").End(xlDown))
    Set rPit = .Range("J2", .Range("J2").End(xlDown))
    Set rSector = .Range("E2", .Range("E2").End(xlDown))
    Sheets("Stint Analysis").Range("F:J").NumberFormat = "ss.000"
    For Each rng In .Range("L1", .Range("L1").End(xlDown))
        For r1 = 0 To 21 Step 3
            For c1 = 0 To 2
                Sheets("Stint Analysis").Cells(r + r1, c + c1).FormulaArray = _
                "=AVERAGE(IF(('Race Results'!" & rTeam.Address & "=" & rng.Value & ")*('Race Results'!" & rPit.Address & "='Stint Analysis'!$B" & r + r1 - 1 & "),'Race Results'!" & rSector.Offset(, c1).Address & "))"
            Next c1
        Next r1
        r = r + 24
    Next rng
End With

End Sub

wow!! it worked soooooo well!!!
I just put the code in at the right place, pressed my macro command button and it was all there!!

That's amazing!! Thank you sooooo much!!

I will try to finish up everything from now, meaning do the same to find the minimum values and stuff and also try to modify a bit what you just gave me... maybe you have seen the results given are a bit different from the results expected sometimes because there is a condition on using the times in the average (that's why it's called avr green, so all sectors done under safety car or during out laps are ignored because too long and not representative of the actual performance of the cars)

I know I am trying to complicate even more but that's what I need!

I'll get back to you if it goes wrong but I think I can take it from here now.

thank you so much again!!! You've just made my day :D

Good, glad it worked for you. I've actually done a little more including the minimum times and some formatting. Come back if you get stuck, otherwise good luck.

Sub x()

Dim rTeam As Range, rPit As Range, rSector As Range, rng As Range
Dim r As Long, c As Long, r1 As Long, c1 As Long

r = 5: c = 6

Application.ScreenUpdating = False

Sheets("Stint Analysis").Range("F:J").ClearContents

With Sheets("Race Results")
    Set rTeam = .Range("C2", .Range("C2").End(xlDown))
    Set rPit = .Range("J2", .Range("J2").End(xlDown))
    Set rSector = .Range("E2", .Range("E2").End(xlDown))
    
    For Each rng In .Range("L1", .Range("L1").End(xlDown))
        For r1 = 0 To 21 Step 3
            For c1 = 0 To 2
                With Sheets("Stint Analysis").Cells(r + r1, c + c1)
                    .Offset(-1).FormulaArray = _
                        "=MIN(IF(('Race Results'!" & rTeam.Address & "=" & rng.Value & ")*('Race Results'!" & rPit.Address & "='Stint Analysis'!$B" & r + r1 - 1 & "),'Race Results'!" & rSector.Offset(, c1).Address & "))"
                    .FormulaArray = _
                        "=AVERAGE(IF(('Race Results'!" & rTeam.Address & "=" & rng.Value & ")*('Race Results'!" & rPit.Address & "='Stint Analysis'!$B" & r + r1 - 1 & "),'Race Results'!" & rSector.Offset(, c1).Address & "))"
                End With
            Next c1
        Next r1
        r = r + 24
    Next rng
    With Sheets("Stint Analysis").Range("F:J")
        .Font.Name = "Arial"
        .Font.Size = "8"
        .NumberFormat = "ss.000"
        .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    End With
End With

Application.ScreenUpdating = True

End Sub

I managed to add a condition in the formula array but it does not really give me what I want... but at least it gives me something! lol

Here what we had:

.FormulaArray = _
                        "=AVERAGE(IF(('Race Results'!" & rTeam.Address & "=" & rng.Value & ")*('Race Results'!" & rPit.Address & "='Stint Analysis'!$B" & r + r1 - 1 & "),'Race Results'!" & rSector.Offset(, c1).Address & "))"

Here what I have now:

.FormulaArray = _
                        "=AVERAGE(IF(('Race Results'!" & rTeam.Address & "=" & rng.Value & ")*('Race Results'!" & rPit.Address & "='Stint Analysis'!$B" & r + r1 - 1 & ")*('Race Results'!" & rSector.Offset(, c1).Address & "<1.2*'Race Results'!" & rSector.Offset(, c1).Address & "),'Race Results'!" & rSector.Offset(r + r1 - 1, c1).Address & "))"

My problem is instead of giving me a smaller average value because the biggest values in the average were taken away it gives me a bigger value...

Does someone see my mistake please??

Thank you

I've attempted something but haven't come up with the same averages as you, which may mean I haven't fully understood.

In the first sheet of the attachment I've added a formula in cols M-O (for each of the sectors) which puts a 1 for the first lap of each driver and also if any lap time is more than 1.2 times the previous one.

I've then amended the array formula in the code to exclude any 1s in those columns.

Where have I gone wrong?

nowhere
it was the ratio I gave you.... oops it is more 1.015 or so than 1.2... 1.2 was not excluding all the laps manually excluded in the other attachment.

sorry for the trouble...

was it not possible to just add a condition in the formula array instead of having to add those columns?

Thinking about that makes my head hurt. Some of the formula whizzes here may be able to, but afraid it's beyond me.

What I could do is add and then remove the formulae in the code so it's all automated.

it's fine don't worry
thanks for everything again

I import data into Excel 2007 regularly and create a pivot table with several columns. I have been copying the sheet in to a template spreadsheet, refreshing the data and then copying the updated pivot table data back to the original spreadsheet. I would like to learn how to create a macro that creates the pivot table for me. I’ve looked at several examples on the forum, but it is hard to see what has been done. I create macros regularly, but only by using the record macro feature in Excel. When I did this with my data, I got an error immediately after a new sheet was created. The part that was highlighted in yellow in VBA below it is the line starting with "ActiveWorkbook". My columns heading will be static, but the number of rows will change. I want to create a simple pivot table with columns G though Z.

Sub Macro3()
'
' Macro3 Macro
'

'
Columns("M:V").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C13:R1048576C22", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet9!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet9").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date of Loss")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("$ Res"), "Count of $ Res", xlCount
End Sub