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

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

thank you

I made it work to fill in the lap times and max and avr speeds with the right formating.

however when I try to add a condition on the sector times for the average, it does not give me any error but nothing happens anymore in the cells.

Could you please tell me how and where to add the condition saying that if a sector time is superior to the previous sector time (in rows) multiplied by a factor 1.2 then it should not be included in the average?

Thanks in advance!!

(I am not sure 1.2 is the right coefficient but this is just a bit of tuning to do afterwards.)

can you please send me your sheet with this macro enabled i hav to work on something similar