group and analyse data

event_id	menu_hint	EVENT_NAME	EVENT_DT	SELECTION_NAME	WIN_LOSE	BSP
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	5. Convoluted	0	9.953745
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	3. Loui Wonton	0	38.853743
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	1. Cherokee Outlaw	0	1.848149
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	8. Smooth Jack	1	19.109564

Hello . I have a CSV file with lots of entries like in the table above.
this is just one of them and its a greyhound race with 4 dogs in it. the winner is the one with a 1 in the WIN_LOSE column .
and there are about 80-90 of them each with 4 5 6 7 or eight dogs .
I would like to analyse those races. but I have no Idea how to do that in excel.


First I would like to know how can I see the relations between dogs , like:

what is the BSP of the favorite in every race where it didnt win . in my example the favorite would be the one with 1.84 as BSP . and the winner with 19.1

I have no idea how to .. kinda group those entries by the ID and analyse each one of them .
I hope this is not too much to post or ask ..
thanks !

this might be what I am looking for . I will learn a little bit about this pivot table and hopefully do my work . thanks you all excel wizards !

Originally Posted by Richard Buttrey

Hi,

I'd be inclined to use a Pivot Table. The attached might give you a starter.. You can of course add all the fields for a full analysis. Add slicers as appropriate to more easily filter the data.

umm. another question to you .you created another column with "Lowest BSP" . and it works . but only for a race . in my example with one race and 4 runners . in the real dataset with lots of races if I try to drag the formula down to be applied to all rows this part of your code ($H$2:$H$5,$G$2:$G$5,0) doesn't move down . and I cant figure it out how to make it work !

I did now attach a real example file of my dataset !

Power Query

Please Login or Register  to view this content.

with your help and a little bit of python I managed to do what I wanted . thanks you very much !