Deleting multiple entries in betting-odds Data

[Bosun]

Hey,

I currently have about 60 .csv files with soccer odds data that I need to "clean". Please see my raw data in the attached file.

What I want to do is to delete all entries that have "IP" in column P. This isnt to hard, and Ive been able to write a code to do so. The next step is more tricky though. My data includes all matched bets on a certain football match. I need to slim this down to one single price. This means that for each "SELECTION" in column I, I either want to delete all multiple entries except the one with the highest value in column L. Or I want to merge all the entries to one row which is a weighted average of the odds at different volumes.

The last approach is the most tricky one, so I would be happy with the first approach, i.e. deleting all multiple entries except the largest one.

Would anyone be able to assist me with writing this code? Ideally it would first delete all entries that has "IP", then delete all multiple entries in column I except the one with the largest value for column L.

I have no idea how to write this code

[Siddharth Rout]

Welcome to the Forums :Waves:

When you say delete, Do you mean clear the cell?

[westconn1]

you want to do this in excel?

in excel you can use worksheet functions to return the average, using sumif / countif
or there may be better alternatives

[Bosun]

I wish to delete the rows, so I end up with a excel file with one quote for each outcome, in each game.

E.g. for a game like ManUtd - Blackburn I will only have three rows, showing the highest matched quote for ManUtd to win, the draw and Blackburn to win.

Does that make things more clear?

I wish to use VBA to write a code to automate the process. I could do it "manually", but that would take me a lot of time given I have 50-60 files to go through...

[Siddharth Rout]

Ok I got it...

Here is one approach...

If you know how to record macros (If you don't then see Rob's tutorial in Office FAQ's)

1) Use autofilter to delete all entries that have "IP" in column P
2) Sort the data based on Col I.
3) and then loop through rows to delete rows which have duplicate entries in Col P. You can use the Max() function to find out which cell has the max value in Col L and not delete that...

Well, give it a try... If you are stuck then simply post the code that you have tried and we will definitely help

[Bosun]

Sounds great! I'll give it a try tomorrow morning.

I use the macro recorder to record the initial statement, and then edit the code to write the "delete loop"? Right?

Ill definately run in to trouble, as Im pretty retarded with VBA, so I'll get back to you tomorrow.

Thanks for your help

[Siddharth Rout]

I use the macro recorder to record the initial statement, and then edit the code to write the "delete loop"? Right?

You got that right

Also since you mentioned that you will have to do that with 50-60 files then I also recommend going through Si_the_Geeks tutorial on how to work with Excel Application (You will see a link in his signature). That will make you job easier... Well this will come later... First see how it works for a single file