|
ActiveSheet.Paste should be sufficient with you selecting Sheet11.Cells(3,1) before hand. I've never had to use Destination:=... before. Its possibly that. Should you expect Excel to split vbTab and vbNewLines into columns and rows? You're making a formatted string which I assume would just paste into a single cell as the string you defined (but I've not used clipboard either). Consider your first method, but put Application.ScreenUpdating = false at the start of your routine and true at the end. Screen updating is often the slowest part of a massive loop, I've found.
|
#
?
Aug 2, 2011 08:55
|
|
|
|
| # ? May 11, 2024 15:23 |
|
|
gwar3k1 posted:ActiveSheet.Paste should be sufficient with you selecting Sheet11.Cells(3,1) before hand. I've never had to use Destination:=... before. Its possibly that. Activesheet.paste just puts it all into A3 however vbnewline enables it to actually go down the right number of rows just all the row data ends up in the one column ( it won't paste as recognizing the vbtab should be forcing it to spam the rows like in my output for the for loop. Unfortunately, it is 1 billion too slow to populate the entire thing using the for loop as the populator even with screen updating off ( this has to go through thousands of records ). Before you tell me this shouldnt be done in excel I know but I do what I'm contracted to  Desperately need help with this one
|
|
#
?
Aug 2, 2011 11:49
|
|
|
Baxta posted:Activesheet.paste just puts it all into A3 however vbnewline enables it to actually go down the right number of rows just all the row data ends up in the one column ( it won't paste as recognizing the vbtab should be forcing it to spam the rows like in my output for the for loop. How about this: Paste it to a text file, save. Open as tab delimited, copy Paste in required workbook, close & delete text file
|
|
#
?
Aug 2, 2011 18:13
|
|
|
gwar3k1 posted:How about this: Hmm I'll see how that affects performance. Wish I could just paste tab delimited. I'm sure it's possible as when I'm debugging, the string is saving it with the tabs.
|
|
#
?
Aug 2, 2011 22:06
|
|
|
Microsoft is inclined to agree. Try vbCr instead of vbNewline. Maybe even trim the last vbTab before each newline?
|
|
#
?
Aug 2, 2011 22:28
|
|
|
gwar3k1 posted:Microsoft is inclined to agree. Try vbCr instead of vbNewline. Maybe even trim the last vbTab before each newline? OK apparently my code was alright after all. Now I have an even stupider problem. It works perfectly on windows 7 with office 2007 (all references the same) but doesnt paste properly in windows vista with office 2007 (references still the same. I hate VBA so much. EDIT: Just in case anyone was wondering, ActiveX controls dont work with Office for Mac 2011. Update: Apparently opening a workbook with macros on office for mac then saving it on the mac makes it hosed for windows office. Havent found out exactly why yet. Baxta fucked around with this message at 07:16 on Aug 5, 2011 |
|
#
?
Aug 3, 2011 00:13
|
|
|
Hope someone can help me, i think this is a pretty basic thing to do but i'm rather lost. I want to change a long list of data organized by mm/dd/yyyy to dd/mm and year by column. I have like 50 spreadsheets with 100 years worth of data by day in each one. Starting like this: 1/1/2010 1 1/2/2010 4 1/3/2010 7 etc So it would end up looking like this: 2010 2009 2008 1-Jan 1 2 3 2-Jan 4 5 6 3-Jan 7 8 9 Anyone know an easy way to do it?
|
|
#
?
Aug 9, 2011 16:22
|
|
|
Add a new column called year, and in that column fill the formula =year(A2) where A is the column that contains your date. Then on a new tab, create a pivot table, use the year column you created as the column, the date as the row (format as MM/DD or however you want), and put the value in the data section. Edit: I see you have this data in many places. I would suggest combining it into one sheet. If it won't fit, I'd suggest dumping it into a single DB table and then building the pivot on that table.
|
|
#
?
Aug 9, 2011 21:07
|
|
|
Baxta posted:EDIT: Just in case anyone was wondering, ActiveX controls dont work with Office for Mac 2011. Have you moved from 2003 (or the Mac equivalent) to 2011? Do you know that .xlsx files don't allow macros where as .xlsm do? Sorry if you already know this, but its the first thing that I'd check. Same goes for templates: .xltm allows macro use and isn't the default.
|
|
#
?
Aug 9, 2011 21:30
|
|
|
gwar3k1 posted:Have you moved from 2003 (or the Mac equivalent) to 2011? Do you know that .xlsx files don't allow macros where as .xlsm do? Sorry if you already know this, but its the first thing that I'd check. Same goes for templates: .xltm allows macro use and isn't the default. Yeah its not the issue with the macros. Something in office for mac 2011 does something to the library references in the xlsm. Even if you just open and save on a mac, it screws up the xlsm. I solved the problem by running parallels.
|
|
#
?
Aug 10, 2011 01:44
|
|
|
I have got a new position in my IT department in my organization as head of budget/purchasing for IT-related expenditures. The previous two people (one was an older lady who did everything from an accounting background and no IT knowledge, the other is my boss, the main system administrator, who was simply too swamped with work to be able to keep up with purchasing, managing licenses, balancing budget, etc,) left a slew of spreadsheets containing records of budgets, purchases, etc, but they really didn't know what they were doing and it's pretty obvious by the way you have to enter information into 2-4 different documents every time you do anything.. It's a nightmare since it's a jumbled and confused mess of workbooks which if you (me) neglect to enter something into any one of them, it can very easily skew budget estimates and cause issues when things come down to the wire at the end of the fiscal year. Additionally, I've got a workbook for our color printers, which I created years ago to track toner usage, "mileage" on number of printouts on each device, etc, with some dynamically expanding charts and summary pages so they can easily be waved under the noses of people when I need to press for more money in that area, why we want to buy another device to lighten the load on a particular spot which is getting hit harder than the rest, etc.. Now I'd like to seriously look at cleaning up the budget spreadsheets and combining most of them into one master document to reduce the repetition and chance for data entry errors and things which can get accidentally overlooked in the current mess, and eventually make a master printer inventory spreadsheet which tracks 35-45 printers on a number of factors. *I'm hoping someone here would have some suggestions on any books or websites or training videos or online courses regarding larger spreadsheet projects. I am a bit rusty but have a good working and educational background with spreadsheets including VBA and overall programming experience, but I'm kind of rusty and a bit overwhelmed, so I'm at a but of a loss as to where to begin so I'd hopefully like to find some good resources on taking on a project like these and wrangling them into something more manageable. I've considered using Access for the printer inventory stuff, but I am not entirely sure that it's necessary as there's not THAT much information to track, and frankly I'm terrible with Access but pretty comfortable with Excel, and as they say when your only tool is a hammer..  ------- Years back I asked in the old Excel thread here, how to make a chart which would dynamically expand as I added data.. And nobody answered. I recently looked around again and found out how: use a named range, or a table, as the data source. I'm not sure if this functionality was around in Excel 2003, but it's certainly there in 2007 and 2010, and goddamn is it ever useful. 
coyo7e fucked around with this message at 17:19 on Aug 11, 2011 |
|
#
?
Aug 11, 2011 17:14
|
|
|
Start by rationalising (normalising?) your data. Know what fields/layout you need on your master spreadsheet and design the interface that will feed into that. Then if necessary, create the tools required to migrate from the multiple documents into the single document. When you're done making your life easier, move the originals out of their current locations so your users cannot use them. Force them to use the single workbook or they never will. I've recently moved into a job where we use Spiceworks which covers all the stuff your boss couldn't keep track of, as well as toner use and a whole treasure chest of amazing stuff. We primarily use it for the helpdesk system though. Perhaps you could look into that as a useful piece of software.
|
|
#
?
Aug 11, 2011 18:33
|
|
|
I see what you're saying, and it's certainly possible. I think you'd have normalize/rationalize all your spreadsheet data first though, so you don't have the same redundancies in whatever database you end up with. Maybe check out these links: http://en.wikipedia.org/wiki/Database_normalization http://www.databasedev.co.uk/database_normalization_basics.html Once the sheets are de-duped it'll probably be way easier for you to proceed; I think that you're still at the organization part of this project, and it's premature to say if you'll be using Access/SQL/etc. That said, I suggest you actually go with some accounting package at this point because while you will be able to fake out a lot of the functionality, at some point you're likely going to need a 'real' system anyway. It depends on the size of the organization but the 'best' solution organizationally is probably to start from scratch. Here's some packages based on scale that I am in no way endorsing, but have noticed most businesses using at the various sizes: - SimplyAccounting (small) - QuickBooks/Pro (small/medium) - Microsoft Dynamics/Great Plains (Medium/Large/Possibly very large if you hate yourself)
|
|
#
?
Aug 11, 2011 18:37
|
|
|
Rationalizing, that's what I was looking for, thanks!gwar3k1 posted:I've recently moved into a job where we use Spiceworks which covers all the stuff your boss couldn't keep track of, as well as toner use and a whole treasure chest of amazing stuff. We primarily use it for the helpdesk system though. Perhaps you could look into that as a useful piece of software.  As for the budgeting stuff, we're a government funded non-profit which entails a lot of oddities in the system (read: specialized software which tends to cost a ton or not really fit our organization), and thanks to our wonderful government, we don't have any budget to speak of for "frivolities" like budget software for the foreseeable future, we're already looking at serious budget cuts for the next several years.
|
|
#
?
Aug 11, 2011 22:52
|
|
|
Excel 2007: So, I want to be able to put a horizontal line (i.e. a border) between data from different clients - automatically, e.g. What I have: code:code:- Set up a conditional formula on cell A3 to put a border on the top of the cell if the value in A2 doesn't match the one in A3, making sure to take out the '$' on the row value (so $A3 instead of $A$3) so the cell references will move. - Copy the cell, then 'paste special - formats only' into the next cell across (B3). - Copy A3-B3, 'paste special - formats only' into C3. - Repeat that until the conditional format is across all the rows you need it. - Copy 3:3, 'paste special - formats only' into row 4. - Copy 3:4 (rows 3 & 4), 'paste special - formats only' into row 5. - Repeat that until Excel crashes with an out-of-memory error.  fe: Wow I'm retarded. It turns out I can do this just by selecting A3:<lower right corner of data block>, then conditional formatting -> new rule -> use a formula [bottom option] -> formula: =$A2<>$A3 and set custom format: border top. Once again, Excel proves to be significantly smarter than I am. 
|
|
#
?
Aug 12, 2011 06:49
|
|
|
This may be a dumb question but I can't figure out how to do this clearly in Excel 2010 for some reason.. Combo boxes. I'm drawing a blank on how to make a combo box spit out anything besides a raw integer "1, 2, 3, 4" etc. I made this fringe benefits calculator for some of our budget people today to warm up my (sadly rusty) excel muscles after one of them asked me if I could add "some checkboxes and stuff" so they didn't keep inputting the same poo poo over and over, and ended up having to use a nested if statement because I just couldn't remember how to do poo poo with Combo Boxes correctly.. Also I'm in 2010 so things are named a bit differently than I remember them being in 2003. Link to worksheet for anyone curious as to how badly I screwed this up (yes I know it's misspelled, the e is for "edited to remove identifying info"). Macros are necessary to use the reset button and checkboxes, but not to see what I was doing, if you're  like I am.
|
|
#
?
Aug 12, 2011 23:02
|
|
|
So I've recently come into working with some very large-scale (upwards of 5,000 lines per worksheet, 7+ worksheets per workbook) financial models in Excel. As-is, they are already pushing the physical limits of our computing power, and rely on a lot of opaque hlookup and vlookup functions to draw from massive data arrays and manipulate them into summary reports. The manipulation of these data is pretty basic, nothing beyond some regression analysis and algebra. However, we are looking at options to expand the dimensions of these models in ways that will clearly break Excel. Specifically, we want more flexibility with multi-variable inputs and associated reporting (scenario analyses if that means anything to you). This would involve adding 10+ worksheets to already sluggish workbooks. What direction should I be moving in, if I want to expand and make adjusting input data and report formatting straight-forward for non-technical users? I'm thinking Access, and skipping over VB for PowerShell. Will I still be using Excel for manipulation? We have SQL servers already set up for our GIS department that also may be of use. If my background isn't clear, I am more experienced with the theoretical construction of these models and less with the tools. However, I'd like to move more into database, SQL, and simple development skills as we do bigger and more frequent projects like these. Where should I be looking?
|
|
#
?
Aug 16, 2011 19:24
|
|
|
coyo7e posted:This may be a dumb question but I can't figure out how to do this clearly in Excel 2010 for some reason.. Combo boxes. I'm drawing a blank on how to make a combo box spit out anything besides a raw integer "1, 2, 3, 4" etc. You need to use a ActiveX combo box rather than just a Form box. The Form Box will return the key for the list item selected - e.g., if someone selects the 4th item, it returns 4. You can use that with a VLookup if you want, or you can use an ActiveX box which will hold actual values. I made a huge post with instructions on using ActiveX combo boxes to create a dynamic chart a couple of pages back, everything you need to know should be in there.
|
|
#
?
Aug 16, 2011 19:58
|
|
|
ZerodotJander posted:You need to use a ActiveX combo box rather than just a Form box. The Form Box will return the key for the list item selected - e.g., if someone selects the 4th item, it returns 4. You can use that with a VLookup if you want, or you can use an ActiveX box which will hold actual values. I have a few reference books but none of them go in-depth about the activeX crap, I'd need yet another book for that, apparently..
|
|
#
?
Aug 17, 2011 23:59
|
|
|
I would like to have Excel take a look at Column A, run a google search, and spit the first result into Column B. My Excel skills are beginner at best (I have a degree in econ so I can run regressions, and I can successfully make a pivot table... sometimes) Seems like a simple enough script, but I don't even know where to start. I'm using Excel 2007. edit. Upon further research I realized this is way harder than I thought. Any input is appreciated though. I Love Topanga fucked around with this message at 18:34 on Aug 19, 2011 |
|
#
?
Aug 19, 2011 17:22
|
|
|
I Love Topanga posted:I would like to have Excel take a look at Column A, run a google search, and spit the first result into Column B. First, do a google search and figure out where your query goes in the URL. Then record a macro that: Uses Web Data (Data > Get External Data > From Web) [Paste your query url in the address bar] Select the page (click the yellow box with an arrow in it) Saves the web query to a new sheet Copy the cell with the link you want (first result) Paste it into your sheet B1 Delete the web query sheet When you stop recording, you can see the VBA that Excel produced by going into the IDE (Alt+F11). If you're comfortable with code, mess about with it.
|
|
#
?
Aug 19, 2011 19:40
|
|
|
I'm pretty sure what you want to do is a violation of Google's TOS. They used to have a SOAP API that you could use for something like this (up to 1k queries/day) but they are no longer giving out licenses for that. You could put this lovely formula into a cell of column B: quote:=HYPERLINK("http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&btnI=Im+Feeling+Lucky&q="&A1,"I'm Feeling Lucky") This would create a hyperlink in the cell that, when clicked, will open up the Google I'm Feeling Lucky result for whatever text is in cell A1.
|
|
#
?
Aug 19, 2011 21:00
|
|
|
gwar3k1 posted:First, do a google search and figure out where your query goes in the URL. I tried this, and it didn't really work the way I was hoping. Although I may have not been following directions correctly. The problem lies in Google not always displaying the URL in the same cell. With the # of ads being inconsistent the URL would appear anywhere between cells 85-100. I tried doing this using Google's "Are you Feeling Lucky?" and it got close. Except it seems as though the macro isn't actually copying and pasting the words actively, just as input. Here is the output from my previous try: code:For example code:I Love Topanga fucked around with this message at 21:39 on Aug 19, 2011 |
|
#
?
Aug 19, 2011 21:36
|
|
|
Sub Par posted:I'm pretty sure what you want to do is a violation of Google's TOS. They used to have a SOAP API that you could use for something like this (up to 1k queries/day) but they are no longer giving out licenses for that. I didn't even think of the Google TOS... This probably would be in violation of that. I'll have to see if I can get a license for the API. If I can get this to work I'll have to make sure that I'm using it without violating the TOS with page views/day etc. You're formula is one step closer. Now all I need is to have it just display the result.
|
|
#
?
Aug 19, 2011 21:44
|
|
|
Is there any reason why it has to be the top Google result? If you were to make it the wikipedia page, that would probably be easier. Also, in most cases, it probably is the top result anyway.
|
|
#
?
Aug 19, 2011 23:56
|
|
|
You can actually very easily do what you are looking to do with Google Spreadsheets and the importXML function. =importxml("http://www.google.com/search?q="&B2&"&pws=0&num=1","//h3[@class='r']/a/@href") Enter the keyword you're searching for into cell B2. This constructs a Google search results page URL for that keyword, with personalized search turned off (pws=0) and only the top result (num-1). The importxml function then runs a XPATH query on the page (since all webpages are xml documents) and returns the HREF from the Anchor tag that is enclosed in an H3 tag with class=r - which happens to be the URL for a result. One spreadsheet can only support 50 importxml queries, but it's still really useful.
|
|
#
?
Aug 20, 2011 03:55
|
|
|
I'm trying to visualize correlation between two sets of values as a scatter plot, except that one of the values is non-numeric. I have eight servers, which have experienced a particular critical event on one or more dates. I would like to create a scatter plot showing the date on the X-axis, and the server name on the Y-axis. However, since the server name is non-numeric, Excel won't let me plot it. What's the easiest way to do what I want?
|
|
#
?
Aug 26, 2011 17:13
|
|
|
Assign numeric values to the servers and then edit your axis labels.
|
|
#
?
Aug 26, 2011 18:11
|
|
|
I need to create some rather large excel files (100k+ rows, 200ish columns, 10 worksheets). I tried using Perl, but it doesn't free memory and eventually runs out. Do any scripting languages (the more *nix friendly the better) have the ability to create such large excel files?
|
|
#
?
Aug 30, 2011 18:46
|
|
|
uG posted:I need to create some rather large excel files (100k+ rows, 200ish columns, 10 worksheets). I tried using Perl, but it doesn't free memory and eventually runs out. Do any scripting languages (the more *nix friendly the better) have the ability to create such large excel files? Of random data? Can you just use VBA to do this? I assume you're using 2007+ as 2003 and below cap at 65536 rows or there abouts. Is the excersize to have it create an xls within Excel, or could you create 10x csv files and import those as sheets into an Excel workbook?
|
|
#
?
Aug 30, 2011 21:28
|
|
|
gwar3k1 posted:Of random data? Can you just use VBA to do this? I assume you're using 2007+ as 2003 and below cap at 65536 rows or there abouts. I can do this manually in excel by opening all the csv files and dragging their sheet tab to another sheet, and saving as xls. But i'm trying to avoid that so I don't have to download the files to a windows machine, then reupload them to the *nix server.
|
|
#
?
Aug 30, 2011 21:53
|
|
|
I have a question about how to consistently make use of the autofill feature in Excel. Basically I have a data table where a couple of columns have user-entered data, and the rest are formulas that output a result based on the entered data. Sometimes when the data is entered, the columns that have the requisite user-entered data automatically calculate the result and display it without me having to drag down the previous row, but I've never been able to get this to consistently happen. Anyone know how to make this happen?
|
|
#
?
Aug 31, 2011 13:26
|
|
|
uG posted:Well my Perl script is creating a bunch of csvs, from which i'm trying to group some of them together in a workbook as worksheets with a secondary script. I'm working with multiple large files here that are located on a *nix server, so i'm preferring to do this without windows/excel (so I don't have to keep downloading/uploading). I can do all of this with Perl, except it runs out of memory very early on due to the way Perl handles memory. I briefly looked at Python and PHP, and it seems they either couldn't write more than 65536 rows or had memory limitations. If you're doing straight ascii CSV you might be able to do it in batches (with distinct entry/exit so memory gets freshed) and then just concatenate the files together into one mega CSV. What's the source, is it a database?
|
|
#
?
Aug 31, 2011 19:07
|
|
|
Scaramouche posted:If you're doing straight ascii CSV you might be able to do it in batches (with distinct entry/exit so memory gets freshed) and then just concatenate the files together into one mega CSV. What's the source, is it a database?
|
|
#
?
Sep 2, 2011 16:09
|
|
|
Edit: the answer is no.
AJzer fucked around with this message at 21:26 on Sep 2, 2011 |
|
#
?
Sep 2, 2011 19:02
|
|
|
I am not the best Excel user ever, and I have a few questions I can't find answers for. I have two databases of information. In both of these databases I have Latitude, Longitude, "Heat Flow" and a bunch of other columns. Now I need to compare both of these databases with each other, and find which points have different heat flow numbers. Currently I have both databases mixed into one huge one, and I figured out how to sort the Latitude and Longitude so both databases are lined up by location. This way I see that one database reports one number for heat flow, and the other one reports another one (usually the same) at the same co-ordinates of lat and long. However there are a bunch of unique entries that do not occur in both data sets. Is there an easy way for excel to locate these and remove them? I am not sure how this works since both the latitude and longitude define one point. So if lat is x and long is y AND no other points have BOTH lat x and long y sort of thing.
|
|
#
?
Sep 2, 2011 19:31
|
|
|
Goodpancakes posted:I am not the best Excel user ever, and I have a few questions I can't find answers for. I think this would be easier done in a database. What database is everything stored in?
|
|
#
?
Sep 4, 2011 12:36
|
|
|
G-Dub posted:I think this would be easier done in a database. What database is everything stored in? I was literally given a text print-out and an excel spreadsheet to start this. Is this something that could be done in, say, access?
|
|
#
?
Sep 6, 2011 22:51
|
|
|
I have a range of dates I want to conditionally format based on old-new. Easy peasy. However, I only want to format some of those cells based on the value of another cell. For example: code:Any ideas?
|
|
#
?
Sep 7, 2011 15:07
|
|
|
|
| # ? May 11, 2024 15:23 |
|
|
You would want to have a couple rules. The first one would be to have no formatting where where the following formula is true: =B1="Y" and you would want to specify that if that rule is true, Excel should stop. The second rule should be to format based on the values of the A column. Here's as screenshot of the rules I set up:  And here's how it looks: 
|
|
#
?
Sep 7, 2011 18:21
|
|