Ask me anything about Microsoft Excel - Page 8 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

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.

Quote

09-30-2010 , 03:24 PM

#177

NHFunkii

Carpal \'Tunnel

Join Date: Jul 2005 Posts: 14,649

Quote:

Originally Posted by PolvoPelusa

what kinda job?

doing statistcal analysis at a political advertising agency

Quote:

Originally Posted by JL514

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

Quote

10-01-2010 , 01:19 PM

#178

ChipWrecked

Carpal \'Tunnel

Join Date: Nov 2002 Posts: 13,006

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.

Quote

10-01-2010 , 02:19 PM

#179

PolvoPelusa

adept

Join Date: Aug 2008 Posts: 974

pivot table

Quote

10-01-2010 , 04:06 PM

#180

ChipWrecked

Carpal \'Tunnel

Join Date: Nov 2002 Posts: 13,006

****, even OP doesn't do pivot tables

Quote

10-01-2010 , 05:11 PM

#181

PayTheSnucka

grinder

Join Date: Jul 2004 Posts: 564

Quote:

Originally Posted by ChipWrecked

This is one of the instances where I would use a pivot table, but I would immediately range-value the results elsewhere.

Quote

10-01-2010 , 06:05 PM

#182

PolvoPelusa

adept

Join Date: Aug 2008 Posts: 974

pivot tables tutorial (1/3)

Quote

10-01-2010 , 06:38 PM

#183

ChipWrecked

Carpal \'Tunnel

Join Date: Nov 2002 Posts: 13,006

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!

Quote

10-02-2010 , 01:31 AM

#184

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by ChipWrecked

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

Quote

10-02-2010 , 10:48 AM

#185

suzzer99

Save the Cheerleader, Save the World

Join Date: Nov 2005 Posts: 99,984

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.

Quote

10-02-2010 , 11:50 AM

#186

spyderracing

veteran

Join Date: May 2006 Posts: 2,364

Ever use SPSS, and have any thoughts on comparisons between their program and Excel?

Quote

10-02-2010 , 04:49 PM

#187

HajiShirazu

Pooh-Bah

Join Date: Aug 2003 Posts: 4,688

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.

Quote

10-02-2010 , 04:57 PM

#188

PolvoPelusa

adept

Join Date: Aug 2008 Posts: 974

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.

Quote

10-02-2010 , 08:09 PM

#189

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by HajiShirazu

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

Quote

10-02-2010 , 08:12 PM

#190

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by spyderracing

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

Quote

10-02-2010 , 08:22 PM

#191

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by suzzer99

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.&):

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

Quote

10-02-2010 , 09:29 PM

#192

suzzer99

Save the Cheerleader, Save the World

Join Date: Nov 2005 Posts: 99,984

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.

Quote

10-02-2010 , 11:24 PM

#193

Hellrazor

grinder

Join Date: Jul 2003 Posts: 640

Quote:

Originally Posted by suzzer99

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

Quote

10-02-2010 , 11:25 PM

#194

suzzer99

Save the Cheerleader, Save the World

Join Date: Nov 2005 Posts: 99,984

OMG they finally upgraded!!! I seriously didn't think this day would ever come.

Quote

10-02-2010 , 11:25 PM

#195

Hellrazor

grinder

Join Date: Jul 2003 Posts: 640

Quote:

Originally Posted by suzzer99

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....

Quote

10-02-2010 , 11:30 PM

#196

Hellrazor

grinder

Join Date: Jul 2003 Posts: 640

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)

Quote

10-02-2010 , 11:37 PM

#197

suzzer99

Save the Cheerleader, Save the World

Join Date: Nov 2005 Posts: 99,984

Thanks for that tip. I start a new corporate job Monday, and am still on Office 2000 at home.

Quote

10-03-2010 , 05:17 AM

#198

JammyDodga

veteran

Join Date: Jul 2006 Posts: 2,268

Quote:

Originally Posted by zomg

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?

Quote

10-03-2010 , 12:51 PM

#199

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by JammyDodga

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

Quote

10-03-2010 , 11:17 PM

#200

Mr.WeakTight

old hand

Join Date: Mar 2007 Posts: 1,217

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?

Quote

Page 8 of 120

First

3 4 5 6 7 8 9 10 11 12 13 18 28 58 108

Last

Post Reply Subscribe

...

Page 8 of 120

First

3 4 5 6 7 8 9 10 11 12 13 18 28 58 108

Last