Two Plus Two Publishing LLC
Two Plus Two Publishing LLC
 

Go Back   Two Plus Two Poker Forums > >

Notices

Other Other Topics Discussion of arts & entertainment, pop culture, food & drink, health and exercise, fashion, relationships, work, and just about anything else in life except poker, sports, religion and politics.

Reply
 
Thread Tools Display Modes
Old 09-13-2010, 10:18 AM   #76
killa
huge dick
 
killa's Avatar
 
Join Date: Oct 2004
Location: NYC
Posts: 18,956
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by d10 View Post
I don't use Pivot Tables either. As OP mentioned, VBA provides so much more flexibility I can't imagine a situation I would ever use one.
thats not the point dont start an ask me thread about "using MS Excel" and not know how to use a part of it
killa is offline   Reply With Quote
Old 09-13-2010, 10:22 AM   #77
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by killa View Post
thats not the point dont start an ask me thread about "using MS Excel" and not know how to use a part of it
zomg is offline   Reply With Quote
Old 09-13-2010, 10:25 AM   #78
killa
huge dick
 
killa's Avatar
 
Join Date: Oct 2004
Location: NYC
Posts: 18,956
Re: Ask me anything about Microsoft Excel

I'm letting it slide but understand that i scrutinize computer type ask me threads far more then most since its my industry
killa is offline   Reply With Quote
Old 09-13-2010, 10:38 AM   #79
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by killa View Post
I'm letting it slide but understand that i scrutinize computer type ask me threads far more then most since its my industry
I knew when I made it there would be extra scrutiny because i'm sure there are a disproportionate amount of IT people on this forum. I should have titled it "ask me anything about excel automation" because that's more where my speciality lies.

In saying that, i was hoping there are finance people here who spend their whole day in pivot tables and finance functions that i will never touch who could benefit from automation and can help others with pivot tables questions.
zomg is offline   Reply With Quote
Old 09-13-2010, 10:50 AM   #80
killa
huge dick
 
killa's Avatar
 
Join Date: Oct 2004
Location: NYC
Posts: 18,956
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
I knew when I made it there would be extra scrutiny because i'm sure there are a disproportionate amount of IT people on this forum. I should have titled it "ask me anything about excel automation" because that's more where my speciality lies.

In saying that, i was hoping there are finance people here who spend their whole day in pivot tables and finance functions that i will never touch who could benefit from automation and can help others with pivot tables questions.
fwiw I work at Hedge Fund as there IT so i know a few things about thsi
but carry on carry on
killa is offline   Reply With Quote
Old 09-13-2010, 11:05 AM   #81
slickpoppa
Carpal \'Tunnel
 
slickpoppa's Avatar
 
Join Date: Aug 2004
Posts: 7,575
Re: Ask me anything about Microsoft Excel

The nerd turf war that is going on in here is ruining this thread for people who don't give a **** which of you guys know more about Excel.
slickpoppa is offline   Reply With Quote
Old 09-13-2010, 11:12 AM   #82
Ultim8Degen
banned
 
Ultim8Degen's Avatar
 
Join Date: Dec 2007
Location: Capucine FTW
Posts: 1,967
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Firstly, I know i've removed passwords from PDF fles before (i found some program on google) but never in excel and it would probably be a long process.

In saying that I just checked the site and while its hard to say without a log-in it seems like you can view the results in .PDF or just on the website, if you can get it to display on a website you can import it into excel using VBA. Click on the data tab (in 2007) and go to "import from web" then log-in and navigate to the table you want. If it works and you record it in the macro recorder you should be able to write some VBA to go around it to do it automatically.
that is the problem. when you try to import from web, then a box pops open asking you for a password at that point. I did not know if there is another way for the data to be moved from pdf to excel
Ultim8Degen is offline   Reply With Quote
Old 09-13-2010, 11:21 AM   #83
Ultim8Degen
banned
 
Ultim8Degen's Avatar
 
Join Date: Dec 2007
Location: Capucine FTW
Posts: 1,967
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by killa View Post
I'm letting it slide but understand that i scrutinize computer type ask me threads far more then most since its my industry
is that why your name is 'killa'?
Ultim8Degen is offline   Reply With Quote
Old 09-13-2010, 11:21 AM   #84
Cueballmania
Carpal \'Tunnel
 
Cueballmania's Avatar
 
Join Date: Sep 2005
Location: Sioux Falls, SD
Posts: 14,161
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
I've never done is specifically, but there appears to be a lot of resources available:

http://controls.engin.umich.edu/wiki...icrosoft_Excel

http://lertap.curtin.edu.au/HTMLHelp...igenvalues.htm

http://people.revoledu.com/kardi/tut...igenValue.html

I only glossed over them because i've never calculated an eigenvalue before but it does seem possible
Cool. My matrix isn't symmetric, so I'll keep the Mathematica step. But your formula worked perfectly (aside from the -1, but that was simple). I didn't know about the function "SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*1)" and that was what I really wanted. Thanks.
Cueballmania is offline   Reply With Quote
Old 09-13-2010, 11:22 AM   #85
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Ultim8Degen View Post
that is the problem. when you try to import from web, then a box pops open asking you for a password at that point. I did not know if there is another way for the data to be moved from pdf to excel
If you log in, in the excel web browser once you shouldn't have to do it again. Worst case scenario if you manually run the import in excel then you could run a macro to instantly clean it up

I'm busy at the moment but later tonight i'll create a log-in and see what i can do, what specific information do you need from the site? Is it results from every track on every date for every race or do you just want specific ones that you pick?
zomg is offline   Reply With Quote
Old 09-13-2010, 11:26 AM   #86
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Cueballmania View Post
Cool. My matrix isn't symmetric, so I'll keep the Mathematica step. But your formula worked perfectly (aside from the -1, but that was simple). I didn't know about the function "SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*1)" and that was what I really wanted. Thanks.
I was going to mention it in the OP as my favourite formula

it's basically a countif on two or more columns of data the *1 at the end makes it a countiF if you changed the *1 to another range it would act as a sumif, for example if you had points scores in column C of data then you could get the total points scored for games when A2 beat B1 by using

SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*(d ata!$C$2:$C$1000))
zomg is offline   Reply With Quote
Old 09-13-2010, 11:27 AM   #87
killa
huge dick
 
killa's Avatar
 
Join Date: Oct 2004
Location: NYC
Posts: 18,956
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Ultim8Degen View Post
is that why your name is 'killa'?
something like that, anyway put the thread back on track as I said
killa is offline   Reply With Quote
Old 09-13-2010, 11:52 AM   #88
Ultim8Degen
banned
 
Ultim8Degen's Avatar
 
Join Date: Dec 2007
Location: Capucine FTW
Posts: 1,967
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
If you log in, in the excel web browser once you shouldn't have to do it again. Worst case scenario if you manually run the import in excel then you could run a macro to instantly clean it up

I'm busy at the moment but later tonight i'll create a log-in and see what i can do, what specific information do you need from the site? Is it results from every track on every date for every race or do you just want specific ones that you pick?
I have a model for picking horses. Currently, I have to do all the calculations by hand. but if I could import all the data to excel, and then write some simple macros, you can imagine how many more races I could handicap and how much faster it would go.
Ultim8Degen is offline   Reply With Quote
Old 09-13-2010, 01:04 PM   #89
Tony Lepatata
old hand
 
Join Date: Jun 2007
Location: New York City
Posts: 1,996
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by killa View Post
fwiw I work at Hedge Fund as there IT so i know a few things about thsi
but carry on carry on
good thing you're not in their grammar and spelling department
Tony Lepatata is offline   Reply With Quote
Old 09-13-2010, 01:46 PM   #90
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Quote:
Originally Posted by Ultim8Degen View Post
that is the problem. when you try to import from web, then a box pops open asking you for a password at that point. I did not know if there is another way for the data to be moved from pdf to excel
If you log in, in the excel web browser once you shouldn't have to do it again. Worst case scenario if you manually run the import in excel then you could run a macro to instantly clean it up

I'm busy at the moment but later tonight i'll create a log-in and see what i can do, what specific information do you need from the site? Is it results from every track on every date for every race or do you just want specific ones that you pick?
Not sure if excel has it, but in access you can add a web browser active x control to a form which has all sorts of built-in functions and triggered events. So basically you could load the site into the browser component, toss in your uid/pw, iterate through the PDF links (parsing the HTML), download the files to a specific directory, switch over to excel and pull them into a worksheet. From there its as easy as writing a macro to process the data to suite you needs.


The browser control was added to the .NET framework in v3 or 3.5, but I never realized there was a control in access. Would have saved me a lot of time for some screen-scraping apps I've put together in the past.
PolvoPelusa is offline   Reply With Quote
Old 09-13-2010, 02:20 PM   #91
exec771
Carpal \'Tunnel
 
exec771's Avatar
 
Join Date: Jun 2007
Posts: 17,333
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Stupid things American girls have said to me would probably be worthy of it's own thread, e.g.
- Are you from [country that is no where near australia]? e.g. England, South Africa, New Zealand, Ireland
wat
exec771 is offline   Reply With Quote
Old 09-13-2010, 02:53 PM   #92
aces_dad
veteran
 
Join Date: Mar 2005
Location: Hillsboro, OR
Posts: 2,639
Re: Ask me anything about Microsoft Excel

I have a cell which has a hyperlink which looks like this:

http://myCompany/sap(bD1lbiZjPTkxMA=...?id=9000416915

I want to update the last 10 numbers in the hyperlink and have the 'Text to display' automatically reflect this:

9*416915

I'm finding myself having to edit both the hyperlink and the Text to display field. Can you provide a way to edit only one of these two fields and have both updated?
aces_dad is offline   Reply With Quote
Old 09-13-2010, 03:14 PM   #93
killa
huge dick
 
killa's Avatar
 
Join Date: Oct 2004
Location: NYC
Posts: 18,956
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Tony Lepatata View Post
good thing you're not in their grammar and spelling department
making fun of my spelling and grammar? How ****ing original of you
killa is offline   Reply With Quote
Old 09-13-2010, 03:35 PM   #94
magent
old hand
 
Join Date: Jul 2010
Location: 30 hands per ****ing hourrrrrrrrrrr
Posts: 1,878
Re: Ask me anything about Microsoft Excel

Say I have permutation of units that I have in order in column A. I'll use the alphabet to simplify and illustrate.

AA
AB
AC
AD etc. to AZ, then

BA
BB
BC
BD
BE etc.

CA
CB
CC
CD

and so on till ZZ. Each two letters are in a cell, and now I'd like to completely randomize the worksheet, so that the cells in the column would read like:

XA
CV
YH
LB

and on. How do I do this? Doesn't seem to be any 'randomize cells' button or anything, but I've been told it can be done.
magent is offline   Reply With Quote
Old 09-13-2010, 03:39 PM   #95
Dudd
Carpal \'Tunnel
 
Dudd's Avatar
 
Join Date: Sep 2004
Location: pm karak w/ ffb questions
Posts: 53,715
Re: Ask me anything about Microsoft Excel

One really easy way to do that is to create a column of random numbers and then just sort by that column, then delete the random column when you're finished sorting.
Dudd is offline   Reply With Quote
Old 09-13-2010, 04:54 PM   #96
FC Juggernaut
newbie
 
FC Juggernaut's Avatar
 
Join Date: Aug 2010
Location: space is the place
Posts: 40
Re: Ask me anything about Microsoft Excel

What do the little green triangles mean in the top left corners of cells? i feel like they are responsible for not letting me perform simple sum equations for columns of numbers.
FC Juggernaut is offline   Reply With Quote
Old 09-13-2010, 05:39 PM   #97
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
Not sure if excel has it, but in access you can add a web browser active x control to a form which has all sorts of built-in functions and triggered events. So basically you could load the site into the browser component, toss in your uid/pw, iterate through the PDF links (parsing the HTML), download the files to a specific directory, switch over to excel and pull them into a worksheet. From there its as easy as writing a macro to process the data to suite you needs.


The browser control was added to the .NET framework in v3 or 3.5, but I never realized there was a control in access. Would have saved me a lot of time for some screen-scraping apps I've put together in the past.
Never used it before but i imagine its the same thing as the one in excel, i think he said that the PDF was password protected and he couldnt copy data out, which is why i was looking at importing the whole page and formatting with vba

Quote:
Originally Posted by exec771 View Post
wat
I think the accent is so different to an australian accent that i'm not correcting myself!

Quote:
Originally Posted by aces_dad View Post
I have a cell which has a hyperlink which looks like this:

http://myCompany/sap(bD1lbiZjPTkxMA=...?id=9000416915

I want to update the last 10 numbers in the hyperlink and have the 'Text to display' automatically reflect this:

9*416915

I'm finding myself having to edit both the hyperlink and the Text to display field. Can you provide a way to edit only one of these two fields and have both updated?
If the hyperlink is in A1 then you can do:

=HYPERLINK(A1,RIGHT(A1,10))

Quote:
Originally Posted by magent View Post
Say I have permutation of units that I have in order in column A. I'll use the alphabet to simplify and illustrate.

AA
AB
AC
AD etc. to AZ, then

BA
BB
BC
BD
BE etc.

CA
CB
CC
CD

and so on till ZZ. Each two letters are in a cell, and now I'd like to completely randomize the worksheet, so that the cells in the column would read like:

XA
CV
YH
LB

and on. How do I do this? Doesn't seem to be any 'randomize cells' button or anything, but I've been told it can be done.
Quote:
Originally Posted by Dudd View Post
One really easy way to do that is to create a column of random numbers and then just sort by that column, then delete the random column when you're finished sorting.
more explanation if you need: in column C put =RAND() then copy down, then sort by column C and it will be random, if you want to re-randomise then go to the formula tab and click calculate sheet and sort again


Quote:
Originally Posted by FC Juggernaut View Post
What do the little green triangles mean in the top left corners of cells? i feel like they are responsible for not letting me perform simple sum equations for columns of numbers.
They are just notes from excel, off the top of my head you get them if you are displaying a number as text and also if you have 10 cells in a row with the same formula (or a formula copied down) then you change one in the middle it will alert you that there is an inconsistent formula

personally i usually just right click on the little green thing then hit ignore
zomg is offline   Reply With Quote
Old 09-13-2010, 06:10 PM   #98
KanMan
grinder
 
Join Date: Oct 2005
Posts: 656
Re: Ask me anything about Microsoft Excel

CopThis/zomg/anyone else,

I'm entering into the actuary field and I need to touch up on my excel. I never took any classes and my only experience with excel were at my job, so i'd say im pretty much a beginner.

Are there any good books out there I can pick up to get acclimated?
KanMan is offline   Reply With Quote
Old 09-13-2010, 09:05 PM   #99
growingskin
newbie
 
Join Date: Sep 2010
Posts: 45
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
In 2007: Click on the cell, go to the home tab on the ribbon, select conditional formatting > 'equal to' then in the box that comes up type "paid" (without quotes)

In 2003: from memory i think its data > conditional formatting then go to formula and enter ="Paid"
thanks for your reply

But I can't seem to get it to work on 2003, I haven't tried it on my 2nd computer with 2007, I'll have to try it later although I really wanted it to work on 2003 > this my main pc
growingskin is offline   Reply With Quote
Old 09-13-2010, 09:32 PM   #100
sockhead2
grinder
 
Join Date: Jan 2010
Posts: 573
Re: Ask me anything about Microsoft Excel

I can do anything in Excel, but what I've found over and over to be a big problem is people insisting on using Excel, when what they really want is a database. Great, I can figure out how to do all kinds of manipulation, apply all kinds of filters and sorting, and using Excel 2007, do all this on a couple hundred thousand rows of data... and because I'm really clever and a total Excel guru, I can make it work without crashing the computer, and I can make it run in under 2 hours. Or, if we dump it into any database -- even MS Access which everyone's got on their computer "for free" in any work environment using Excel -- anyone with even basic database skills can do everything I did in Excel, and it will only take 5 seconds to run. And there is no risk that it will crash anyone's computer.

Ug.

Know what I mean? So while I'm totally impressed with everything Excel can do, and I find it incredibly fun to tackle huge and seemingly impossible tasks in it, I'd much rather people just accept it for what it is and use other programs when it is more appropriate.

Anyway, one other thing. I can do anything in Excel, including use pivot tables. Nanner nanner boo boo!
sockhead2 is offline   Reply With Quote

Reply
      

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Forum Jump


All times are GMT -4. The time now is 04:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright © 2008-2017, Two Plus Two Interactive
 
 
Poker Players - Streaming Live Online