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-30-2010, 01:43 PM   #176
swiz
enthusiast
 
Join Date: Dec 2007
Posts: 61
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
Something like:

Code:
Private Sub Worksheet_Activate()
ActiveSheet.Columns("A:A").ClearContents
cRow = 1
For Each nm In ThisWorkbook.Names
    ActiveSheet.Range("A" & cRow) = nm.Name
    cRow = cRow + 1
Next nm
End Sub
awesome, thanks. I think the key is to just have the application discover the names from the source, rather then having a legend put aside. This is clearly a cleaner approach. Thanks again to both of you.
swiz is offline   Reply With Quote
Old 09-30-2010, 03:24 PM   #177
NHFunkii
Carpal \'Tunnel
 
NHFunkii's Avatar
 
Join Date: Jul 2005
Location: is this real life?
Posts: 14,642
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by PolvoPelusa View Post
what kinda job?
doing statistcal analysis at a political advertising agency

Quote:
Originally Posted by JL514 View Post
Funkii getting a job?
probably gonna take an internship instead actually, but yeah I'm not trying to play poker forever so gonna try out some other stuff
NHFunkii is offline   Reply With Quote
Old 10-01-2010, 01:19 PM   #178
ChipWrecked
Carpal \'Tunnel
 
ChipWrecked's Avatar
 
Join Date: Nov 2002
Location: It's alive
Posts: 12,976
Re: Ask me anything about Microsoft Excel

I'm sure this is a completely noob question but that's where I'm at....

Excel 2007

I have a single column with over 10k text values, many of them repeat. I want to sort each unique instance into its own column.

I'm trying to find out how many uniques there are, and how many instances of each unique. I think this sort will be the best way to do this but am open to suggestion.


I'm clearly googletarding because I can't seem to find an answer to this, but I imagine it's a fairly standard sort operation.

Thanks in advance.
ChipWrecked is offline   Reply With Quote
Old 10-01-2010, 02:19 PM   #179
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

pivot table
PolvoPelusa is offline   Reply With Quote
Old 10-01-2010, 04:06 PM   #180
ChipWrecked
Carpal \'Tunnel
 
ChipWrecked's Avatar
 
Join Date: Nov 2002
Location: It's alive
Posts: 12,976
Re: Ask me anything about Microsoft Excel

****, even OP doesn't do pivot tables
ChipWrecked is offline   Reply With Quote
Old 10-01-2010, 05:11 PM   #181
PayTheSnucka
grinder
 
PayTheSnucka's Avatar
 
Join Date: Jul 2004
Posts: 564
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by ChipWrecked View Post
I'm sure this is a completely noob question but that's where I'm at....

Excel 2007

I have a single column with over 10k text values, many of them repeat. I want to sort each unique instance into its own column.

I'm trying to find out how many uniques there are, and how many instances of each unique. I think this sort will be the best way to do this but am open to suggestion.


I'm clearly googletarding because I can't seem to find an answer to this, but I imagine it's a fairly standard sort operation.

Thanks in advance.
This is one of the instances where I would use a pivot table, but I would immediately range-value the results elsewhere.
PayTheSnucka is offline   Reply With Quote
Old 10-01-2010, 06:05 PM   #182
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

pivot tables tutorial (1/3)
PolvoPelusa is offline   Reply With Quote
Old 10-01-2010, 06:38 PM   #183
ChipWrecked
Carpal \'Tunnel
 
ChipWrecked's Avatar
 
Join Date: Nov 2002
Location: It's alive
Posts: 12,976
Re: Ask me anything about Microsoft Excel

Thanks all, this worked.

What was hanging me up was the need for something in the 'values' field, to give Excel the ability to count unique instances in the main column.

One of the IT managers (not from my group) turned out to be a pivot table savant, and gave me props for getting as far as I did on my own (thanks to this thread) when I asked him to look at what I had.

Thanks for the help OOT!
ChipWrecked is offline   Reply With Quote
Old 10-02-2010, 01:31 AM   #184
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 ChipWrecked View Post
Thanks all, this worked.

What was hanging me up was the need for something in the 'values' field, to give Excel the ability to count unique instances in the main column.

One of the IT managers (not from my group) turned out to be a pivot table savant, and gave me props for getting as far as I did on my own (thanks to this thread) when I asked him to look at what I had.

Thanks for the help OOT!
Good to hear, just for clarification in this instance i would've used a pivot as well (It's clearly not difficult to make a basic one) it's just that i dont encounter these types of situations because i am rarely using excel for data analysis (ironic i guess)

My job would more likely be to create a pretty report from the data in which case a PT isn't sufficient
zomg is offline   Reply With Quote
Old 10-02-2010, 10:48 AM   #185
suzzer99
Carpal \'Tunnel
 
suzzer99's Avatar
 
Join Date: Nov 2005
Location: on top of the bell curve
Posts: 89,361
Re: Ask me anything about Microsoft Excel

if I have some random crap that ends with a number like ejdnn.&):&uui8 in some random cell, and copy/fill down, Excel will increment like this:

ejdnn.&):&uui8
ejdnn.&):&uui9
ejdnn.&):&uui10
ejdnn.&):&uui11
... etc. ...


BUT if I have the number '1' in cell A1, and copy/fill down, it won't increment. I get:

1
1
1
1
...

Gee, like you'd never want to increment a 1 in cell A1, but always want to increment some random garbage in cell K74. This is just sick sadistic minds at Microsoft laughing at us right?

Like how the number of recently opened files to remember will always default to 4 and you will NEVER be able to remember more than 9. I'm sorry we just don't have the memory space in today's PCs to keep track of more than 9 filenames. I'm pretty sure office 2150 will still default to 4 recently openened files and max out at 9, except that the settings screen to bump it up to 9 will be hidden behind a labyrinth that takes 8 hours to navigate.

And don't even gt me started on Word... dammit now I'm all annoyed to start my day.

Last edited by suzzer99; 10-02-2010 at 10:54 AM.
suzzer99 is offline   Reply With Quote
Old 10-02-2010, 11:50 AM   #186
spyderracing
veteran
 
spyderracing's Avatar
 
Join Date: May 2006
Location: Hopelessly putting money in the pot
Posts: 2,364
Re: Ask me anything about Microsoft Excel

Ever use SPSS, and have any thoughts on comparisons between their program and Excel?
spyderracing is offline   Reply With Quote
Old 10-02-2010, 04:49 PM   #187
HajiShirazu
Pooh-Bah
 
HajiShirazu's Avatar
 
Join Date: Aug 2003
Location: cricket analysis
Posts: 4,688
Re: Ask me anything about Microsoft Excel

I would like to get some scorecards off of cricinfo.com and/or sherdog.com into an excel workbook, without having to copy/paste each time. These sites are not password protected and have consistent formatting. Is there a good way to do this?

I know how to do a lot with vba, just never did anything with websites, don't know if you even can.
HajiShirazu is offline   Reply With Quote
Old 10-02-2010, 04:57 PM   #188
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

i'd recommend access for this. place a browser activex component on a form, load a webpage, parse the html to extract what you need, move on to the next page. essetially a crawler/spider/scraper/there-are-plenty-of-names-for-these...then just export to excel.
PolvoPelusa is offline   Reply With Quote
Old 10-02-2010, 08:09 PM   #189
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 HajiShirazu View Post
I would like to get some scorecards off of cricinfo.com and/or sherdog.com into an excel workbook, without having to copy/paste each time. These sites are not password protected and have consistent formatting. Is there a good way to do this?

I know how to do a lot with vba, just never did anything with websites, don't know if you even can.
This should point you in the right direction but it takes a long time to run and you will need to add some VBA formatting

take out the line EXIT SUB in the middle to make it run all the way through otherwise it will just create one result sheet

basically it imports the main page then loops through all the hyper links to find the main tour pages then it loops through the tour page hyperlinks to find individual games i never actually waited for it to run all the way through but there should be plenty there for you if you know how to use VBA

Code:
Sub importCricInfoResults()
    Dim mainPageWS As Worksheet
    Dim tmpWS As Worksheet
    Dim curWS As Worksheet
    
    Set mainPageWS = Sheets.Add
    totalCount = 1
    With mainPageWS.QueryTables.Add(Connection:= _
        "URL;http://www.cricinfo.com/ci/engine/series/index.html?season=2010", _
        Destination:=mainPageWS.Range("$A$1"))
        .Name = "index.html?season=2010"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
    For Each hpl In mainPageWS.Hyperlinks
        If Left(hpl.Address, 41) = "http://www.cricinfo.com/ci/engine/series/" Then
            If Left(hpl.Address, 46) <> "http://www.cricinfo.com/ci/engine/series/index" Then
                Set tmpWS = Sheets.Add
                With tmpWS.QueryTables.Add(Connection:="URL;" & hpl.Address, _
                    Destination:=tmpWS.Range("$A$1"))
                    .Name = hpl.Address
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = False
                    .RefreshOnFileOpen = False
                    .BackgroundQuery = True
                    .RefreshStyle = xlInsertDeleteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .WebSelectionType = xlEntirePage
                    .WebFormatting = xlWebFormattingAll
                    .WebPreFormattedTextToColumns = True
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=False
                End With
                curCount = 1
                For Each hpl2 In tmpWS.Hyperlinks
                    If Left(hpl2.Address, 41) = "http://www.cricinfo.com/ci/engine/series/" Then
                    
                    End If
                    'MsgBox hpl2.Address & "," & InStr(hpl2.Address, "match")
                    If InStr(hpl2.Address, "/engine/match/") <> 0 Then
                        Set curWS = Sheets.Add
                        tmpName = Mid(hpl2.Address, 25, InStr(hpl2.Address, "/engine") - 25)
                        tmpTeam1 = Left(tmpName, 3)
                        tmpTeam2 = Mid(tmpName, InStr(tmpName, "-v-") + 3, 3)
                        curYear = Right(tmpName, 4)
                        tmpIndex = Left(Right(hpl.Address, 11), 6)
                        tmpShtName = tmpTeam1 & "V" & tmpTeam2 & curYear & "-" & tmpIndex & "-" & curCount
                        curWS.Name = tmpShtName
                        Application.StatusBar = "Creating: " & tmpShtName & " Total: " & totalCount
                        
                        With curWS.QueryTables.Add(Connection:= _
                            "URL;" & hpl2.Address, Destination _
                            :=curWS.Range("$A$1"))
                            .Name = hpl2.Address
                            .FieldNames = True
                            .RowNumbers = False
                            .FillAdjacentFormulas = False
                            .PreserveFormatting = True
                            .RefreshOnFileOpen = False
                            .BackgroundQuery = True
                            .RefreshStyle = xlInsertDeleteCells
                            .SavePassword = False
                            .SaveData = True
                            .AdjustColumnWidth = True
                            .RefreshPeriod = 0
                            .WebSelectionType = xlEntirePage
                            .WebFormatting = xlWebFormattingNone
                            .WebPreFormattedTextToColumns = True
                            .WebConsecutiveDelimitersAsOne = True
                            .WebSingleBlockTextImport = False
                            .WebDisableDateRecognition = False
                            .WebDisableRedirections = False
                            .Refresh BackgroundQuery:=False
                        End With
                        
                        For i = 1 To curWS.Range("A65000").End(xlUp).Row
                            If curWS.Range("A" & i) = "View dismissal" Then
                                curWS.Rows("1:" & i - 2).Delete
                                Exit For
                            End If
                        Next i
                       
                        '''''''''''' REMOVE THIS LINE
                        exit sub

                        curCount = curCount + 1
                        totalCount = totalCount + 1
                    End If
                Next hpl2
                Application.DisplayAlerts = False
                    tmpWS.Delete
                Application.DisplayAlerts = True
            End If
        End If
    Next hpl
    
    Application.DisplayAlerts = False
        mainPageWS.Delete
    Application.DisplayAlerts = True
    
End Sub
zomg is offline   Reply With Quote
Old 10-02-2010, 08:12 PM   #190
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 spyderracing View Post
Ever use SPSS, and have any thoughts on comparisons between their program and Excel?
Yes only in University, its great if you need to do intense statistical analysis or plotting but i dont think i will ever use it again in my life. I think the later versions of excel have tried to cover the main functions of SPSS that most people will use and i guess you could argue that it would probably better to teach excel to do this analysis in university but im sure there are situations that would arise for people doing crazy analysis where excel can't do what they want
zomg is offline   Reply With Quote
Old 10-02-2010, 08:22 PM   #191
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 suzzer99 View Post
if I have some random crap that ends with a number like ejdnn.&):&uui8 in some random cell, and copy/fill down, Excel will increment like this:

ejdnn.&):&uui8
ejdnn.&):&uui9
ejdnn.&):&uui10
ejdnn.&):&uui11
... etc. ...


BUT if I have the number '1' in cell A1, and copy/fill down, it won't increment. I get:

1
1
1
1
...

Gee, like you'd never want to increment a 1 in cell A1, but always want to increment some random garbage in cell K74. This is just sick sadistic minds at Microsoft laughing at us right?
Even though you are clearly ranting (all valid points) the reason for the above is text vs number formatting. If you enter a1 in cell a1 then copy down it will change to a2,a3 similarly if you format the cell as text and put a 1 then copy down it will increment

im not saying its not stupid but there is some logic behind it
zomg is offline   Reply With Quote
Old 10-02-2010, 09:29 PM   #192
suzzer99
Carpal \'Tunnel
 
suzzer99's Avatar
 
Join Date: Nov 2005
Location: on top of the bell curve
Posts: 89,361
Re: Ask me anything about Microsoft Excel

Why would you only want to increment text cells and not numeric cells? If anything wouldn't you think it would be the other way around?

By the way the clever way around this is to put '1' and A1 and '2' in A2. Then Excel figures it out and increments the rest.
suzzer99 is offline   Reply With Quote
Old 10-02-2010, 11:24 PM   #193
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by suzzer99 View Post
Like how the number of recently opened files to remember will always default to 4 and you will NEVER be able to remember more than 9. I'm sorry we just don't have the memory space in today's PCs to keep track of more than 9 filenames. I'm pretty sure office 2150 will still default to 4 recently openened files and max out at 9, except that the settings screen to bump it up to 9 will be hidden behind a labyrinth that takes 8 hours to navigate.
.
Default number of recent files in excel 2010 is 25, max is 50. File|Help|Options|Advanced|Display
Hellrazor is offline   Reply With Quote
Old 10-02-2010, 11:25 PM   #194
suzzer99
Carpal \'Tunnel
 
suzzer99's Avatar
 
Join Date: Nov 2005
Location: on top of the bell curve
Posts: 89,361
Re: Ask me anything about Microsoft Excel

OMG they finally upgraded!!! I seriously didn't think this day would ever come.
suzzer99 is offline   Reply With Quote
Old 10-02-2010, 11:25 PM   #195
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by suzzer99 View Post
Why would you only want to increment text cells and not numeric cells? If anything wouldn't you think it would be the other way around?

By the way the clever way around this is to put '1' and A1 and '2' in A2. Then Excel figures it out and increments the rest.
Excel 2010 - you can choose how to fill, Linear, Growth, Date etc. You can also choose step and stop values. Not sure if this was in 2007, too lazy to remote into work PC to check....
Hellrazor is offline   Reply With Quote
Old 10-02-2010, 11:30 PM   #196
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
Re: Ask me anything about Microsoft Excel

Also key tip for almost free and legal upgrades for some - if you work for a large corp that has licensing agreements with Microsoft - check to see if they have a "Home Use Program" aka Microsoft HUP - it allows you to purchase a copy of the most recent office package for very cheap. I have office 2010 for $10 on my home PC - downloaded and licensed by Microsoft, downside is I have to agree to delete it if I leave my job. (....)

Also Microsoft Project 2010 and Visio 2010 avail for $10 more...(same restrictions)
Hellrazor is offline   Reply With Quote
Old 10-02-2010, 11:37 PM   #197
suzzer99
Carpal \'Tunnel
 
suzzer99's Avatar
 
Join Date: Nov 2005
Location: on top of the bell curve
Posts: 89,361
Re: Ask me anything about Microsoft Excel

Thanks for that tip. I start a new corporate job Monday, and am still on Office 2000 at home.
suzzer99 is offline   Reply With Quote
Old 10-03-2010, 05:17 AM   #198
JammyDodga
veteran
 
JammyDodga's Avatar
 
Join Date: Jul 2006
Posts: 2,268
Quote:
Originally Posted by zomg View Post
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))
Wait, you know there's sumifs and countifs functions in 2007 right?
JammyDodga is offline   Reply With Quote
Old 10-03-2010, 12:51 PM   #199
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 JammyDodga View Post
Wait, you know there's sumifs and countifs functions in 2007 right?
Yes but as I mentioned 90% of the work I do is not for companies using 2007

I guess for completeness this is same formula using sumifs

=sumifs(data!$C$2:$C$1000,data!$A$2:$A$1000,$A2,da ta!$B$2:$B$1000,B$1)

note that the sum range is first
zomg is offline   Reply With Quote
Old 10-03-2010, 11:17 PM   #200
Mr.WeakTight
old hand
 
Mr.WeakTight's Avatar
 
Join Date: Mar 2007
Location: land of 11,842 lakes
Posts: 1,217
Re: Ask me anything about Microsoft Excel

I have a project that involves making a database out of strictly text.
it would basically be a knowledgebase at my job. they have one but it's poorly done. but it would be outside my job for me to make a new one, but i really need the info that's in the knowledgebase to do a good job; and i can't really download some other software if that's part of the solution.

I tried to use Access, but there seems to be a 250 character limit and some of the text entries would be significantly higher than that and can't be broken up. Is there a way around that limitation? or should I use Excel?
Mr.WeakTight 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:42 AM.


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