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-12-2010, 03:02 PM   #26
jimmerish
enthusiast
 
Join Date: Aug 2009
Posts: 63
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Big TR View Post
Sorry, but I'll be a dick and mention that an ask me thread about Excel in which the OP can't discuss the use of pivot tables is laughable.
lol agree.
jimmerish is offline   Reply With Quote
Old 09-12-2010, 03:04 PM   #27
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 Big TR View Post
Sorry, but I'll be a dick and mention that an ask me thread about Excel in which the OP can't discuss the use of pivot tables is laughable.
I agree, but i assumed other people would have some comments on it and I/others could learn, that's the point no? There's always going to be someone better at something but maybe they couldn't be bothered starting a thread.

I don't want to get defensive but most of the things I do are not specifically front end which is why I suck at pivot tables. It's probably because i've never done a job that required pivot tables, and find that using vba to manipulate data produces better data sets in my experience.
zomg is offline   Reply With Quote
Old 09-12-2010, 03:08 PM   #28
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 jimmerish View Post
When watching someone else use Excel do you get antsy cause you see like 12 different ways things could be doing it 10 times faster and more efficiently?
Yes! I have also learned that most people don't care about a better way to do it and just keep my mouth shut (unless that's why i am there obviously).

It's the same thing in video games, when someone is doing something wrong and you just want to punch them in the face. Unfortunately it's not always a good idea.

EDIT: I also realise that somewhere out there someone could be watching over my shoulder and thinking the same thing (see: pivot tables)
zomg is offline   Reply With Quote
Old 09-12-2010, 03:15 PM   #29
ballin4life
veteran
 
Join Date: Jun 2006
Posts: 2,021
Re: Ask me anything about Microsoft Excel

What are the pros/cons of using Excel to store data as opposed to a (relational) database?
ballin4life is offline   Reply With Quote
Old 09-12-2010, 03:23 PM   #30
d10
Carpal \'Tunnel
 
d10's Avatar
 
Join Date: Jan 2005
Location: I fly better than I drive
Posts: 7,068
Re: Ask me anything about Microsoft Excel

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.
d10 is offline   Reply With Quote
Old 09-12-2010, 03:23 PM   #31
Rnr_Rnr_Hobgoblin
Pooh-Bah
 
Join Date: Oct 2004
Posts: 4,610
Re: Ask me anything about Microsoft Excel

Obviously I'm a super n00b on Excel but I'm curious how hard it is to write(?) macros. i run some of these daily and can't imagine how much harder my job would be without them.
Rnr_Rnr_Hobgoblin is offline   Reply With Quote
Old 09-12-2010, 03:38 PM   #32
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

I have two questions that spring to mind after we just upgraded to Office 07 at work:

I notice that Excel 07 has gone from something like 255 cols and 66k rows so something a zillion times more. However, it seems to me that it you use all the rows then even inserting/deleting a row (so forcing Excel to change lots of references) will crash the PC. Is this just some kind of forward planning by Microsoft in that the sheet range is way too big for anyone to use now?

Re macros and the syntax of Excel ranges (especially if you want variables, eg row number). This officially does my head in and I've never been convinced that it works as it should. Is it just me, or have I missed something?
CopTHIS is offline   Reply With Quote
Old 09-12-2010, 03:40 PM   #33
SuperRams
enthusiast
 
Join Date: Apr 2010
Posts: 98
Re: Ask me anything about Microsoft Excel

How would one get into this line of work? I do a fair bit of VBA programming and SQL-based work in my current position.

A tech question -

How would you aggregate an array?

Say my array is 3 columns:

A 1 11
A 1 22
B 1 10
B 1 11

So that my new array is:

A 1 33
B 1 21
SuperRams is offline   Reply With Quote
Old 09-12-2010, 03:41 PM   #34
Neko
Pooh-Bah
 
Join Date: Jul 2005
Posts: 4,000
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by ballin4life View Post
What are the pros/cons of using Excel to store data as opposed to a (relational) database?
http://adminsecret.monster.com/benef...not-a-database
Neko is offline   Reply With Quote
Old 09-12-2010, 03:44 PM   #35
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 ballin4life View Post
What are the pros/cons of using Excel to store data as opposed to a (relational) database?
First i would NEVER use excel for a relational database, you would pretty much be using VBA to rewrite SQL. On a flatfile (i.e. a single table with no joins) i would say that any company that is using it could be using a real database with more effect but they don't have the knowledge/money/motivation to do it and you could argue that in most cases it is probably sufficient to the level they need

To do any meaningful reporting from a database you would need to write complex SQL that would then feed into excel to produce pretty graphs, obviously people are much more comfortable with setting up a simple datasheet in excel than trying to set up a database from scratch.

EDIT: or see post above

Quote:
Originally Posted by Rnr_Rnr_Hobgoblin View Post
Obviously I'm a super n00b on Excel but I'm curious how hard it is to write(?) macros. i run some of these daily and can't imagine how much harder my job would be without them.
If you have any programming experience it is REALLY easy, with anything involving only one file you can record it with the macro recorder then go to the vba and take what you need, e.g. whenever i need to do a sort in vba from scratch ill do it in the recorder then take what i need and put it in my code (the recorder creates really bad macros but is useful for specific functions) heres what i mean:

Code:
Sub recordedSort()
'
' recordedSort Macro
'

'
    Range("A1:F7").Select
    Range("F7").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:F7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
vs
Code:
Sub mySort()
    Dim sht1 As Worksheet
    Set sht1 = ThisWorkbook.Sheets("Sheet1")

    With sht1.Sort
        .SortFields.Clear
        .SortFields.Add Key:=sht1.Columns("B:B"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=sht1.Columns("D:D"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange sht1.Columns("A:F")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
You should almost never be selecting specific cells as it slows processing and can produce random results, also using 'activesheet' can sometimes screw you up

Even that is not the most efficient way to write a sort and you could probably do it in less lines but that was just off the top of my head

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.
This is how I feel but I don't know pivot tables well enough to completely write them off
zomg is offline   Reply With Quote
Old 09-12-2010, 03:47 PM   #36
SuperRams
enthusiast
 
Join Date: Apr 2010
Posts: 98
Re: Ask me anything about Microsoft Excel

CopThis,

When you want a variable range find out how many rows and cols you have first then concatenate the variables.

iColumnCount= Range("A1").End(xlToRight).Column
iRowCount = Range("A1").End(xldown).Row

rgMyRange = wsMyWorksheet.Range("A" & iRowCount, "A" & iColumnCount)

Untested code fwiw.
SuperRams is offline   Reply With Quote
Old 09-12-2010, 03:49 PM   #37
SuperRams
enthusiast
 
Join Date: Apr 2010
Posts: 98
Re: Ask me anything about Microsoft Excel

zomg,

"Dim sht1 As Worksheet"

Do you ever use the Option Explicit statement? When I started I learnt it was best practice and all
SuperRams is offline   Reply With Quote
Old 09-12-2010, 03:57 PM   #38
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 CopTHIS View Post
I have two questions that spring to mind after we just upgraded to Office 07 at work:

I notice that Excel 07 has gone from something like 255 cols and 66k rows so something a zillion times more. However, it seems to me that it you use all the rows then even inserting/deleting a row (so forcing Excel to change lots of references) will crash the PC. Is this just some kind of forward planning by Microsoft in that the sheet range is way too big for anyone to use now?
I've actually never encountered this and i've had very large databases it's probably just a computer specific issue. One thing i find with excel (especially with vba) the workbook can often get corrupted and produce random results that can be completely fixed by copying into a fresh workbook and re saving with no other changes.. I spend a lot of time convincing companies that excel is crap and they should be using other systems (essentially talking myself out of a job but sometimes processes involving numerous large macro enabled workbooks in excel can be frustrating)

Quote:
Originally Posted by CopTHIS View Post
Re macros and the syntax of Excel ranges (especially if you want variables, eg row number). This officially does my head in and I've never been convinced that it works as it should. Is it just me, or have I missed something?
I'm not sure what you mean but alot of people use the reference style:
Code:
sheets("Sheet1").cells(1,1) = "hello"
I prefer the style:
Code:
sheets("Sheet1").range("A1") = "hello"
for obvious reasons, and very rarely need to use the ".cells" extension

Quote:
Originally Posted by SuperRams View Post
How would one get into this line of work? I do a fair bit of VBA programming and SQL-based work in my current position.
I mentioned this before but the hardest part is getting your foot in the door, even someone with a rudimentary understanding of using VBA could offer value to business', the hard part is convincing them to spend the money

Quote:
Originally Posted by SuperRams View Post
A tech question -

How would you aggregate an array?

Say my array is 3 columns:

A 1 11
A 1 22
B 1 10
B 1 11

So that my new array is:

A 1 33
B 1 21
off the top of my head i would do something like
for each element in main array
if element is not in second array
add to second array using worksheetfunction.sumif(array(1st column),current letter, array(3rd column))

im not sure of the exact syntax but at first glance that's probably what i would do

Let me know if you need more help but i'm more a believer of pointing people in the right direction than just flat out giving a solution
I would look into using the "worksheetfunction" command off the top of my head i
zomg is offline   Reply With Quote
Old 09-12-2010, 03:59 PM   #39
z28dreams
Pooh-Bah
 
z28dreams's Avatar
 
Join Date: Sep 2005
Location: Spammer
Posts: 5,103
Re: Ask me anything about Microsoft Excel

Here's one for you:

Do you have any best practices for handling the formatting of dates?

I've spent countless hours trying to convert various cells into a date format that I wanted, but with little luck.

For example, going between Jan 4th, 2010, 1/4/2010, and a more generic 1-2010 / Jan-2010 always results in nightmares for me.

Sometimes it seems to work perfectly, other times Excel refuses to cooperate. I'm aware of the Cell Formatting options (and even tried using the custom formatting for like M/YYYY), but it doesn't always work.

Any tips?
z28dreams is offline   Reply With Quote
Old 09-12-2010, 04:00 PM   #40
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 SuperRams View Post
zomg,

"Dim sht1 As Worksheet"

Do you ever use the Option Explicit statement? When I started I learnt it was best practice and all
I try to and it is definitely best practice. I always hear that the main benefit is it saves space by specifically declaring everything but i'm not convinced that it makes any significant difference with the processing power cpu's have these days.

OTOH the amount of times i've misspelled a variable and taken 20 minutes to figure out why the result isn't what it should be when an option explicit would have thrown an error straight away is all the convincing i need.
zomg is offline   Reply With Quote
Old 09-12-2010, 04:04 PM   #41
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 SuperRams View Post
CopThis,

When you want a variable range find out how many rows and cols you have first then concatenate the variables.

iColumnCount= Range("A1").End(xlToRight).Column
iRowCount = Range("A1").End(xldown).Row

rgMyRange = wsMyWorksheet.Range("A" & iRowCount, "A" & iColumnCount)

Untested code fwiw.
FWIW, i never use toright and down i will always use fromleft and up because if there are blanks in the data xldown will not get all the data

i.e.

Code:
iColumnCount= Range("IV1").end(xlfromleft).column
iRowCount = Range("A65000").End(xlup).row
in saying that you can also use "lastusedrow" (not sure on exact spelling) but it has issues in that i think it gets set when the workbook is saved so in an unsaved workbook it will miss added rows (can be fixed with a worksheet.save command i think)
zomg is offline   Reply With Quote
Old 09-12-2010, 04:07 PM   #42
SuperRams
enthusiast
 
Join Date: Apr 2010
Posts: 98
Re: Ask me anything about Microsoft Excel

Thanks for the replies. I have thought of something that contains blank data in one column causing me to offset the reference I can tweak tomo with the up technique

The array question is in Access so writing it to a temp table (not generally a fan of) then opening a recordset works fine, just wasn't sure if it was the most effective.

How would you get "in" with a company i.e. contract work? I've found working for large organisations it's easy to spot stuff that needs development but I can't think how you would get the freelance side of things.
SuperRams is offline   Reply With Quote
Old 09-12-2010, 04:13 PM   #43
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 z28dreams View Post
Here's one for you:

Do you have any best practices for handling the formatting of dates?

I've spent countless hours trying to convert various cells into a date format that I wanted, but with little luck.

For example, going between Jan 4th, 2010, 1/4/2010, and a more generic 1-2010 / Jan-2010 always results in nightmares for me.

Sometimes it seems to work perfectly, other times Excel refuses to cooperate. I'm aware of the Cell Formatting options (and even tried using the custom formatting for like M/YYYY), but it doesn't always work.

Any tips?
As someone who has moved his entire operation from Australia (DD/MM/YYYY) to USA i can safely say that excel and vba suck balls with date handling

But in saying that if you are in not moving continents then in the custom formatting you can do:

Jan 4th, 2010,
mmm ddd, yyyy
Note that you cant get the "th/nd" extensions without using external formula i.e. if the date is in cell A1, then if you put this formula in A2 it will format correctly (personally id just lose the extension, who cares)

=TEXT(A1,"mmm d")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","n d","rd","th","st","nd","rd","th","st"})&TEXT(A1 ,", yyyy")

1/4/2010
m/d/yyyy


1-2010 / Jan-2010
m-yyyy / mmm-yyyy
zomg is offline   Reply With Quote
Old 09-12-2010, 04:24 PM   #44
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 SuperRams View Post
Thanks for the replies. I have thought of something that contains blank data in one column causing me to offset the reference I can tweak tomo with the up technique

The array question is in Access so writing it to a temp table (not generally a fan of) then opening a recordset works fine, just wasn't sure if it was the most effective.
No problem, one of the things i love about excel/vba is there is always a better way to do something so i'm always learning

woops missed your question: I hate temp tables, i have found that they create so many more headaches in the long run. It's hard to say without seeing but have you looked at doing the whole calculation in the original SQL?

Quote:
How would you get "in" with a company i.e. contract work? I've found working for large organisations it's easy to spot stuff that needs development but I can't think how you would get the freelance side of things.
In short, I was in the same position as you but i socialised with the owners of the process i thought could be improved then offered to help them out and create solutions. I've designed systems that i could sell for hundreds of thousands for next to nothing while working for a company only to have them call me up years after i have left and ask for changes or more help.

I had a lady call me 3 years after i worked somewhere saying she spoke to such and such who told her that i could probably help her and this has now become my second largest account.

My newest client is a huge movie production studio that my friend was on the marketing team for, he told his boss that i could automate everything they were doing and i've done two contracts with them so far with 3 more on the way in the next couple of months. Note that most of the macros aren't complex at all and rarely take more than a day to complete

It's very much a word of mouth thing and one of the things i struggle with the most is getting new clients. Although thankfully I have enough work at the moment
zomg is offline   Reply With Quote
Old 09-12-2010, 05:16 PM   #45
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 ryanthe4aces View Post
In a pivot table is there anyway to use that data in a group of equations?

Aka lets say the data in a pivot is in E 3-30 and i want to multiple this data by data on another sheet that is lets say sheet 1 D 3-30. When i try this it never works. What am i doing wrong?
This is one of the reasons I don't use pivot tables, I'll run into something like this and realise that if i was not using pivot tables it would be much easier to display the data exactly how i want at the expense of a couple of seconds of processing time

Off the top of my head i'm sure you could write a user defined function (UDF) that you can then set the pivot table to have =mycustomsum(column). hopefully someone with a better understanding of pivot tables can chime in, i'd love to here more on it i'm sure there is an easier way

edit: you could add another column to the data with your calculation (you can hide it from view on the main data sheet if you want)
GETPIVOTDATA(data_field,pivot_table[,field1,item1]...[,fieldN,itemN])


EDIT: pivot tables are great and I highly recommend using them especially when your audience is somewhat competent. they can be dynamically created and destroyed which can drastically cut down on lead time for some of the smaller requests where users just want the ability to summarize/aggregate the data in different ways.

Last edited by PolvoPelusa; 09-12-2010 at 05:22 PM.
PolvoPelusa is offline   Reply With Quote
Old 09-12-2010, 05:18 PM   #46
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

I do the exact same thing for a large company in the US (although we funnel everything into a bunch of SQL Server databases via SSIS and just use Excel/Access for UI. I love the company I work for and don't want to go full-time contract, but picking up some extra dough on the side (especially at $50-90 /hr) doesn't sound bad at all.

Could you elaborate on how you find legit projects on Craigslist for that price range?
PolvoPelusa is offline   Reply With Quote
Old 09-12-2010, 05:39 PM   #47
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
I'd like to hear thoughts from others here who are further down the road than me. Ideally i'd probably like to move more into the consulting side and let someone else do the actual programming but have no idea
I doubt I'm "further down the road" than you, but my plan is to attack some of the project manager type roles at my current company as soon as I absorb a bit more of the business-knowledge (supply-chain) and people management skills. Simply put, I want to be the link between senior management and a team of my clones. Currently (at the company I work for), there are only a handful of these type of people (that I'm aware of) and they are treated like gold.
PolvoPelusa is offline   Reply With Quote
Old 09-12-2010, 05:44 PM   #48
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by SuperRams View Post
CopThis,

When you want a variable range find out how many rows and cols you have first then concatenate the variables.

iColumnCount= Range("A1").End(xlToRight).Column
iRowCount = Range("A1").End(xldown).Row

rgMyRange = wsMyWorksheet.Range("A" & iRowCount, "A" & iColumnCount)

Untested code fwiw.
Thanks, I'll fiddle about with coding when I'm next at work. I just find it fiddly - eg I'm sure that I've been able to use the range command when the variable is the row but not when the variable is the column. I think range("A" & var : "C" & var2) worked but range(var & "1" : var2 & "2") or something similar wouldn't - if you see what I mean.
CopTHIS is offline   Reply With Quote
Old 09-12-2010, 05:50 PM   #49
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,158
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
I don't want to get defensive but most of the things I do are not specifically front end which is why I suck at pivot tables. It's probably because i've never done a job that required pivot tables, and find that using vba to manipulate data produces better data sets in my experience.
What do you mean by this? What kind of things do you do?

I can't imagine a whole lot of things where Excel is the right tool for the job for back end work. At the bare minimum I could see writing a simple script to import the Excel data into Access for proper processing/querying/reporting.

Edit: Actually I think its super easy to import Excel data into Access using VB - but it's been a long time since I've done Excel/Access programming.
jjshabado is offline   Reply With Quote
Old 09-12-2010, 05:53 PM   #50
CopTHIS
Pooh-Bah
 
Join Date: Jan 2006
Posts: 4,094
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by jjshabado View Post
What do you mean by this? What kind of things do you do?

I can't imagine a whole lot of things where Excel is the right tool for the job for back end work. At the bare minimum I could see writing a simple script to import the Excel data into Access for proper processing/querying/reporting.

Edit: Actually I think its super easy to import Excel data into Access using VB - but it's been a long time since I've done Excel/Access programming.
fwiw I work as an actuary and we use spreadsheets for calcs and modelling all the time. For big jobs involving data we may use Access, and some specific jobs have their own software, but we are not programmers and Excel is so easy to use and ideal for what we do.
CopTHIS 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 05:11 PM.


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