Hey,

First off, just wanted to say this thread has been very helpful.

Now my problem
I am using the data>from web feature in Excel2007 and trying to import stats from NHL.com. The problem I am having is excel keeps on converting some of the stats to dates, which then renders them useless. Is there a way to turn off this auto-date conversion that excel keeps doing? Or is there another way to scrap daily data from nhl.com that I can use in an excel sheet?

Thanks.

-DontDoItPls

That would be AWESOME man. Thanks. I'm using 2007. I have about 15 different folders in various parts of my computer that have .doc files in them I use a lot. When I'm done with them, I throw them in a separate folder and I'll never mess with them again. So a big Excel file with a list of the folder names that auto-updates with how many .doc's I have would be unbelievably helpful.

I have excel data that I wanted to apply zebra (every third row highlighted) formatting for easier reading. I wanted the zebra affect to be shown even if I filtered the data. In order to use this I used conditional formatting and used the formula =MOD(SUBTOTAL(3,$A1:$A2),3)=2 The formula had the desired affect but I was curious about what the significance of the middle part of the function was ($A$1:$A2). Why are there dollar signs in front of both A and 1 but only one dollar sign if front of A2. I am also unclear as to what that part of the function does.

If you have any programming knowledge then forums/googling along with the macro recorder and then adapting code as you need is the by far the best way to learn. I don't know about you but personally i never retain from reading books only by using it in practice several times

I'm somewhere in tennessee at the moment and the internet blows so can't really test for you right now, but when you are importing you can go to "options" and there is a check box for "disable date recognition" (use the macro recorder to get the vba equivalent) if that doesn't work you could try manually set the formatting of the column to "general" after.

if you still can't get it to work let me know the exact link you are using and i'll try when i get a chance


Ok so i set it up that you list all the file paths (in format C:\ or C:\test\) in row 1 of the sheet you want then run "refreshLists"

right click on sheet tab > view code, on the left right click and go Insert Module then paste:

you could modify it to highlight ones that have been removed/added by removing the "clearcontents" line and adding a check on each line to see if it is equal but i'll leave that to you, there should be plenty there to get you started. You could also add an if right(ofile.name,4) = ".doc" if you just want word documents

NOTE: you will need to change the sheet name in both functions!

That's quite clever!

Here's how it works:

SUBTOTAL(3,Range) is a count of text values (in non-hidden rows i guess, cool!) in that range so A1:A2 will = 2 then MOD(2,3) divides 2 by 3 and returns the remainder (in this case 2/3 returns 2 as would 5/3 8/3 etc because there is 2 left over)

Regarding your first question, the $ signs are absolute references, they won't change when you copy and paste the cell. A $ in front of a column means that if you copy the cell one to the left/right the column won't automatically change (and the same for numbers up/down) the way it works is it will always start at A1:A2 then if you copy it down to A1000 it will be $A$1:$A1000 then the count will be 1000 in that row and mod(1000,3) would return 1 as 999 is the nearest multiple leaving 1 left over

it's kind of confusing to explain in words but hope that helps!

Worked perfectly. Many thanks.

-DontDoItPls

Hey,

Need your help again.

Situation
I am using several Web Queries to import similar data from different sites. Obviously when I import them they display differently since they are from different sources. I have come up with a formula to match up the data that is similar that works great.

=INDEX(Sheet2!B$2:B$31,MATCH(Sheet3!A:A,Sheet2!B$2 :B$31,0))

Problem
**My Formula is able to re-order Column B from Sheet2 to mimic Column A of Sheet 3, but I can't figure out how to get the data from Column C Sheet 2 to stay with Column B Sheet 2 when it re-orders to mimic Column A of Sheet 3.

Example of Data
Sheet 2
B--------------- C
Washington------5
Toronto--------- 4
Boston---------- 2

Sheet 3
A
Boston
Washington
Toronto

Thanks and hopefully this makes sense. If not, let me know and I will try to re-write.

-DontDoItPls

what are the best sites to brush up on pivot tables and v lookup?

I was taught to use INDEX / MATCH rather than VLOOKUP. I'm not a fan of VLOOKUP as it's less transparent - e.g., it's not always obvious what column is being referred to in a large table, and if a user inserts a column into the table it can mess up the formulae. Also INDEX/MATCH seem to work better with named ranges as far as I can tell.

But I seem to be in a very small minority. So what are the advantages of using VLOOKUP rather than INDEX/MATCH. Is it simply that it doesn't involve using a nested formula?

Second question. How do you avoid using multiple nested IF statements in XL (not VBA) - I'd like the equivalent of SELECT CASE....

I have no idea how to do what zomg told me. Can anyone help me? I'm super Excel noob, but not a moron so a tutorial or something would help me. Thanks.

I do the same only using OFFSET/MATCH, with the OFFSET reference being one cell above the column of cells I'm looking through with MATCH. Or if that data starts on row 1, just using the top cell and sticking a -1 at the end of the row argument, but that's usually not necessary since there's usually a header above my reference data that's not included in the MATCH reference. I like doing it this way because it allows me to only name a single column of cells as opposed to an entire table, which makes working with that data a lot easier in other parts of the worksheet considering a lot of functions will only accept a single-column reference.

I used to use VLOOKUP because early on in my Excel days I learned by modifying someone else's work and he used a lot of VLOOKUPs, so I have experience with both and I like my way better. No idea if this is a proper programming practice. I don't do this professionally. It works better for me though.

If this is possible it would be news to me. If you're not doing this already it helps a little bit to split up your cases as equally as practicable. Like instead of:

=if(A1="a",1,if(A1="b",2,if(A1="c",3,if(A1="d",4,i f(A1="e",5,if(A1="f",6,if(A1="g",7,8)))))))

you could do:

=if(and(A1>="a",A1<="d"),if(A1<="b",if(A1="a",1,2) ,if(A1="c",3,4)),if(A1<="f",if(A1="e",5,6),if(A1=" g",7,8)))

That might be a bad example cause the second case is a longer formula and is harder to read but I've come across situations where it helps, and if you want your workbook compatible with 2003 which only allows 7 layers of nesting it can really help.

Hey mate, sorry been in new orleans for the last week and it's been super messy

here you go:








Enjoy!

Will get to the rest of the questions shortly

How do you make certain columns/rows remain stationary while you scroll the rest of the fields?

Freeze pane/column/row

I have 9 probabilities of MLB players making outs. I want to run 100k simulations to see how often on average each player will bat until 27 outs are made. How?

zomg,

After I go to View Code > Insert Module and paste, I don't know what to do or change. I also don't know how to get to that Run Macro window you're on. Are the red highlighted parts ("Change here") where I paste in the location of the folders I want them to count up the .docs from?

Is it possible you just send me a working Excel file of this and I can just input the values? Apparently I'm even dumber than I thought at Excel.

I think the "change here" part is just referring to the sheet name. The name within the quotes on that line should be the name of the sheet where you have your folders listed across the top row. And by the sheet name I mean what it says on the tab at the bottom of the sheet that you right clicked on to get to the "View Code" option. If you never changed it to anything else, it will be "Sheet1". If for some reason you put it on the third sheet and didn't change the name of that one either it would be "Sheet3" and zomg's code would work without changing anything.

To get to the run macro window you can go to the View tab and click Macros->View Macros or press Alt+F8. Once you get it working like that it's only a few extra steps to get the macro to run when you click a button on the worksheet, open the workbook, or just about anything else you'd want to use as a trigger for updating your list.

I think this could be read a couple different ways but what I assumed you wanted was something like this:

Player 1 out 90% of the time, player 2: 80%, player 3: 70%...
Simulate Player 1 at bat and see if he's out
Simulate Player 2 at bat and see if he's out
Simulate Player 3 at bat and see if he's out
...
repeat until 27 outs, then report how many batters it took to get there.

If that's the case, list your 9 players probabilities in cells A1 through A9 in the format 0.xxx (any number 0 through 1.000). Right click on the bottom where it says "Sheet1" and click "View Code". Copy and paste this into the big empty text box that's taking up the majority of the screen:

Go back to your worksheet. Press Alt+F8. Run "Simulate". Wait a couple minutes. The results of your simulation will be listed in column B. If you want the average, you can type "=AVERAGE(B1:B100000)" into any empty cell.

NOTES:
If you're using Excel 2003 or earlier, or the compatibility mode in Excel 2007+, there is no row 100,000 and this code will give you an error once it reaches the last row. In that case I would change where the code says "100000" to "50000", run it once, then change where the code says "Cells(i, 2).Value = Batters" to "Cells(i, 3).Value = Batters", then run it again. This will put the results of your simulations into rows B and C, 50,000 results each. Change your average formula to "B1:C50000".

Even if you have Excel 2007+, it wouldn't be a bad idea to change the code like I mentioned above, only cut it down further, maybe 10,000 simulations in column 2. Then run it again using column 3, 4, etc until you have 10 columns of 10,000 simulations each. If you jump straight in to 100,000 your computer will lock up for a while until it's finished all of them. FWIW my old computer takes 5 seconds to run through 10,000 iterations of this code. I'm not going to try it at 100,000, but it would take longer than 5 sec*10, and even if it was only a minute, a single minute feels like a really long time when you're waiting to see if your code did what you want it to do.

Final note, I took your first sentence to mean exactly as you said it, the probability of each batter making an out. I'd expect your values to be on the upper side of .500, with higher values meaning worse batters. I don't know enough about baseball to know if that's a common stat, all I know is that if you're using batting averages or some other number where higher is better, this will be calculating backwards.

awesome, can't wait to try this. Thanks a ton.

Hey there,

Do you know of a good online course or book for learning to write excel macros? I just want to write some basic stuff, modelling casino side games etc.

worked perfectly, thanks again.

Thanks!

I played around with it and figured it out.

zomg,

Right now it looks as if I can only paste the folder location and get results from the the 1 cell. Would it be difficult to allow me to get results from folders pasted in lower cells so I can scroll down to view contents? I think the way around this is to either keep using 1B, 1C for folder locations or to start over on new sheets which seems pretty demanding.

Also is it possible to get the number of files in there too? Like, ideally I'd want the final product to look like this.

FOLDER #1

randomfile1.doc
randomfile2.doc
randomfile3.doc

3 DOC FILES

Folder #2

randomfile1.doc
randomfile2.doc
randomfile3.doc
randomfile4.doc
randomfile4.doc
randomfile6.doc

6 DOC FILES

And then I could insert the code to add those cells up and have 9 DOC FILES somewhere at the bottom. Heh. If this is too demanding for you to do in your spare time I understand. Thanks for all the help.

Lets say we have a list of customers with first name, last name, City and I want to find out the city John Doe lives in. There are many Johns and many Does but there is only 1 John Doe.

I imagine this is possible to do using a vlookup

I am doing this in a googlespread sheet.

=arrayformula(INDEX(data!$C$1:$C$92,MATCH(1,(data! $A$1:$A$92=B5)*(data!$B$1:$B$92=C5),0)))

where data!$C$1:$C$92 is the city information I want to grab

where data!$A$1:$A$92 is all first names with B5 being the first name I want to match

where data!$B$1:$B$92 is all last names with C5 being the last name I want to match

I am not sure what the leading 1 and trailing 0 in Match formula does.

I found this solution from http://www.pcreview.co.uk/forums/do-...-t2290819.html using a google search for spreadsheet using 2 search criteria.