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 10-03-2010, 11:31 PM   #201
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 22,372
Re: Ask me anything about Microsoft Excel

Most databases have a separate datatype for large chunks of text. In Access it looks like this is called "memo".

You should probably look at actual document database systems. A document database in Access is probably a bad idea (putting it in Excel is much worse).
jjshabado is offline   Reply With Quote
Old 10-04-2010, 08:37 AM   #202
HUHandEH
Pooh-Bah
 
HUHandEH's Avatar
 
Join Date: Dec 2009
Location: On the edge.
Posts: 5,279
Re: Ask me anything about Microsoft Excel

What about Alt F11? how much of that do you know?
HUHandEH is offline   Reply With Quote
Old 10-04-2010, 12:45 PM   #203
SuperRams
enthusiast
 
Join Date: Apr 2010
Posts: 98
Re: Ask me anything about Microsoft Excel

Edit: jj got there first!

And yeah, virtual document storage seems to be what you want otherwise you'll spend ages doing something that a. won't be as good as non-database document storage/filing solutions, b. wasting your time a bit.
SuperRams is offline   Reply With Quote
Old 10-09-2010, 05:05 PM   #204
joblessmba
journeyman
 
joblessmba's Avatar
 
Join Date: Feb 2010
Posts: 282
Re: Ask me anything about Microsoft Excel

I have an interview for a Jr. Financial Analyst position coming up this week.

Is there any Excel things I could study to prepare?

I am comfortable taking quarterly and yearly financial forms and generating forecasts and such.

Just not sure what an analyst would do on a day-to-day basis....seems like the accounting department would handle a majority of the data gathering.
joblessmba is offline   Reply With Quote
Old 10-11-2010, 09:09 AM   #205
subandi
Carpal \'Tunnel
 
subandi's Avatar
 
Join Date: Jun 2007
Location: working
Posts: 6,848
Re: Ask me anything about Microsoft Excel

very basic I think but I know nothing about excel.

I also dont have/use it I use google docs/open office.

I have a spreadsheet where every customer has a specific name and a specific number (plus other propeties),

in daily use I only need the name but for some reports I get
stats for the customers where they are only "named" by their number.

is it possible (and how?)
to make it that the program immediately "knows" that i.e. "123" means "subandi" "124" means "peter" and so on, and maybe even auto replaces all "123"s in that specific (name-) row with "subandi"?

aorn Im doing it manually which is easy but slow & very tedious.

thank you in advance for a help!
subandi is offline   Reply With Quote
Old 10-11-2010, 10:44 AM   #206
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,508
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by subandi View Post
very basic I think but I know nothing about excel.

I also dont have/use it I use google docs/open office.

I have a spreadsheet where every customer has a specific name and a specific number (plus other propeties),

in daily use I only need the name but for some reports I get
stats for the customers where they are only "named" by their number.

is it possible (and how?)
to make it that the program immediately "knows" that i.e. "123" means "subandi" "124" means "peter" and so on, and maybe even auto replaces all "123"s in that specific (name-) row with "subandi"?

aorn Im doing it manually which is easy but slow & very tedious.

thank you in advance for a help!
Should be pretty easily done with vlookup.

Assuming a table like that:
Code:
123 subandi
124 Peter
125 Richard
126 Paul
You could choose a field, for example A1, and write the name or number, then you put
Code:
=VLOOKUP(A1,A3:B6,2,TRUE)
in B1,
assuming A3:B6 is the area you got the values in, thats where it searches
2 is the column with the names
TRUE looks for exact matches.

I hope this is right and it should work, given I understood you correctly. (just saw that you were looking for something else, forget what I just wrote)


Does anyone, not necessarily OP, have a page with VBA projects/tasks? I need to motivate myself and learn VBA properly and I do it best by having a specific goal (I work with in a bank and use excel everyday, but we got all kinds of macros and they are too difficult to understand with my limited knowledge).
Spurious is offline   Reply With Quote
Old 10-11-2010, 11:09 AM   #207
Rinse Agent
journeyman
 
Rinse Agent's Avatar
 
Join Date: Dec 2007
Location: London
Posts: 309
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Spurious View Post
Should be pretty easily done with vlookup.

Assuming a table like that:
Code:
123 subandi
124 Peter
125 Richard
126 Paul
You could choose a field, for example A1, and write the name or number, then you put
Code:
=VLOOKUP(A1,A3:B6,2,FALSE)
in B1,
assuming A3:B6 is the area you got the values in, thats where it searches
2 is the column with the names
FALSE looks for exact matches.

FYLOOKUP
Rinse Agent is offline   Reply With Quote
Old 10-11-2010, 11:16 AM   #208
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Don't forget your numbers should be on the left, names on the right (if you're searching for the numbers).
Else you're formula should be:
Quote:
=INDEX(B3:B6,MATCH(A1,A3:A6;0))
Malfunction is offline   Reply With Quote
Old 10-11-2010, 11:18 AM   #209
RacersEdge
banned
 
RacersEdge's Avatar
 
Join Date: Jan 2004
Location: Bluff City
Posts: 14,289
Re: Ask me anything about Microsoft Excel

Not sure if OP is still around, so anyone who knows...

1) I use a VLOOKUP to convert city names to a number, i.e.

Chicage 1
Detroit 2
San Diego 3

but it always messes up San Diego, - gives it a number close by but not correct. I thought it was related to the spaces in San Diego, but other cities have spaces and they don't always get the wrong lookup value. Any idea what's going on? (I'm using "exact match" also).


2) What are the uses of Excel besides pivot tables that the average business office is missing out on?


3) What is best way to learn VBA? Is there a specific book?
RacersEdge is offline   Reply With Quote
Old 10-11-2010, 01:16 PM   #210
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,508
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Rinse Agent View Post
FYLOOKUP
Thanks, thats obviously what I meant
Spurious is offline   Reply With Quote
Old 10-11-2010, 03:53 PM   #211
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by RacersEdge View Post
Not sure if OP is still around, so anyone who knows...

1) I use a VLOOKUP to convert city names to a number, i.e.

Chicage 1
Detroit 2
San Diego 3

but it always messes up San Diego, - gives it a number close by but not correct. I thought it was related to the spaces in San Diego, but other cities have spaces and they don't always get the wrong lookup value. Any idea what's going on? (I'm using "exact match" also).

3) What is best way to learn VBA? Is there a specific book?
1) Whats your formula, sounds like you def did something wrong...

3) I liked Sams Teach Yourself Visual Basic for Applications (easily found as PDF somewhere)....rest I figured out myself (but I'm not great).
Malfunction is offline   Reply With Quote
Old 10-11-2010, 04:17 PM   #212
RacersEdge
banned
 
RacersEdge's Avatar
 
Join Date: Jan 2004
Location: Bluff City
Posts: 14,289
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Malfunction View Post
1) Whats your formula, sounds like you def did something wrong...

3) I liked Sams Teach Yourself Visual Basic for Applications (easily found as PDF somewhere)....rest I figured out myself (but I'm not great).
I use a named range, so it's just

VLOOKUP(A1,namedrange,2,0) where the city name is in column A and namedrange has 2 columns like I wrote above.

I just tried to get the error again, but couldn't get it - but I'm in Excel 2003 here and the issue was with 2007 at home. I have 32 names in the list and San Diego always gets the wrong value - the others are always correct.
RacersEdge is offline   Reply With Quote
Old 10-12-2010, 02:20 AM   #213
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Thats really weird.
I assume "namedrange" doesn't include A1?
Malfunction is offline   Reply With Quote
Old 10-13-2010, 05:28 PM   #214
Hellrazor
grinder
 
Join Date: Jul 2003
Location: 'Jersey
Posts: 640
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by RacersEdge View Post
I use a named range, so it's just

VLOOKUP(A1,namedrange,2,0) where the city name is in column A and namedrange has 2 columns like I wrote above.

I just tried to get the error again, but couldn't get it - but I'm in Excel 2003 here and the issue was with 2007 at home. I have 32 names in the list and San Diego always gets the wrong value - the others are always correct.

Any spaces on the end of San Diego in the table?
Wasn't able to recreate this unless I added the space to San Diego in the namedrange.

Try inserting a =TRIM(Citynamecol1) in the second column of the namedrange and vlookup on that. Or CLEAN() instead of trim if you imported the text from another application that may have some non-visible codes in the text.
Hellrazor is offline   Reply With Quote
Old 10-13-2010, 05:52 PM   #215
RacersEdge
banned
 
RacersEdge's Avatar
 
Join Date: Jan 2004
Location: Bluff City
Posts: 14,289
Re: Ask me anything about Microsoft Excel

I'll try that - probably CLEAN since I did paste this from another source.
RacersEdge is offline   Reply With Quote
Old 10-13-2010, 07:25 PM   #216
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 RacersEdge View Post
I'll try that - probably CLEAN since I did paste this from another source.
It's usually the 'space' that causes the problems, ive had issues where i've had to delete and re-do the space and the v-lookup works. you can try finding out the character code of the space then doing a find and replace on that code

i.e.

=SUBSTITUTE(A1,CODE(MID($A$1,4,1))," ")

where $A$1 is the aforementioned "san diego" if you just use "CODE(MID($A$1,4,1))" it should return 32 which is the standard ASCII code for a space


I'm not familiar with whether the clean function would handle this
zomg is offline   Reply With Quote
Old 10-13-2010, 08:03 PM   #217
zomg
Excelling at Flash
 
zomg's Avatar
 
Join Date: Nov 2006
Posts: 1,488
Re: Ask me anything about Microsoft Excel

For those of you that are NBA fantasy players, i made a macro that will import the top 500 players into excel but you need to have a valid fantasy log in.

1. Open excel, on the data tab go to Get External Data > From Web
2. in the box that opens navigate to fantasy NBA (http://basketball.fantasysports.yaho...sort=OR&sdir=1) and log in and save password
3. close the above box then right click on a sheet tab and select "view code"

Code:
Sub importNBA()

    Dim tmpWS As Worksheet
    Dim fWS As Worksheet
    
    For i = 0 To 19
        Application.StatusBar = i
        Set tmpWS = Sheets.Add
        With tmpWS.QueryTables.Add(Connection:= _
            "URL;http://basketball.fantasysports.yahoo.com/nba/222/players?status=A&pos=P&cut_type=33&stat1=S_S_2009&myteam=0&sort=OR&sdir=1&count=" & i * 25 _
            , Destination:=tmpWS.Range("$A$1"))
            .Name = _
            "players?status=A&pos=P&cut_type=33&stat1=S_S_2009&myteam=0&sort=OR&sdir=1&count=" & i * 25
            .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
        
        tmpWS.Rows("1:120").Delete Shift:=xlUp
    
        For p = tmpWS.Range("A65000").End(xlUp).Row To 1 Step -1
            If Left(tmpWS.Range("A" & p), 1) = "(" Then
                tmpWS.Range("B" & p - 1) = tmpWS.Range("A" & p)
                tmpWS.Rows(p).Delete
            End If
        Next p
        If i = 1 Then
            Set fWS = Sheets.Add
            tmpWS.Range("A1:Z27").Copy Destination:=fWS.Range("A1")
        Else
            tmpWS.Range("A3:Z27").Copy Destination:=fWS.Range("A" & fWS.Range("A65000").End(xlUp).Row + 1)
        End If
        Application.DisplayAlerts = False
        tmpWS.Delete
        Application.DisplayAlerts = True
    Next i
End Sub
zomg is offline   Reply With Quote
Old 10-13-2010, 08:10 PM   #218
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 Spurious View Post
Does anyone, not necessarily OP, have a page with VBA projects/tasks? I need to motivate myself and learn VBA properly and I do it best by having a specific goal (I work with in a bank and use excel everyday, but we got all kinds of macros and they are too difficult to understand with my limited knowledge).
mrexcel.com forums

seriously, post questions answer other peoples questions when you have time its like this thread times a million
zomg is offline   Reply With Quote
Old 10-14-2010, 01:55 AM   #219
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,508
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by zomg View Post
mrexcel.com forums

seriously, post questions answer other peoples questions when you have time its like this thread times a million
Thanks, gonna do this!
Spurious is offline   Reply With Quote
Old 10-14-2010, 05:40 AM   #220
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,508
Re: Ask me anything about Microsoft Excel

Is there a way to just add something to an array instead of having to define which index? (Java has this)

And is it possible to just take a range like "A1:C9" and put it in an array? Or do you have to go through every single cell and add them?
Spurious is offline   Reply With Quote
Old 10-14-2010, 07:39 AM   #221
subandi
Carpal \'Tunnel
 
subandi's Avatar
 
Join Date: Jun 2007
Location: working
Posts: 6,848
Re: Ask me anything about Microsoft Excel

just want to say thank you to spurious, rinse agent and malfunction,
didnt come around trying it yet because I didnt have to
but it looks like I what imagined and Im sure it will work - so thank you!
subandi is offline   Reply With Quote
Old 10-14-2010, 08:18 AM   #222
PolvoPelusa
adept
 
Join Date: Aug 2008
Location: Century Laboratories
Posts: 974
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by RacersEdge View Post
2) What are the uses of Excel besides pivot tables that the average business office is missing out on?


3) What is best way to learn VBA? Is there a specific book?


2. pivot tables/charts based on cubes are insanely useful from a BI perspective.

also, something that's sure to be hot with users who have a decent understanding of relational databases is powerpivot in 2010. publishing to sharepoint is pretty big...offloading processing to the server + rendering the display in HTML keeps users from ****ting the bed.

3. find a project that interests you and dissect it to discover how it works. if you don't know what a line of code does, use google
PolvoPelusa is offline   Reply With Quote
Old 10-16-2010, 12:09 PM   #223
subandi
Carpal \'Tunnel
 
subandi's Avatar
 
Join Date: Jun 2007
Location: working
Posts: 6,848
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Spurious View Post
Should be pretty easily done with vlookup.

Assuming a table like that:
Code:
123 subandi
124 Peter
125 Richard
126 Paul
You could choose a field, for example A1, and write the name or number, then you put
Code:
=VLOOKUP(A1,A3:B6,2,TRUE)
in B1,
assuming A3:B6 is the area you got the values in, thats where it searches
2 is the column with the names
TRUE looks for exact matches.

I hope this is right and it should work, given I understood you correctly.
I got it kinda working but still requires me to enter/change the 1st variable for each lookup,

what I need is something like

=vlookup(R3,A1:B70,2,false)
=vlookup(R4,A1:B70,2,false)
=vlookup(R5,A1:B70,2,false)
..

without having to copy and paste then edit it for every line.

fwiw the line where the vlookups are are in Q

so is there something like a universal placeholder that says
"take the # of this field and put an R before it"
so it gives out R3 when put in Q3 and R4 when in Q4 and so on..?

that way I would only have to paste "=vlookup(X,A1:B70,2,false)"
where X is the variable Im looking for..

does something like this exist?

also, even more basic question,
when I enter 1.89 into a field it display it as 01.01.89 how can I disable that?

it obviously thinks its a date..

thank you very much in advance for any help!
subandi is offline   Reply With Quote
Old 10-16-2010, 12:22 PM   #224
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,508
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by subandi View Post
I got it kinda working but still requires me to enter/change the 1st variable for each lookup,

what I need is something like

=vlookup(R3,A1:B70,2,false)
=vlookup(R4,A1:B70,2,false)
=vlookup(R5,A1:B70,2,false)
..

without having to copy and paste then edit it for every line.

fwiw the line where the vlookups are are in Q

so is there something like a universal placeholder that says
"take the # of this field and put an R before it"
so it gives out R3 when put in Q3 and R4 when in Q4 and so on..?

that way I would only have to paste "=vlookup(X,A1:B70,2,false)"
where X is the variable Im looking for..

does something like this exist?

also, even more basic question,
when I enter 1.89 into a field it display it as 01.01.89 how can I disable that?

it obviously thinks its a date..

thank you very much in advance for any help!
I dont know if there is a loop function for regular excel, I'd only knew a solution for your problem using VBA.

You can just go to format (Format), cells (Zelle(n)), then change the format from Date to Number.
Spurious is offline   Reply With Quote
Old 10-16-2010, 03:36 PM   #225
Malfunction
old hand
 
Join Date: Jul 2008
Location: Limping sooted one-gappers
Posts: 1,719
Re: Ask me anything about Microsoft Excel

Quote:
I got it kinda working but still requires me to enter/change the 1st variable for each lookup,
Subandi you're kinda asking 2 questions.
First one requires the use of dollarsigns:
You use dollar-signs to tell Excel not to auto-change your range as you drag it down/right/left/up etc.

Put =A1 in B1 and drag it down gives =A2.
Put =A$1 in B1 and drag it down gives =A1
Put =$A1 in B1 and drag it right gives =A1

Depending where you put the dollarsign (in front of column or row) Excel won't change it. Obv you can use 2 dollarsigns to fix a position forever.
IF you ever drag a range to use in a formula, hit F4 to toggle the different options.

So you're solution would be:
=vlookup(R3,A$1:B$70,2,false)

and dragged down.

Quote:
So is there something like a universal placeholder that says
"take the # of this field and put an R before it"
so it gives out R3 when put in Q3 and R4 when in Q4 and so on..?
This is a different question!
=VLOOKUP(INDIRECT(ADDRESS(ROW(),18)),$A$1:$B$70,2, false)

ADDRESS gives a 'cell' according to row/column. So ROW() gives 4 when put in row 4 (say Q4!) and 18 gives 'r' as R is the 18th column.

INDIRECT tells Excel to use the result of ADDRESS(ROW();18), which is $R$4, as a range.
So actually, when put in row 4, your formula looks like this:
=vlookup(R4,$A$1:$B$70,2,false)

But when put in row 1598 your formula looks like this:
=vlookup(R1598,$A$1:$B$70,2,false)

.

Hope this helps!
Malfunction 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 07:04 PM.


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