|
I have this CSV bcp file with dates in it. Fully formatted dates like "Jan 11 2012 8:15:00:000AM". Excel thinks they're text and only wants to sort them alphabetically. I have already tried using the extended sort feature that lets me pick "Jan Feb Mar" instead of "Normal", but this does nothing. Any ideas?
|
#
?
Dec 3, 2012 17:45
|
|
|
|
| # ? May 24, 2024 01:23 |
|
|
baquerd posted:I have this CSV bcp file with dates in it. Fully formatted dates like "Jan 11 2012 8:15:00:000AM". Excel thinks they're text and only wants to sort them alphabetically. I have already tried using the extended sort feature that lets me pick "Jan Feb Mar" instead of "Normal", but this does nothing. Any ideas? Add a column with the formula "=datevalue(A1)" and use that column to sort.
|
|
#
?
Dec 3, 2012 17:55
|
|
|
Old James posted:Add a column with the formula "=datevalue(A1)" and use that column to sort. That chokes on the formatted text I have above, and it also choked on "Apr 12 2012". Figured out it wants a comma after the day in order to parse the date, but I can't figure out how to get it to deal with any time text that's added on to it. I'll just run it through a converter, split into two columns, and date format one and time format the other...
|
|
#
?
Dec 3, 2012 18:27
|
|
|
baquerd posted:That chokes on the formatted text I have above, and it also choked on "Apr 12 2012". Figured out it wants a comma after the day in order to parse the date, but I can't figure out how to get it to deal with any time text that's added on to it. I'll just run it through a converter, split into two columns, and date format one and time format the other... Since the format you have is fixed width, you could use a combination of LEFT(str, num_chars) and RIGHT(str, num_chars) to break the date and times apart. From there, you would need DATEVALUE() and TIMEVALUE(), as well as adding that comma that you noted needs to be in the date. If you can get away with a one-time reformat, that's probably faster though.
|
|
#
?
Dec 3, 2012 19:32
|
|
|
So I have a largely academic question. I just took a final in my VBA class (heh) and the coding question was basically, "Given x words to search for, y sheets in the workbook, and z cells on each sheet, count the occurrences of each search word in the workbook." Now, the brute-force, good-enough-for-the-class solution is just three nested loops: 'for each word in the range of "search word" cells 'for each sheet not named "Count" in the workbook 'for each cell in the continuous range (starting at A1) in the sheet 'if search word = the cell, increment the counter ' output each count So that has complexity of like n^3 and runs really slowly and poorly. So I was wondering how it would be possible to cut down the complexity. Maybe creating some sort of dictionary and like, well if the word isn't in the dictionary just error handle and move on? At they very least something like that would cut out the "for each word in the range of search words" loop. Thoughts? I'm still a novice in programming in general, so I get the ideas but not the exact implementations yet.
|
|
#
?
Dec 10, 2012 21:57
|
|
|
The cells.find method should be able to search an entire workbook at once. So you would only need one For loop going through workbooks and then if the .find has at least 1 match, a Do While loop using the cells.findnext and a counter.
Old James fucked around with this message at 23:56 on Dec 10, 2012 |
|
#
?
Dec 10, 2012 23:50
|
|
|
If I'm remembering correctly Microsoft's actual solution to the count of <word> in <range> problem is to use an array formula and substitute each occurrence of <word> with an empty string, so the count can be determined by sum(original range length - new range length) / length of <word>. I have absolutely no idea on the actual complexity of it, but in cases where your bottleneck is CPU and you have enough available memory it seems like it'd be more efficient than looping over each cell. I also don't actually know how array formulas transfer into VBA, so that solution might not be at all practical in your situation.
|
|
#
?
Dec 11, 2012 06:27
|
|
|
I have an excel document that looks something like this:code:What I need to do is to take each of the 40 sets and put them each on a slide in powerpoint. So I'll have one powerpoint document labeled case 524, with 40 slides in it, each slide having the same 20 rows but with their corresponding unique observations. Is there some way I can do this automatically without just selecting the right ranges and copy/pasting 40 times (and then 40 more times for every case?) All the ways I have found so far for exporting from excel to powerpoint only involve putting info on one slide. I can't figure out if it's possible to spread the data correctly over 40 slides.
|
|
#
?
Dec 12, 2012 17:39
|
|
|
Elysium posted:stuff. It's definitely possible, but the time to code and test such a macro might take longer than the time you would spend copy/pasting the rows. So unless you have to repeat this task fairly frequently then I would suggest blasting some music, shutting off your brain, and start ctrl+c and ctrl+v'ing.
|
|
#
?
Dec 12, 2012 18:23
|
|
|
I have a program I wrote (that works with numbers) that I want to be able to generate a text file that can then be imported into common spreadsheet programs. I know about delimited text files, but the thing is I want to be able to specify separate sheets. Like, have one table on sheet 1, the next on sheet 2 and so on. I thought there was a fairly-simple way to do this with xml but if so, I can't figure it out. I have found a few XML exporters, but the same kind of file (I wrote a small test file, copying from the image) doesn't work for importing--at least with Libreoffice... ? One exporter- http://digitalimprint.com/misc/oooexport/ Is there a way to specify multiple sheets in a imported text file?
|
|
#
?
Dec 13, 2012 00:36
|
|
|
Turkeybone posted:So I have a largely academic question. I just took a final in my VBA class (heh) and the coding question was basically, "Given x words to search for, y sheets in the workbook, and z cells on each sheet, count the occurrences of each search word in the workbook." Now, the brute-force, good-enough-for-the-class solution is just three nested loops: So actually there is a specific boolean in dictionaries called .exists, which checks if something is a key in a dictionary. So what I did was create a dictionary with my search words as keys, and the values as 0. So for each word in a cell, if it exists in the dictionary then I increment the counter. It runs MUCH faster. The two lines about "R1" are just to show someone else how fast it runs. code:
|
|
#
?
Dec 14, 2012 00:47
|
|
|
Turkeybone posted:So actually there is a specific boolean in dictionaries called .exists, which checks if something is a key in a dictionary. So what I did was create a dictionary with my search words as keys, and the values as 0. So for each word in a cell, if it exists in the dictionary then I increment the counter. It runs MUCH faster. The two lines about "R1" are just to show someone else how fast it runs. In terms of algorithm that seems much better. However, you also have a VBA specific issue: don't use the spreadsheet as if it was actually part of your workspace. It is NOT just a big array that's always there: reading and writing to the sheet is very very slow. Much slower than accessing variables. So the loop on the cells involves reading each cell from the workbook all the way to VBA, one at a time. Unless you have too much data (not sure what the limit is), you can copy the entire ws.UsedRange.Cells range into an array in one go, then loop over that instead. It'll depend on how much data you have but I made a small (3000 cells) example and it was 100 times faster.
|
|
#
?
Dec 14, 2012 13:44
|
|
|
Ahhh, interesting, I will check that out too! This is already way beyond even the knowledge of the instructor, which I am totally okay with 
|
|
#
?
Dec 14, 2012 15:58
|
|
|
I've been tasked by my office to build a spreadsheet tracking various systems and their statuses. I'm looking for a way to be able to count multiple status codes, but my formula knowledge extends to simple counting and not multiple variables. Basically, I need to be able to enter foo and bar and fizz into any one of about 20 columns and have them calculated elsewhere. If this is a "Just take an Excel course" question, then I'm sorry, but this is daunting and has a deadline of Thursday.
|
|
#
?
Dec 17, 2012 02:50
|
|
|
The Betrayer posted:I've been tasked by my office to build a spreadsheet tracking various systems and their statuses. I'm not sure what you're asking honestly, but i think your solution is pivot table. Pivot tables usually solve everything.
|
|
#
?
Dec 17, 2012 03:13
|
|
|
The Betrayer posted:I've been tasked by my office to build a spreadsheet tracking various systems and their statuses. =countif($A$1:$R$1000,"foo") =countif($A$1:$R$1000,"bar") =countif($A$1:$R$1000,"fizz")
|
|
#
?
Dec 17, 2012 05:29
|
|
|
I'm building a spreadsheet to track my investments, and I have a question. Say I have a large table like the excerpt shown belowcode:I know I could make a ton of dummy columns with stuff like =if(B1="Equity",A1,0), and then sum those, but was wondering if there is a better way.
|
|
#
?
Dec 17, 2012 16:29
|
|
|
Xenoborg posted:I'm building a spreadsheet to track my investments, and I have a question. Say I have a large table like the excerpt shown below =sumif($B$1:$B$6,"Equity",$A$1:$A$6) -or alternately- =sumif($B$1:$B$6,$B$2,$A$1:$A$6)
|
|
#
?
Dec 17, 2012 16:37
|
|
|
Old James posted:=sumif($B$1:$B$6,"Equity",$A$1:$A$6) Ah great thanks, I had a feeling there was a function for it I just couldn't remember.
|
|
#
?
Dec 17, 2012 16:46
|
|
|
I know how to do it with VBA however, have they added a function to give you the last date/time that the workbook was saved? I don't like having to enable macros every time I open this workbook, just to have a nice-looking cover/documentation sheet.
|
|
#
?
Jan 17, 2013 19:04
|
|
|
coyo7e posted:I know how to do it with VBA however, have they added a function to give you the last date/time that the workbook was saved? I don't like having to enable macros every time I open this workbook, just to have a nice-looking cover/documentation sheet. Not sure on the date thing, but if you save macros in your Personal.xlsb, you don't have to muck around with individual macro-enabled workbooks. Huge time saver for quick one-offs like these.
|
|
#
?
Jan 17, 2013 20:40
|
|
|
I am trying to make a little record keeping spreadsheet that compiles flight and duty times for the previous 12 months. I would like it to only display the last 12 months to avoid clutter, but it needs to track 24 months for accurate previous 3, 6, and 12 month consecutive totals. I would like to be able to add a new line for the new month (A24) and have it automatically "hide" the 1st month (A12) and continue the formulas used in columns D,E and F. Is this possible in Excel or am I going to have to find someone to write some kind of little program to do it? Here is a screenshot of what I have so far- in the formula view you can see how the formulas are set up (E18 is a 6 month cell, I'm sure you can extrapolate how I did the rest.) 
|
|
#
?
Jan 18, 2013 01:45
|
|
|
greasyhands posted:I am trying to make a little record keeping spreadsheet that compiles flight and duty times for the previous 12 months. I would like it to only display the last 12 months to avoid clutter, but it needs to track 24 months for accurate previous 3, 6, and 12 month consecutive totals. I would like to be able to add a new line for the new month (A24) and have it automatically "hide" the 1st month (A12) and continue the formulas used in columns D,E and F. Is this possible in Excel or am I going to have to find someone to write some kind of little program to do it? You could turn the range into a Table object (click the Insert Tab, first icon on the left) which creates named ranges out of each column, when you add a new line at the bottom it will resize the named ranges to include the new row, and if every cell in a column uses the same formula it will copy that down. As far as hiding rows, that will have to be done with VBA. EDIT: Table objects are great, I recommend using them as often as possible. EDIT2: VVVV That's been inconsistent for me which is why I use tables, but yeah that can work. Old James fucked around with this message at 05:46 on Jan 19, 2013 |
|
#
?
Jan 19, 2013 03:17
|
|
|
Do you absolutely need to HIDE those rows, to "reduce clutter"? Why don't you just use freeze panes and scroll, like, once a year? Excel will usually copy down formulas when you add new rows too, you don't really need to use a table I don't think.
|
|
#
?
Jan 19, 2013 03:56
|
|
|
ShimaTetsuo posted:Do you absolutely need to HIDE those rows, to "reduce clutter"? Why don't you just use freeze panes and scroll, like, once a year? The idea is to be able to distribute this spreadsheet to a dozen or so people and have them be able to print it off with only 12 rows and them not have to actually know what they are doing other than entering the data. Excel does copy down the formula, but it doesnt work because it needs to only include the current row and the previous 2 (for the 3 months column, anyways) but what it does is add in the new row without removing the first so you end up with 4 months for the first added row, 5 months for the next added row, etc. Thanks for the suggestion Old James, I'm new to Excel so I barely know what you're saying but at least I have a starting point for learning this. greasyhands fucked around with this message at 07:44 on Jan 19, 2013 |
|
#
?
Jan 19, 2013 07:32
|
|
|
You're right that they don't always copy down if you don't use a table (it's something to do with having all columns contain data, and if you dragged down the formulas...not completely clear). I do have a solution for your other problem however: if you just want to print the last 12 months, use dynamic named ranges. Go to the formulas tab, then Name Manager, then "New...". Create a new named range called "Print_Area", scoped to the sheet your data is on. In the refers to box, type in a formula that returns the range of your data depending on the month. I don't know exactly how your spreadsheet will be set up, but something like: =INDIRECT("A"&COUNT($A:$A)&":F"&COUNT($A:$A)+11) You will have to adjust depending on if anything is sitting above row 8 (ideally you wouldn't put anything there and you would start the top of the page). After that's done, you can add another named range called "Print_Titles". Set its "Refers to" box to "$10:$11". Then, anytime you add a month, the print area (the area of the sheet that gets printed by default) will automatically be the last 12 months plus the header row.
|
|
#
?
Jan 19, 2013 19:34
|
|
|
I often log my start and end time using the default "Shift+Alt+;" but is there a way I can get seconds to show up and on the third column it will automatically display how long it took me exactly? Something along the lines of this? (it should be 3 seconds on row 1 and 1 minute on row 2 but you get the point  )
|
|
#
?
Jan 23, 2013 15:40
|
|
|
Disharmony posted:I often log my start and end time using the default "Shift+Alt+;" but is there a way I can get seconds to show up and on the third column it will automatically display how long it took me exactly? Something along the lines of this? The shift+alt+; shortcut always returns the "seconds" part as zero. I don't think there is any way to do this without VBA. You can do something like: code:Also, looks like it was a mistake, but I just want to point out that the "3 ms" on your picture is very difficult to measure within Excel/VBA. The shortest time that can easily be measured is 1 second. For shorter times you would have to do something like this...but I doubt that you can hit that keyboard shortcut fast enough anyway.
|
|
#
?
Jan 24, 2013 01:01
|
|
|
Disharmony posted:I often log my start and end time using the default "Shift+Alt+;" I've been trying this key combo and nothing happens. What does it do for you?
|
|
#
?
Jan 25, 2013 17:16
|
|
|
Old James posted:I've been trying this key combo and nothing happens. What does it do for you? It's actually ctrl+shift+;, not alt. I forgot to check when I wrote my answer earlier. It sets the value of the highlighted cell to the current time, but the "seconds" part of the time is always returned as 0.
|
|
#
?
Jan 25, 2013 23:02
|
|
|
I've got a worksheet with a bunch of formulas using the indirect function to reference other cells. Is there a way to convert all of that to the "normal" referencing method?
|
|
#
?
Feb 4, 2013 05:15
|
|
|
Ragingsheep posted:I've got a worksheet with a bunch of formulas using the indirect function to reference other cells. Is there a way to convert all of that to the "normal" referencing method? I threw something together that you should be able to adapt into your own thing; not knowing what your spreadsheet looked like, I built a dummy one with INDIRECT functions built into one column. Select the first INDIRECT cell and make sure the column four to the right is empty, and this macro code will spit out the direct reference forumlae into that empty column. Where it gets complicated is if you have more than one indirect function in a cell, but the macro should give you a basic idea of how to make something that would work in that case. code:
|
|
#
?
Feb 6, 2013 19:01
|
|
|
This is awesome. Thanks.
|
|
#
?
Feb 7, 2013 00:38
|
|
|
Okay, I have a question. I have a db that's a flat file in excel, and I am trying to put it into Access (ugh). One of the columns is pictures (photos of inventory), and I'd like to somehow take these excel objects and turn them into files so I can include them as objects in an Access database. Or hell, even if I could extract the pictures into files that I can name and move around, that would be a start. Any thoughts as to how to go about this?
|
|
#
?
Feb 11, 2013 03:16
|
|
|
Turkeybone posted:Okay, I have a question. I have a db that's a flat file in excel, and I am trying to put it into Access (ugh). One of the columns is pictures (photos of inventory), and I'd like to somehow take these excel objects and turn them into files so I can include them as objects in an Access database. Or hell, even if I could extract the pictures into files that I can name and move around, that would be a start. Any thoughts as to how to go about this? This sounds horrible. Someone else please correct me if I am wrong, but in Excel images are not part of the range object but shape objects with coordinate properties. If you import the spreadsheet into Access it won't know which row of data matches which image. I don't think any VBA code would help either. How many rows of data are you looking at? Old James fucked around with this message at 04:45 on Feb 11, 2013 |
|
#
?
Feb 11, 2013 04:43
|
|
|
Old James posted:This sounds horrible. Someone else please correct me if I am wrong, but in Excel images are not part of the range object but shape objects with coordinate properties. If you import the spreadsheet into Access it won't know which row of data matches which image. I don't think any VBA code would help either. Visual Basic .NET code:You'll get a directory (outPath variable in ExportToFile) full of files named 1.jpg, 2.jpg etc. based on which row they are in, not accounting for headings or anything like that. Unfortunately I couldn't figure out what weird scale it was using for shape.Left so I didn't account for what column the picture is in, but hopefully you only have one column with pictures. If not it's *probably* not too hard to work the math out anyway. Also the code relies on data being in Sheet1 (name of the sheet is unimportant), and the temporary chart being in Sheet3 (name is important), so that will need to be adjusted if you have several sheets and Sheet3 already in use. It's also a bit hacky because of the way properties of charts are made accessible (ActiveChart does not give access to Width, Height and Border properties or the Cut method, while Sheet3.ChartObjects(1) does) which is why the sheet names are hardcoded as I really couldn't be bothered trying to work that out in a reliable way quickly. The Gripper fucked around with this message at 17:00 on Feb 11, 2013 |
|
#
?
Feb 11, 2013 08:13
|
|
|
Yeah, it's a pretty bad design, and then bringing it into access on top of that. Just completely non-technical college kids at work. It's maybe like 200 rows altogether. I can totally just make a dummy copy and make it work with the constraints of this program; I'll let you know how it turns out, thanks!
|
|
#
?
Feb 11, 2013 14:19
|
|
|
How do you add $ signs to fix a cell reference? I've been typing them in manually for years, but during a demonstration today the guy was pressing something and they were appearing automatically.
|
|
#
?
Feb 12, 2013 18:58
|
|
|
Hoops posted:How do you add $ signs to fix a cell reference? I've been typing them in manually for years, but during a demonstration today the guy was pressing something and they were appearing automatically. F4 to cycle through the various permutations of absolute references.
|
|
#
?
Feb 12, 2013 19:04
|
|
|
|
| # ? May 24, 2024 01:23 |
|
|
.
maskenfreiheit fucked around with this message at 21:25 on Apr 28, 2019 |
|
#
?
Feb 13, 2013 00:14
|
|