|
22 Eargesplitten posted:VBA: Is there any way to make a macro run while letting you edit other stuff in the file still? Thanks to people demanding everything now, now, now, I've got macros that take 30 loving minutes and freeze up every instance of Excel while that's happening. I know almost no VBA. 1) Try to make your macro faster. This usually involves minimizing interaction with the Excel object model, for example by getting the data from cells as a single access to range.value (returning an array) rather than iterating through the individual cells. 2) Use another programming language in some form (which still won't really help if you have no choice but to interact with the Excel object model). Can you describe what is specifically taking so long in your macro?
|
#
?
Sep 7, 2017 04:03
|
|
|
|
| # ? May 12, 2024 22:38 |
|
|
We had an old set of activity codes, which weren't supposed to be used on this project. Early on, before we locked it down, some people (including VPs) did it anyway. Now every time I update the project I have to convert those old codes into the new codes, or the people who need to use the report (including some of the same VPs) get errors in the report. Unfortunately, while that was easy when it was 100-1000 rows, we're up to 10,000 rows in some sheets, and about 100 possible codes that could be converted to others. And depending on the sheet they could be in one of two columns. 10,000 * 100 * 2 = 2,000,000. That's the problem. I was sure this was related to the VPN being extremely slow, but the macro took over two hours to run before I aborted it earlier. I saved the file locally and started it again, it's still taking forever. Maybe if they didn't give me a "u" series processor it wouldn't be quite as bad either. I'll see if using an array makes it any faster. After doing the math, I'll also cut the run time in half by making a different version for each sheet depending on what column the code is in.
|
|
#
?
Sep 7, 2017 04:37
|
|
|
22 Eargesplitten posted:We had an old set of activity codes, which weren't supposed to be used on this project. Early on, before we locked it down, some people (including VPs) did it anyway. Now every time I update the project I have to convert those old codes into the new codes, or the people who need to use the report (including some of the same VPs) get errors in the report. Unfortunately, while that was easy when it was 100-1000 rows, we're up to 10,000 rows in some sheets, and about 100 possible codes that could be converted to others. And depending on the sheet they could be in one of two columns. 10,000 * 100 * 2 = 2,000,000. That's the problem. I was sure this was related to the VPN being extremely slow, but the macro took over two hours to run before I aborted it earlier. I saved the file locally and started it again, it's still taking forever. Maybe if they didn't give me a "u" series processor it wouldn't be quite as bad either. You should use a dictionary structure to store the 100 codes that you want to convert. Either use the native "collection" type (in which case you unfortunately have to define a function to check for membership) or add a reference to the Microsoft Scripting Runtime and use the Scripting.Dictionary type (in which case you can use a built in function to check for membership). See https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure You will just have to iterate through the codes that you want to convert once in your macro, to build the collection/dictionary. Then just iterate through the report and check whether the values are contained as keys in the collection/dictionary object. It will be much faster if you then also use an array to access all of the report (it might be better to do it in multiple chunks but you'll probably be able to do it all in one go). If there's no complicated formatting going on, you can then just modify the array as you go and write it back again at the end. Alternatively, since it sounds like you don't have to actually modify too many cells, you could just read from the array but then write back to the cells directly (the write operations will be slower than , but if you don't have to write to a lot of cells it won't be too bad.) (By the way, since it sounds like you're not really using any real excel features until after you process the report with your macro, it would probably be easier to actually do this in another programming language, but I realize that if you don't have experience with that it can be harder to get started that way.) mystes fucked around with this message at 05:37 on Sep 7, 2017 |
|
#
?
Sep 7, 2017 05:33
|
|
|
I'll look into that. As it is I've just got the .Replace functions for each and every code in the macro, so it doesn't have to refer to anything else. Unfortunately my time is very limited, as these are the people that thought it would take an hour to modify the report for a whole new project (even before the project got above 20k total lines, it took 30 minutes or more just to run the macros to update the codes and fill in a specific column that had been using a snail pace array function). I'm supposed to have this updated and sent in by morning (not happening, I'll give them the old one with the updates requested), and 6 more in another week. I think tomorrow will be dedicated to optimization, but I can't start from scratch in another language. I'll refer back to what you said, since it makes sense from what little I know.
|
|
#
?
Sep 7, 2017 06:37
|
|
|
Well, my manager took a look and asked why I didn't just take the sheet with the conversion information, paste the transactions into a new sheet, and do a new column with a VLOOKUP to find the conversions, then copy and paste as values. Sometimes you just need a second pair of eyes to come in and tell you you're way overthinking it.
|
|
#
?
Sep 7, 2017 19:31
|
|
|
Is there a way to make one sheet be a merging of the rows of several other sheets in order by some column (say date)?
|
|
#
?
Sep 8, 2017 01:59
|
|
|
KernelSlanders posted:Is there a way to make one sheet be a merging of the rows of several other sheets in order by some column (say date)? I'd copy all the rows across to the new sheet first, then sort the new sheet by your chosen column. Would that work?
|
|
#
?
Sep 8, 2017 08:13
|
|
|
potatocubed posted:I'd copy all the rows across to the new sheet first, then sort the new sheet by your chosen column. Would that work? No. Then I'd have to re-copy and re-sort every time I added a row to any of the other sheets.
|
|
#
?
Sep 9, 2017 03:26
|
|
|
Do a match for them all on a new sheet and throw a concatenation in perhaps?
|
|
#
?
Sep 9, 2017 08:11
|
|
|
Haven't had my caffeine yet but convert each sheet into a named table and merge them using Power Query into a single table which you 'Load To' a new sheet?
|
|
#
?
Sep 9, 2017 16:23
|
|
|
So I'm trying to replace over a hundred different Excel files, all meant to share the same template, with a single Excel file that enforces that template idea — one sheet for printing and a few others for the data that was spread across the other Excel files. This went well, now I have to deploy it. Our current workflow is (a) navigate to the folder containing the right file, (b) open it and (c) print. I'd like to mimic this flow, but have (b) open the new Excel sheet and refer to the correct data based on the filename. I'm not sure how to do this using Windows shortcuts or trickery in Excel, and compounding the pain, I don't think I can use VBA due to macro trust settings. And of course the new spreadsheet uses a dynamic print area, camera views and dated header/footer information. I want to change the workflow entirely, to (a) open up the new consolidated workbook, (b) select desired data then (c) print, but it would require upfront investment to re-enter all our data and train people on the new system, which we don't have time for right now. I'd like to find something that facilitates a slow/partial rollout by integrating smoothly into the process we already have. Has anyone had to deal with something like this before? Is it possible to do anything to fake the old workflow under these constraints?
|
|
#
?
Sep 23, 2017 04:38
|
|
|
handle posted:So I'm trying to replace over a hundred different Excel files, all meant to share the same template, with a single Excel file that enforces that template idea — one sheet for printing and a few others for the data that was spread across the other Excel files. This went well, now I have to deploy it. I think you're getting into stuff that will be difficult without some sort of programming at some point in the process, but given your constraints, I guess I would probably look into trying to copy all the existing data into one file (as individual sheets if you must, I guess) and provide a way to select them. Honestly, I have to wonder if Excel is really the right tool for the job. Without more information it's hard to give a specific suggestion, though.
|
|
#
?
Sep 23, 2017 13:55
|
|
|
mystes posted:You can get the filename from a formula, but shortcuts probably won't work because the file is still opened from its real location. Also, if you use the filename, it will cause problems if someone then saves the file with a different filename. For the moment, yeah, I'm planning to copy the data we have into the spreadsheet and put a dropdown in a cell on the display page to switch between them. We miiiight be able to use VBA if we can add to the trusted locations, and then I might try passing options from a shortcut in Explorer. There are ways to read the command line I guess, and I hope that would also work if Excel is launched from a shortcut.
|
|
#
?
Sep 24, 2017 01:37
|
|
|
I'm trying to summarize some data from related Excel workbooks and I think the only way this is going to work is VBA, but I'm wondering if someone smarter than me may have a better solution (I'm not opposed to using VBA but I'm not well-versed enough in it to actually write something to do what I need). I work in a call center, and we have monthly Quality spreadsheets with data that is pulled by our QA agents. Each agent on the phone gets their own spreadsheet each month. These are stored in a consistent folder/filename structure on sharepoint, so building filename strings shouldn't be too difficult given a list of people. Here's what I'm trying to do: Each workbook has (among others, but these are the 2 that are important for now) tabs called 'Info' and 'Analyst Summary' - the former is where the QA agent inputs some metadata (their name, name of the person being reviewed, and importantly the # of calls being reviewed). The latter gives a summary, split by call, of the scores they received in their evaluation. What I am hoping to do: each month, I'd like to rip out the information from this 'Analyst Summary' tab for each person and consolidate it into one sheet for ease of review. Here's what the 'Analyst Summary' tab currently looks like:  The number of calls evaluated is determined by a value in the 'Info' sheet, as each person is different (newer folks get more calls evaluated during their training/first few months). I know I can do all this by using =INDIRECT and building strings to put in a formula to VLOOKUP all this poo poo, but because of the varying number of calls I still have to go and open each workbook to figure out how many formulas I need for each person, which at that point I may as well just open up the loving things and manually copy & paste the rows I need and call it a day. Is there a better solution here or do I need to go learn some basic VBA to accomplish this? Thanks in advance for any tips!
|
|
#
?
Oct 2, 2017 17:53
|
|
|
If you know the maximum number of calls you will ever need, you could add something like IF(a2>total_calls,your formula,"") to your formulas. (Where total_calls is a reference to the number of calls for that person). And then just make sure your summary template has enough rows to accommodate everyone.
|
|
#
?
Oct 2, 2017 20:41
|
|
|
Can you give an example of what you want your output to be?
|
|
#
?
Oct 2, 2017 21:08
|
|
|
docbeard posted:If you know the maximum number of calls you will ever need, you could add something like IF(a2>total_calls,your formula,"") to your formulas. (Where total_calls is a reference to the number of calls for that person). And then just make sure your summary template has enough rows to accommodate everyone. I had an epiphany that I can just pull the maximum number of calls for everyone, and just apply a filter at the top to remove blanks. This should work in theory, though I am having a hell of a time getting the INDIRECT function to actually work properly I've built up a filepath/sheet name string and, when used directly in a VLOOKUP like so: =VLOOKUP(1,'http://employees/sites/Department/SubDepartment/QA1/LastName, FirstName/Year/[YearMonth LastName, FirstName.xlsm]Analyst Summary'!$A$2:$I$12,2,0) This works just fine. However, if I try to use INDIRECT and instead build the filepath out of its parts, like so: =VLOOKUP(1,INDIRECT("'"&Names!$C$1&Names!$A5&"/"&Names!$C$2&"/["&"17"&"09"&" "&Names!$A5&".xlsm]"&Names!$F$1&"'!"&Names!$F$2,FALSE),2,0) I get a #REF error. I know I've built the string correctly, as I can put just whats contained within the INDIRECT into a separate cell, copy it and paste value, and paste that directly into the VLOOKUP and it's perfect. I'm not sure where else I could be going wrong! e: apparently using an external reference with INDIRECT() requires the external workbook to be open, so, gently caress. balls. fuckballs. kumba fucked around with this message at 22:01 on Oct 3, 2017 |
|
#
?
Oct 3, 2017 18:22
|
|
|
Is there a way to add a little button on a page that will hide all rows that contain no data? Better yet, the cells are linked to another tab and would like the rows hidden when the original cells are empty.
|
|
#
?
Oct 26, 2017 05:42
|
|
|
Would a filter not work?
|
|
#
?
Oct 26, 2017 09:24
|
|
|
Fingerless Gloves posted:Would a filter not work? Ehhh, not exactly. It's hard to describe so I'll figure something out.
|
|
#
?
Oct 31, 2017 05:11
|
|
|
I have a ss of about 3500 rows that includes a xx/xx/xxxx date. I'm trying to sort the ss by just xx/xx. I tried to format that row using date and only show xx/xx, but when I sort it still includes the year. Is there a way to mass delete the year off all rows? I don't need the info (and never will).
|
|
#
?
Oct 31, 2017 16:31
|
|
|
Alfalfa posted:I have a ss of about 3500 rows that includes a xx/xx/xxxx date. I'm trying to sort the ss by just xx/xx. I tried to format that row using date and only show xx/xx, but when I sort it still includes the year. Is there a way to mass delete the year off all rows? I don't need the info (and never will). I don't know of a way to do it just by that, but if say your list of dates are in A1, you could put =WEEKNUM(A1,1) in column B and =WEEKDAY(A1,1) in column C then just sort by those two columns instead
|
|
#
?
Oct 31, 2017 16:43
|
|
|
kumba posted:I don't know of a way to do it just by that, but if say your list of dates are in A1, you could put =WEEKNUM(A1,1) in column B and =WEEKDAY(A1,1) in column C then just sort by those two columns instead Thanks. This might work but I'm using =MONTH(A2) as the month is the most important part of what I need.
|
|
#
?
Oct 31, 2017 16:48
|
|
|
What about something like this?
|
|
#
?
Oct 31, 2017 17:53
|
|
|
In cases like that where you're fighting with the formatting code I usually just export to CSV and regex to clean it up. I think you'd need to find (assuming POSIX and CSV): code:code:code:
|
|
#
?
Nov 1, 2017 22:13
|
|
|
Richard Noggin posted:What about something like this? I’ll give this a try this morning appreciate it. Scaramouche posted:In cases like that where you're fighting with the formatting code I usually just export to CSV and regex to clean it up. Once I decipher this into simpleton English I’ll also give this a shot lol. Thanks everyone for the help. As the =MONTH came back with all sorts of weird issues when trying to reformat it back into month/day with year being gone.
|
|
#
?
Nov 3, 2017 14:11
|
|
|
RegEx is like Find + Replace on steroids. You can match various patterns, replace them with static strings, or replace them with parts of other text. It is hella powerful and cool but also dangerous if you search for the wrong thing. I'll not learn you the whole thing but I'll explain what I said more. First you need a regex capable text editor; I use Textpad but stuff like Ultapad or Notepad++ have it as well. The first two things I said to search for was (the slash is the date separator and not a regex thing): (0-9+)/(0-9+)/ In regex talk that means any number from 0-9, and the plus sign means match multiple numbers. So 1111 would be found, 11a11 would only find the first 11, and zyxqb would not be found at all. The brackets around the number mean I want to "remember" what was found so I can work with it later. They're assigned in order. The third thing was this: (?:0-9+) The ?: means match on this, but don't "remember" it, because this is the year and we're discarding it anyway. That's the "find" part, now onto the replace: \1,\2 So the things "remembered" are automatically assigned a number so you can reference them later. \1 is the Month and \2 is the Day. So theoretically (I'm doing this from memory) the result from this set of data: code:code:So the dangers here are obvious, though the way I've structured it (looking for the slash, including the commas at the end of the column in the original example) mitigate that somewhat. But if you included another column full of dates in the file, this would find/modify those ones as well. Generally if I'm not doing multi-line stuff I usually only copy out the sorted relevant column and paste it back into the file I'm done. There's all kinds of cool syntax to find things in regex like A-Za-z (all upper and lower case letters) \s (space) \t (tab, useful for TSV) \n (newline, dangerous if line-ending religion isn't known) and many more.
|
|
#
?
Nov 3, 2017 18:11
|
|
|
Why does Excel not support regexes in find/replace even though word does?
|
|
#
?
Nov 3, 2017 18:59
|
|
|
mystes posted:Why does Excel not support regexes in find/replace even though word does? I haven't used actual Excel in years (luckily VBA doesn't enter my world very often). But LibreOffice Calc supports it! https://help.libreoffice.org/Common/List_of_Regular_Expressions
|
|
#
?
Nov 15, 2017 19:39
|
|
|
Yeah I know but I'm stuck with Excel at work.
|
|
#
?
Nov 15, 2017 20:39
|
|
|
I want to make a stacked bar chart with ~100 cateogories. It's for an illustration about insect diversity if anyone cares. I want to make the chart, then export to a vector format. I can make the bar chart easily in Excel, but it automatically assigns colors to each field, and makes the ones on the left darker, and the ones on the right lighter. I just want contrasting colors, preferably non-garish, of equal lightness, randomly assigned. This is the fade I'm talking about.  I could do it in Google docs, but I can't export to a vector format from there. Also garish.  Do I have to go to R to make this stupid simple chart?
|
|
#
?
Nov 19, 2017 16:16
|
|
|
My first question would be, does a chart with so many categories actually deliver any useful information? Wouldn't it be better to show only the largest categories, collect the smaller ones as an "other" (or perhaps in multiple super-categories if it makes sense) and then do several "zoomed" charts showing the fine details? Barring that, I'm sure the answer is to use VBA to generate and assign new colors to the chart categories.
|
|
#
?
Nov 19, 2017 16:50
|
|
|
Yeah the many categories are kind of the point. It will be divided into chunks. Ugh, rather R than VBS, but thanks.
|
|
#
?
Nov 19, 2017 16:56
|
|
|
I'm stumped coming up with an Excel solution for this problem. Say I have a table of employee names, boss names, and the amount of money each boss gave to an employee.  What I'd like to end up with is a summary of some sort detailing who gave to each employee. The amount given doesn't need to be in the summary. The summary doesn't have to be pretty, just such that you can tell at a glance who donated to a given employee. So, something like: code:
|
|
#
?
Dec 1, 2017 07:50
|
|
|
I have a final round of interviews next week with a 30 minute assignment / tech interview using Microsoft Excel. They will give me a large data set and I will have to use Excel to find patterns in it and manipulate the data using Excel / Pivot Tables to identify patterns in the data. I've used Excel and Pivot Tables in the past but is there anything I should keep in mind when faced with a task such as this?
|
|
#
?
Dec 1, 2017 09:04
|
|
|
Hughmoris posted:
Quick and dirty, set up an if statement table next to it. Maybe something like =If(cell>0,$1:$1,"") Then you can concatenate this together to make a single column list.
|
|
#
?
Dec 1, 2017 09:18
|
|
|
Busy Bee posted:I have a final round of interviews next week with a 30 minute assignment / tech interview using Microsoft Excel. They will give me a large data set and I will have to use Excel to find patterns in it and manipulate the data using Excel / Pivot Tables to identify patterns in the data. I've used Excel and Pivot Tables in the past but is there anything I should keep in mind when faced with a task such as this? It depends on if you've already demonstrated your excel abilities I think. If you haven't then you should throw in a few examples of things that can seem impressive like conditional formatting and named ranges. Really it sounds more like a test of your data reading. Maybe throw in some trimming or write correlation =! Causation type of poo poo. Best of luck with the interview, fingers crossed for you!
|
|
#
?
Dec 1, 2017 09:27
|
|
|
Fingerless Gloves posted:It depends on if you've already demonstrated your excel abilities I think. If you haven't then you should throw in a few examples of things that can seem impressive like conditional formatting and named ranges. Really it sounds more like a test of your data reading. Well this is for an analyst position so nothing too complicated I'm assuming. I haven't had a chance to demonstrate my excel abilities to them but I have a fairly good understanding from my previous role. However, conditional formatting, named ranges, trimming etc are things I'm not aware of so I will definitely look into that. Thank you!
|
|
#
?
Dec 1, 2017 09:48
|
|
|
Oh cool, I can actually help there then. You want to be able to always summarise data away from the raw data itself. Make a summary table. Best way, pivot the data after processing and have a whole list of sliders. If you haven't, learn how to make a combination chart. Bar charts with a line chart on a second axis always makes people cream. Also never make a static table. Alway make it so you can add or remove data to change the data. Make a big deal of that, say your report is fully dynamic or modular or whatever buzzword you can think of. If you can prove how efficient you are by building one report they'll love it. I'm gonna check over my works now to see what more I can glean from it and give as more shittily written advice.
|
|
#
?
Dec 1, 2017 10:20
|
|
|
|
| # ? May 12, 2024 22:38 |
|
|
Fingerless Gloves posted:Oh cool, I can actually help there then. That's great advice, thank you. What do you mean by never making a static table? So lets say they give me an Excel sheet with multiple columns and rows - the columns could be name, price, purchase ID etc. The way I would go about it is to create a table from that where I can play around with the column header. Super basic understanding of Excel on my end. Then I could insert pivot tables and mess around with the data from there and compare the various variables. Does this mean it's still a static table?
|
|
#
?
Dec 1, 2017 11:25
|
|