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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Thank you for everything StephenR!

If I may ask for a bit more, is it possible with an array formula to simply copy data to another place?

This is for a new sheet in my workbook,
I need to create a macro that will, once again search for a car number and then a pit number, but then I "simply" needs it to copy all the lap time cells corresponding to a specific column in a third sheet "Stints" and for each pit number to use the next column (so 7*6 columns from B to AQ + column A which has lap number in it)

I have been trying quite a few things this afternoon, from the code you already gave me before and other stuff as well but I either get codes which run indefinitely or zeros all over the sheet.... something is obviously wrong and I cannot find what....


Thank you so much again for all your help

If that is a separate question, it might be better to start a new thread for it.

On your original question, could you add to your attachment some sort of illustration of how the ratio works? I've not had a chance to look at yet, but that may help to expedite matters once I do.

ok I will do sorry.

for the ratio:

the code you gave me does the average of the sector times.

However some values are too high to be representative of the cars' performances: it happens during out laps (the car leaves the pit lane) and during the safety car laps when the cars are all queued up behind the safety car, driving slowly for safety reasons (debris on track, crashed car in the way...)
so I would like to take those values out of the average.

Until now all the analysis was done manually so I was just choosing which laps to ignore or take.

because slow is dependent on the circuit and type of racing you are doing, I cannot put a value on it.
so I had in mind to compare values with each other.
ignoring the very first lap available for each car (too slow) I imagined each value could be compared to the previous one and if about similar (inferior to the value * a ratio I need to define) it would be included in the average, otherwise ignored.

usually a safety car is about slower by a third of a lap time but it varies a little and out laps are usually faster than safety cars. That's why I came up with a 1.2 ratio with no calculations done for now, I can adjust the value later easily.

I looked at some info on array formulas and I have seen conditions of inferiority/superiority in them but when I tried it did not really work... I think there are just too many things in the arguments and I have not yet completely understood it.

I have attached the manual workbook so you can see a bit what was done.

(it also shows everything else I still need to make automatic )

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:

Please Login or Register  to view this content.

Here what I have now:

Please Login or Register  to view this content.

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