|
Old James posted:Try Qlikview (they have a free version which you can download from their site). This seems pretty awesome, thanks! Running some tests with it now, but from the looks of it this might be the answer.
|
# ? Mar 19, 2014 04:12 |
|
|
# ? May 13, 2024 06:29 |
|
Is it generally more efficient to use a Pivot table or to replicate the table using formulas like countifs, sumifs, index/match?
|
# ? Mar 19, 2014 04:35 |
|
Ragingsheep posted:Is it generally more efficient to use a Pivot table or to replicate the table using formulas like countifs, sumifs, index/match? Depends on how much it is, how the data is formatted and what/how you want the endresult to look like. Pivot tables are super fast and easy to use, but in my experience aren't always flexible enough to allow me the set up I want. (Main problem being % on totals and comparisons between columns/rows) Whenever that happens I tend to use the pivot table to get all the unique values in the list and then use the formulas to create my own table.
|
# ? Mar 19, 2014 10:55 |
|
This seems trivially easy, but can someone tell me the code for "for each workbook in this folder, pull out the value from A1?" (I'll be doing more with it, but my brain just seizes at the workbook level apparently).
|
# ? Mar 19, 2014 13:08 |
|
Turkeybone posted:This seems trivially easy, but can someone tell me the code for "for each workbook in this folder, pull out the value from A1?" (I'll be doing more with it, but my brain just seizes at the workbook level apparently). Modified from http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba Haven't tested the changes, but it should work. code:
|
# ? Mar 19, 2014 14:58 |
|
Dir accepts a pattern too, so you can do the following instead (Dir will then list only Excel files, so you don't have to check inside the loop):code:
code:
|
# ? Mar 20, 2014 00:29 |
|
I have a VBA script to go through a list of ages in various formats and convert them to raw numbers, and it works properly except in the case where the text is "10 and up".code:
code:
|
# ? Mar 22, 2014 06:43 |
|
Try using '10 in the cell containing the actual value? ' causes Excel to treat cell content as literal instead of acting as a date.
|
# ? Mar 22, 2014 12:45 |
|
icantfindaname posted:I have a VBA script to go through a list of ages in various formats and convert them to raw numbers, and it works properly except in the case where the text is "10 and up". Have you tried using Cells(i,2).value2 = CInt(VBA.Strings.Left(str1, pos1 - 1))
|
# ? Mar 22, 2014 16:24 |
|
icantfindaname posted:This part is what's causing the problem Excel is not "converting", it is displaying in the wrong number format (dates are really just numbers displayed in a fancy way, starting at zero being the beginning of 1900). Most likely the cell used to be formatted as a date, or perhaps it has dates somewhere around it and Excel has decided to infer the number format (e.g. if you put a bunch of dates in a column, it will automatically assume the next row down is also a date). You can just change the formatting of the cell. You probably don't want to put the single quote, because it will then treat all your numbers as strings (i.e. can't do arithmetic, ordering is wrong, etc).
|
# ? Mar 22, 2014 17:23 |
|
Alright I fixed it by using Cells.Value = Val(VBA.Strings.Left(...)) Is there any reason to use CInt over Val or vice versa? Also, how would I search through a particular row to find a string, and then return the column of the first cell containing that string?
|
# ? Mar 22, 2014 20:36 |
|
icantfindaname posted:Alright I fixed it by using Cells.Value = Val(VBA.Strings.Left(...)) CInt and Val both return numbers, but CInt only natural ones. Doesn't matter, the effect is that you tell Excel that these things are numbers, not strings that it's up to Excels wisdom to figure out how to parse and present. Strings.Left() returns a string and not a raw number. Also if you use cells.value2 = , instead of value = , your numeric value will be preserved even if Excel decides to present it as a date. For finding a value learn xlFind : http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.15).aspx nice tut here : http://www.vbforums.com/showthread.php?634644-Excel-Find-Method-in-Excel-VBA-(Any-version-of-Excel) sofokles fucked around with this message at 18:36 on Mar 23, 2014 |
# ? Mar 23, 2014 18:29 |
|
ShimaTetsuo posted:Dir accepts a pattern too, so you can do the following instead (Dir will then list only Excel files, so you don't have to check inside the loop): So I went about this a slightly different way, but this might answer my follow-up question (and final big step in some report automation I've been working on). Basically I now have a folder of reports to send to sales reps and cc to their managers.. I now basically just need to attach the appropriately named reports to said emails. The filenames have their unique ID numbers, so could I simply do something like: For each file in folder if file.name like "12345" (their ID number) then attachments.add file.path Obvi that's some rough pseudocode, and I *think* outmail.attachments takes a path. If there is a set order that files are in a folder (alphabetical), then I could just do it 1 to 1 without an if, right? It's only ~30 files, so it's not really a huge issue searching it out I don't think. I'd appreciate any criticism to this logic, thanks. Edit: I used instr(filename, idnumber) to check and I got it to work exactly how I wanted, so yay! Thanks for letting me talk it out to you, thread. Turkeybone fucked around with this message at 18:48 on Mar 25, 2014 |
# ? Mar 25, 2014 18:02 |
|
I have a workbook with data in columns A, B, and C. Column B contains a series of dates. There are a few thousand rows containing approximately 10 or so unique dates. Example data: code:
So I'd end up with several workbooks called: 13-Jun-14 22-May-14 2-Jul-14 24-Apr-14 And so on... I have dug around online and this seemed to be the closest thing but it seems to do way too much and when it tries to create the filename based on the date, the slashes in the date format cause errors. Can anyone point me to a solution?
|
# ? Mar 27, 2014 18:00 |
|
me your dad posted:I have a workbook with data in columns A, B, and C. Column B contains a series of dates. Edit : Oh, you want the entire row, had to change code then OOOps aand you want workbooks not sheet. well well. nevermind then. too late. Edit : Nah, it's never too late. Couple of simple loops does it You prolly want to set screenupdating to false code:
sofokles fucked around with this message at 00:30 on Mar 28, 2014 |
# ? Mar 27, 2014 23:10 |
|
me your dad posted:Stuff Haven't tested this, but try... code:
|
# ? Mar 27, 2014 23:35 |
|
Cool, thanks y'all. I'm at home now but I'll give those a shot in the morning. I'll let you know how it works out.
|
# ? Mar 27, 2014 23:37 |
|
I'm linking some Excel data and putting it into a Word document (just as a regular old table). But for some reason, the linked data keeps getting bolded in Word. There's a space before each number in each cell, and that space seems to be causing this unwanted bolding. If I replace that space with a break space, it seems to fix it. But I don't get why this is happening?
melon cat fucked around with this message at 01:18 on Mar 28, 2014 |
# ? Mar 28, 2014 01:10 |
|
I've got what im sure is an easy problem i can't sort out by myself. I have a spreadsheet where i'd like a script to run down part of a coloumn and increment each cell it turn by one untill another cell on the same row becomes 3 code:
|
# ? Mar 31, 2014 22:20 |
|
Loving Africa Chaps posted:I've got what im sure is an easy problem i can't sort out by myself. The main issue is that your Do loop doesn't increment i at all. The code sets i = ActiveCell.Offset(0, 10).Select, then i doesn't ever change while you're in the loop. Try setting i to the value in the target cell somewhere inside the Do loop. I doubt you'll be able to make the method with ActiveCell.Offset(0, 10).Select work. Try something like i = cl.Offset(0, 10).Value
|
# ? Mar 31, 2014 22:34 |
|
esquilax posted:The main issue is that your Do loop doesn't increment i at all. yeah that was stupid of me. code:
|
# ? Mar 31, 2014 22:50 |
|
I want to automate a simple process in Excel. I have a spreadsheet with about 3000 names. First then last name. All in one column. To clarify, what I have is about 3000 names formatted like this:code:
code:
Any fast way to do this, or will I have to go through the gruelling, manual task of copy and pasting the the last name into the next column over? melon cat fucked around with this message at 18:46 on Apr 1, 2014 |
# ? Apr 1, 2014 18:33 |
|
melon cat posted:I want to automate a simple process in Excel. I have a spreadsheet with about 3000 names. First then last name. All in one column. data tab, text to columns
|
# ? Apr 1, 2014 18:44 |
|
Thanks a lot for telling me about this.
|
# ? Apr 1, 2014 21:05 |
|
Holy poo poo. I have been using mid/find/right/left/len/etc for that for over a year.
|
# ? Apr 2, 2014 03:40 |
|
Follow-up question on this. This works beautifully for First, Last name scenarios. But if I have a cell that has Firstname, Middle Initial, Lastname this task isn't as streamlined. Example: Here's what I'm given: code:
code:
code:
|
# ? Apr 2, 2014 17:14 |
|
melon cat posted:Follow-up question on this. This works beautifully for First, Last name scenarios. But if I have a cell that has Firstname, Middle Initial, Lastname this task isn't as streamlined. Example: code:
code:
|
# ? Apr 2, 2014 17:52 |
|
FAN OF NICKELBACK posted:Holy poo poo. I have been using mid/find/right/left/len/etc for that for over a year. Me too, I know nothing.
|
# ? Apr 3, 2014 14:27 |
|
Once you do text to columns, it becomes the default for future pastes, which can be a nuisance. You turn it off by doing text to columns, delimited, and then deselecting all delimiters. Or that's how I do it, anyway. I had to do a lot of that this week, since I've been doing reports that required me to paste UNIX df output into Excel.
|
# ? Apr 3, 2014 22:25 |
|
I know that the proper answer is probably along the lines of "use a database" but any tips on optimizing a sumifs formula on 600,000+ rows of data?
|
# ? Apr 7, 2014 04:02 |
|
Ragingsheep posted:I know that the proper answer is probably along the lines of "use a database" but any tips on optimizing a sumifs formula on 600,000+ rows of data? Probably best thing to do would be to add a column concatenating multiple Cells into one. Then use your sumif() on that single column as your criteria.
|
# ? Apr 7, 2014 14:20 |
|
Here is my situation: Employees receive annual evaluations. They can receive evaluations of "awesome," "decent," or "lovely." I would like to generate a table of rows for all possible combinations of evaluations over, say, a four-year period. Something like: code:
|
# ? Apr 7, 2014 16:32 |
|
You want the solver tool for that one. Haven't used it in like 6 years though so I forgot how to use it, but it'll definitely solve(er) your problem!
Veskit fucked around with this message at 17:37 on Apr 7, 2014 |
# ? Apr 7, 2014 16:40 |
|
I couldn't figure out how to make the Solver tool generate rows, however it will help me solve the problem that results from generating the rows, so thanks for pointing it out! I did find a pretty simple VBA solution to my original problem: code:
Follow-up question: if someone else has more or fewer evaluation results (just Awesome/lovely, or Awesome/Decent/lovely/Really lovely) and wants to do it across more or fewer years, are they going to have to futz around with the code themselves, or is there a way to pop up a dialogue box that asks "How many different evaluation results?" and "How many years?" and the user can just put the two numbers in and get what they want?
|
# ? Apr 7, 2014 19:18 |
|
Ronald McReagan posted:I couldn't figure out how to make the Solver tool generate rows, however it will help me solve the problem that results from generating the rows, so thanks for pointing it out! You can do that by using a mathematical algorithm instead of a coding one. For example, you have n+1 number of grades (A,D,S) and m number of years. code:
esquilax fucked around with this message at 20:32 on Apr 7, 2014 |
# ? Apr 7, 2014 20:28 |
|
Brilliant, thanks y'all! edit: might the 3 in Mod(3^j) as well be n+1 ? khazar sansculotte fucked around with this message at 22:50 on Apr 7, 2014 |
# ? Apr 7, 2014 22:47 |
|
Oops, yep.
|
# ? Apr 7, 2014 22:52 |
|
Hi there, wondering if anyone could help. I have a dashboard setup which I have been trying to set up a macro for. The information is laid out over many worksheets in an Excel Workbook. Ideally I'd like to be able to attach a macro to a button that when run will unhide all the hidden sheets, provide a drop down menu list to select from, go to the selected sheet and then hide all non selected sheets again without the screen updating. Is this actually possible? The concept sounds simple but while I can implement the switching between sheets macro, it doesn't work when the other sheets are hidden.. Any help would be much appreciated!
|
# ? Apr 8, 2014 13:52 |
|
This should be easy, but I can only find solutions if I want to define the criteria manually which I can't. Say I have a large data set with two columns. Column A has some number and column B has an ID. How can I sum all values in column A that match ID n? The only thing I found through googling involved manually defining criteria. E.G. code:
E: Okay, this seems to (sort of) work based on the recommended pivot tables. Although in one of the first test data sets I tried it in there was a number that didn't work out to what it was supposed to. I checked it several times (it was a small test dataset) and it definitely didn't add up for some (sum hah) reason. In all the datasets I tested afterwards it worked fine though. Tots fucked around with this message at 06:20 on Apr 13, 2014 |
# ? Apr 13, 2014 05:17 |
|
|
# ? May 13, 2024 06:29 |
|
Yes, pivot tables will do what you want. If it didn't come out correctly on one example, it may have to do with some bad labeling on your part, like different spellings (or extra spaces) for the same logical category in column A, or confusion between numbers being recognized as actual numbers or as strings. In your example, do you expect the sum corresponding to A=2 to be 6, or 11? Depending on how your source data is formatted and how you import it into Excel, "02" may automatically be parsed to the number 2, and you'll get 11. Otherwise you may get 6 for 2, and 5 for "02" separately.
|
# ? Apr 13, 2014 16:03 |