Renaming excel columns

[wb]

I thought this would be simple but I can't seem to do it!

How do I rename the columns in Excel (2007)? I want them named something other than A, B, C etc. I tried right clicking but no joy.

[mathare]

You can't rename them. You can use one of the rows as column headers though.

What are you trying to do exactly?

[wb]

It's for my betting records.

Basically, as the records build, it can get confusing as to what information goes in each column and I sometimes have to scroll up. If you have a look at the screen shot for example, you will see some figures in column M.

This happens to be the betfair SP. But I forgot what the column was for, and had to scroll up to look at the heading I have in Cell M1 which states 'Betfair SP'

Scrolling back to the top of the page can be time consuming.

Instead of being called Column M, it would be helpful if I could glance up and see 'Betfair SP' instead.

The same goes for the other columns. Hope that makes sense.

[mathare]

Set row 1 to the column names you would have used, select all of row 1 then Freeze Panes. This makes row 1 stay at the top of the screen so when you scroll down you're only scrolling rows 2 onwards. So in the screenshot you'd see row 1, then row 22, 23, 24 etc.

[wb]

Set row 1 to the column names you would have used, select all of row 1 then Freeze Panes. This makes row 1 stay at the top of the screen so when you scroll down you're only scrolling rows 2 onwards. So in the screenshot you'd see row 1, then row 22, 23, 24 etc.

:thumbs

Problem solved. Thanks Mat.

[fragle123]

In excel 2007 you could also go to the View tab and just above col M you will see a freeze panes box click on the little arrow and select freeze top row.

[wb]

Another excel question...

I have a column (see attached example) with ratings for each horse in A.

Some horses have a symbol beside the rating (eg: the letter p means the horse may improve). I need to separate the symbol from the rating and put the symbols into column B.

I tried the 'text to columns' option but can't figure it out.

The data is screen scraped daily so manually inputting it would take too long.

[mathare]

You may need 3 columns for this depending on how you want to format it. If you want the ratings column to be the numerical values only and a separate column of symbols (p, + etc) then you need three columns. If you're happy for the rating column to contain the symbol too, so you have duplication of information (the symbol) then two columns will suffice.

Assuming the ratings, including symbols, are in column A, enter the following in column B:
=IF(ISERROR(FIND(" ",A2)), A2,LEFT(A2,FIND(" ", A2)-1))
And in column C enter:
=IF(ISERROR(FIND(" ",A2)), "",RIGHT(A2,1))

If A2 is "99 p" this will give you 99 in B2 and "p" in C2

[wb]

I can't give you more rep Mat, but that's exactly what I wanted. Thanks for your time once again, very much appreciated.

[mathare]

If you don't want that second column of just the numbers then put the second formula into column B; it doesn't depend on anything in the first formula so can be used entirely independently

[wb]

Another excel question I've tried and failed to figure out for myself... :(

Each day, I screen scrape some horse names and put them into column A.

In column B, I screen scrape their rating (if any). So column A has the horse name, and column B has their rating.

Now, not every horse actually has a rating - so I need excel to figure out what percentage of the field is rated.


So for example, if there were ten horses in Column A, and eight of them had a rating (in column B), the answer should be 80%.

Obviously, I can do this manually but I need to do it for every race so that's not an option.

So basically, I want excel to count how many horses are in column A. It also has to count how many horses have a rating in column B. Then it must express the percentage.

This is kinda hard to explain.

[vegyjones]

EIf horse with no rating have a blank space in column B,
then you can get excel just to count how many rows column B have an entry in them. From there you can get it to give you a a percentage.

Not sure how you would get it to distinguish between different races though persuming your spreadhseet is as the copied page above.

[wb]

Some of them have no rating, some have a dash (which, for my purpose means no rating).

So for example, have a look at today's 2.15 at Tipperary.

You can see that there are 13 horse names. These will get scraped and pasted into column A.

You will also see that six horses are rated. These ratings will go into column B.

In other words, 46.15% of the field are rated. I need excel to do this calculation when I copy and paste the names and ratings in to the columns.

[mathare]

How are the races arranged on the sheet? Does column A have the horse names for just one race or many races (with blank rows between them)?

[wb]

How are the races arranged on the sheet? Does column A have the horse names for just one race or many races (with blank rows between them)?

Hi Mat, it's a number of races (usually separated by two blank rows).

[wb]

But if it's easier to do something with A:A I don't mind doing them separately.

[mathare]

Can you post an example sheet with a few races in for me to play with please?

[wb]

Here you go Mat, the first three races from Fairyhouse.

What I need displayed (perhaps in column D) opposite each race is the following:


Number of runners
Number of horses with a rating
The % of the field that is rated


And if it's not too much trouble :pk1 I could also do with a cell displaying the difference between the top rated horse and the second rated horse (so for example the difference between the top rated in race one (108) and the second rated (92) is 16.

And finally, if you want to PM me an address, I've a crisp £20 note from the last time I visited England. You can have it as a thanks for all the help!

[wb]

attachment!

[mathare]

OK. I guess the first question I need to ask is whether I can use VBA macros or would you prefer it all done with in-cell formulae?

[wb]

I'd prefer cell formulae but whatever works is fine. Basically, I need to open the file each day and copy and paste in. I don't mind running macros if necessary.

[mathare]

It's an absolute doddle with a couple of simple macros but could be a real problem with cell formulae, basically. I'll post up a quick example shortly and you can fine-tune as necessary from there

[mathare]

Try this for starters.

I can easily move around/reformat the information displayed but for each race you have:

First row in column C = difference between top rated and second rated
First row in column D = number of runners
Second row in column D = number of horses in race with rating
Third row in column D = percentage of field with a rating

The macro runs in a split second and I can eaily talk you through it line by line if you want to know more about what it does. It can easily be extended or amended to do pretty much anything you want. Just let me know.

[wb]

Well that's pretty much the information I want Mat. It sounds like the macro is the easiest option so I'll go with that if possible.

[mathare]

I can't think of an easy fool-proof way to find the end of a race using formulae whereas it's dead simple with a macro.

The macro I have written and included in the above file will do all the races on a sheet regardless of how many there are.

[wb]

Mat, I've ran it and fiddled around with a few examples. That's absolutely perfect for what I want to do:thumbs

PM me that address as I'll be at the post office in the morning.

[mathare]

PM me that address as I'll be at the post office in the morning.I can't do that - what I have just done took me about 10 minutes so isn't worth anything like £20. Not that I would ever charge for anything like that; it's not in my nature. Hang on to the cash. I'm sure you'll be over on these shores at some point in the not too distant future and will find a good use for it then. :)

[wb]

You're a good man Mat.

[vegyjones]

I taught him everything he knows! :D

[wb]

Just wondering if anyone knows if Excel can automatically count winning and losing runs?

Let me explain.

On a particular strategy, I already have it counting number of bets, number of winners etc (by using the countif function) so I don't have to update my stats on SR, ROI etc myself.

But I'd like to have it calculate sequences of winners and losers.

Is this possible? Basically, as part of my stats, I want to add:

'longest sequence of winners in a row' and 'longest sequence of losers in a row'.

The spreadsheet knows it's a winner if I put 'Win' in the cell. Otherwise I put the position the horse came home and the spreadsheet knows it's a loser.

[mathare]

It's simple with VBA but I don't know of a way of doing it otherwise

[wb]

Another one :yikes:

In one of my columns, I write in the type of race that it is. Eg, it might say something like 'Handicap Hurdle'.

At the moment, I type in the race description manually every day. It would be handy if I could just copy and paste the full race titles from the RP or SL - but that's no good for filtering. I like to isolate certain race types and look back through my records on them. If I copied and pasted the full race titles, how could I filter a certain race type?

It's taking me ages to type the race descriptions in every day and there has to be a better way.

I tried a sample today and copied some race titles (see attached file). Would it be possible to display the rows that only have a certain word in them? (a word such as 'maiden' or 'handicap' ).

I tried the 'find' function which can find the word but it doesn't display them in isolation.

So what I'm saying is this: Is there any way to ask it to display only the rows that have the word 'handicap' for example.

[wb]

Ah wait, I've just discovered the 'text filters' option :splapme

[buddhabee]

Create a new Excel worksheet in your workbook and call it setup.

Make a list of all the races types you want starting in A1 going down the page.

Once you've done that, highlight all the entries in the column and in the little box on the top left hand side (that normally shows which cell you're in) type 'racetype'.

(From here I'm relying on my memory! I use Open Office now!)

On your working sheet click on a cell in the relevant column (the Race Type column, I suppose), there's an option called 'Validate...' from one of the menus (Data, I think).

There's a Custom List option. If you enter in 'racetype', it'll recognise that you want to use the 'racetype' range you set up earlier. And in that cell will be a drop down list with your race types.

That way you don't have to be concerned about spelling mistakes. And you can filter on that too.