Excel help please

[Guy Fawkes]

I've done a spreadsheet for my results with the horses.

The columns read obvious things such as "Date", "Horse", "Profit" etc etc etc;

Date

Source

Horse

Meet

Time

Bet

Odds

Stake

Fin Pos

Result

Profit

Comm

Comm %

Returns

Bank

Profit

I'm, just wondering if it's possible to be able to extract rows depending on their source (the source is where the selection has come from eg, my initials "LT", RP tipster, another website etc etc) in order that i can put them into a different spreadsheet and be able to get some figures for each source's profitablitly rather than just the P/L for my actual bank. Doing this i hope to be able to pinpoint which tipsters give the best results etc.

Its 10 years since i did this sorta stuff in I.T. at school but i seem to think it may be possible using "VLookup" formula? But i really dont have a clue where to start.

Am i talking nonsense?

Any help would be much appreciated.

[GotaFancy?]

Vlookup will return one looked up value - to do what you want, only way I can think of is to concatenate an "ID" number (i.e. make every source unique) .... also need to find a way to make the ID number conitinuous for each source ......

A quick play, and I have done this.....

Source	ID	Concatenate	Data
Source A	1	Source A1	assada
Source Z	1	Source Z1	asdasd
Source C	1	Source C1	asdasd
Source A	2	Source A2	asdfsad
Source D	1	Source D1	daddfsf
Source C	2	Source C2	sdfdsdf

The formula in the ID cell is:
Cell A2: =COUNTIF($A$2:A2,A2)
Cell A3: =COUNTIF($A$2:A3,A3)
etc

The concatenate cell is simply:
Cell C2: =CONCATENATE(A2,B2)
etc

You can then do a VLOOKUP for each source in a seperate worksheet, for example:

Source	ID	Concatenate	Data
Source A	1	Source A1	assada
Source A	2	Source A2	asdfsad
Source A	3	Source A3	#N/A
Source A	4	Source A4	#N/A
Source A	5	Source A5	#N/A
Source A	6	Source A6	#N/A

The Data formula is:
Cell D2: =VLOOKUP(C2,Sheet1!C:D,2,FALSE)

The "Error" messages are returned where there is no data for that source and ID yet......

If you know how to use databases, then this kind of data manipulation would make a lot more sense there.......

[aliando]

i'm interested in this kind of thing also but with regards to football.

anyone know of where i can find online a simple excel worksheet in order to build up my knowledge from not much better than scratch? I'm gonna be busy over the next few months but would like to start getting somewhere with this.

cheers.

[davidmphill]

I fcuking hate excel.
It looks so simple but I can never get the fcuker to do what i want!

[GotaFancy?]

Originally Posted by aliando

i'm interested in this kind of thing also but with regards to football.

anyone know of where i can find online a simple excel worksheet in order to build up my knowledge from not much better than scratch? I'm gonna be busy over the next few months but would like to start getting somewhere with this.

cheers.

Best bet is probably to just give it a go - even if you dont manage to do what you want to do at first, you'll learn whilst trying ...... The Help in excel I find to be quite good If you get stumped, then give us a shout here and I'm sure we can help (though try yourself first - the idea is that you learn through trying, not through us doing it for you )

Try something really simple first.....

If you want to post specifics of the project first (i.e. some kind of spec) - I'll happily do it too (on a limited range of data - a lot of your work will be in getting the data!!!) and you can compare your approach with mine (another good way to learn).....

[aliando]

Originally Posted by GotaFancy?

Best bet is probably to just give it a go - even if you dont manage to do what you want to do at first, you'll learn whilst trying ...... The Help in excel I find to be quite good If you get stumped, then give us a shout here and I'm sure we can help (though try yourself first - the idea is that you learn through trying, not through us doing it for you )

Try something really simple first.....

If you want to post specifics of the project first (i.e. some kind of spec) - I'll happily do it too (on a limited range of data - a lot of your work will be in getting the data!!!) and you can compare your approach with mine (another good way to learn).....

i've done a few things and have got the basics up and running but want to look at transferring data from somewhere else onto the sheet and just have not had the time to look at it properly. Am back on nights in a month so may try and tackle it then, when i get time to sit down and solely concentrate on it.

the help is fairly good and i've used it to self-teach but having read posts of others re: transference of dat, it just sounds a lot more difficult.

will give it a try and then get back to you all when i get stuck

[GotaFancy?]

Originally Posted by aliando

but want to look at transferring data from somewhere else onto the sheet and just have not had the time to look at it properly.

Erm......don't want to sound defeatest, but if you're talking about scraping data from a website to build your own database (in Excel), then that sounds like it will be way above what you can do (and would involve programming - probably not too dissimilar from the techniques that datapunter suggests in his Java course).

If the specific data you want to look at is on a certain web page (and HTML), then you can link it through the Data/Import External Data/New Web Query option (Only in Excel 2003 I think) - but I am guessing that's not what you are looking at doing........

[georgej]

If you look in the value place section - i've put some code that scrapes their data into excel
(yes - i got permission first )

Their webpage is nice and straightforward. If i can help i will be glad to assist, but if the site keeps changing it's layout, you will need to keep changing the code - if this is what you were meaning.

As for the lookup formulas you may also want to try sumproduct (but it gives me a headache). There was a post i answered a while ago that i explained how i would do the layout and use data validation. This is handy for combining lists that you want to reference without having to type in all the names in the list repeatedly.

OK - i've said too much. Any problems, just ask GaF.

[aliando]

Originally Posted by GotaFancy?

Erm......don't want to sound defeatest, but if you're talking about scraping data from a website to build your own database (in Excel), then that sounds like it will be way above what you can do (and would involve programming - probably not too dissimilar from the techniques that datapunter suggests in his Java course).

If the specific data you want to look at is on a certain web page (and HTML), then you can link it through the Data/Import External Data/New Web Query option (Only in Excel 2003 I think) - but I am guessing that's not what you are looking at doing........

errrr techy words and i'm lost i'm afraid - i really am useless at these things .

what i was looking to do was simply transfer tables etc over to excel but thought that would be difficult (i think thats what you've said above), so was also thinking of transferring the tables/data into word and then hoping that that would be a little easier to get into excel (again, i've not looked at what tables would be easy to get into word either yet).

like i say, i've not even looked at it yet, but expecting a headache. I'm also expecting it not to work, ultimately and will just do it manually - a bit slower but i'll get there

cheers mate

[GotaFancy?]

If the tables are in an existing web site, then hopefully just a question of linking to them in Excel That shouldn't prove too difficult (if the format is right) - "technique" to use is as described in the 2nd paragraph above.

There is no advantage to trying to go through word first.

Do you know specifically what data you are looking for?

[aliando]

Originally Posted by GotaFancy?

If the tables are in an existing web site, then hopefully just a question of linking to them in Excel That shouldn't prove too difficult (if the format is right) - "technique" to use is as described in the 2nd paragraph above.

There is no advantage to trying to go through word first.

Do you know specifically what data you are looking for?

cheers - its just simple football tables. I've started working on the analysis but will need to sit down with nothing else on and get on with it at some point.

the idea is that i can transfer a table across and it will be analysed by the formulas already installed

sorry nehemiah - i seem to have hijacked your thread

[aliando]

Originally Posted by aliando

cheers - its just simple football tables. I've started working on the analysis but will need to sit down with nothing else on and get on with it at some point.

the idea is that i can transfer a table across and it will be analysed by the formulas already installed

sorry nehemiah - i seem to have hijacked your thread

oh, and the football results too...

[GotaFancy?]

The league tables are straightforward :-) Presume you are interested in current tables, and not tables at a point in time?

Results will be harder - are you looking for historical results too?

[Guy Fawkes]

Originally Posted by aliando

sorry nehemiah - i seem to have hijacked your thread

No worries Ali!

In the end of hours of playing around with GaF's advice etc (thanks v much btw mate) i got mad and did it all the old fashioned way -> manually copy and pasting into relevant new spreadsheets, won't be too bad to continue doing it liek this, now i've doen the backlog

Hope you get your problems fettled ali!

[tipstar]

Nehemiah

have you thought of using AUTOFILTER .

just highlight Date> profit click Data,Filter, Auto filter
This will give you a dropdown list in each column.
You can then view your data in the order you want, if you need to do more calcs you can cut & paste the filtered part into another sheet and work on it there, instead of cut and pasting each row one at a time.

Personally I would use a PivotTable,It all realy depends on how much you need to manipulate your data.