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

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

I will look at this later. It might be easier to add a column in your sheet with a formula to calculate the ratio.