|
Aredna posted:I think this will do what you need if you put it in BI4 and copy it down: =INDEX($AC$3:$BG$3,0,MATCH(BH4,AC4:BG4,0)) I'm more than happy to rearrange the data and just not use this table. Probably the most sensible way of going, actually. Thanks for the suggestion, I will try it shortly
|
# ? Aug 22, 2012 00:17 |
|
|
# ? Jun 27, 2024 16:59 |
|
Edit: Rephrased question. I have a lot of results from many different types of races in excel. I'm preparing to store these in a MySQL DB, so I need to prepare the data. I'm planning to have 2 columns: one for seconds, and one for milliseconds. So a race that is 30 min 6 sec long would have "1806" in col A and "00" in col B. Likewise, a race that is 16 min 40 sec and 24 ms would have "1000" in A and "24" in B. My issue: The data is not entered in a uniform way. Take a look at Col H. Pretty much nothing has leading zeroes. So my first example is entered as 30:06, and my second example is 16:40.24. However, when I set the column format to [h]:mm:ss.00 (Col I), only some of the values are correct. How can I process all of these non uniform inputs and get it split to minutes and seconds? Is this the best approach? Thanks Lt Moose fucked around with this message at 23:08 on Aug 22, 2012 |
# ? Aug 22, 2012 22:46 |
|
Lt Moose posted:I'm having issues with time formats in excel. I have 1000+ rows that have results from various races. I've gotten them to display how I'd like using [h]:mm:ss.00 as the cell format. However, for this project, I will be putting these in a MySQL DB, and I will need to create a separate col for the ms. How can I do that in Excel? No time to play around but I'm pretty sure you want text to columns. http://office.microsoft.com/en-us/excel-help/split-names-by-using-the-convert-text-to-columns-wizard-HA010102340.aspx
|
# ? Aug 22, 2012 22:56 |
|
Thanks, I'll try to play around with that more. I actually changed how I'm going to approach this, and I rephrased my original question, but this still could be helpful.
|
# ? Aug 22, 2012 23:06 |
|
Lt Moose posted:
Stuff like this is basically exactly what google refine was made to do.
|
# ? Aug 22, 2012 23:14 |
|
Is column H text value or time value? If text value then in column J use the formula code:
code:
code:
code:
Old James fucked around with this message at 06:04 on Aug 23, 2012 |
# ? Aug 23, 2012 05:57 |
|
EAT THE EGGS RICOLA posted:Stuff like this is basically exactly what google refine was made to do. THANK YOU! This helped a ton. After some work I finally got it converted and cleaned up. Once I looked at it with that, I did a few different cases and corrected the values. It appears that the main problem was that everything was stored in days (e.g. a value of 1.0 = 24 hours), but it wasn't always correct. So sometimes a 30 minute race would be stored as 1.25 (incorrect, 30 hrs) or 0.0208 (correct).
|
# ? Aug 24, 2012 02:51 |
I am trying to use Google Refine (great program btw, thanks for the suggestion guys) to call a web service for information to fill out 23,000+ rows of data. Now when I go to this webpage it provides me with a search box to fill out. I need to enter a number into one of two boxes and click search which then displays the page of information I need. The problem that I am currently having is that the URL between the search page and the display page doesn't change. Its the same https://www.blahblah/getscoutticket.asp for both. The question is can google refine still fetch data from this webservice? Is there a URL I can feed it that will do the search? How would I figure this out?
|
|
# ? Aug 28, 2012 22:22 |
|
I often use excel to create large arrays, but when I want to save it to a text file for processing in code I've written, excel insists on adding in carriage returns. For whatever reason, this doesn't screw things up when there are multiple objects in a line, but if I have a 1D array, my programs don't know what to do. Is there *any* way to force excel to save files in a normal way without ^M all over the place and just using the standard of \n? It also makes reading the files in vi much easier. The only way around this I have seen is to NOT use excel and c/p into emacs. EDIT: To clarify, the code works fine when given a proper text file, it is excel that is screwing it up because it is adding in ^M instead of making a proper new line. EDIT2: Another way to fix this is to go into pico and save as a MS-Dos format, which seems to remove the carriage returns. Nonetheless, it's a pain in the rear end step that I wish to avoid. Is there a setting to force excel to not think it knows better than me? JetsGuy fucked around with this message at 16:23 on Aug 29, 2012 |
# ? Aug 29, 2012 15:47 |
|
JetsGuy posted:I often use excel to create large arrays, but when I want to save it to a text file for processing in code I've written, excel insists on adding in carriage returns. For whatever reason, this doesn't screw things up when there are multiple objects in a line, but if I have a 1D array, my programs don't know what to do. Does it include the carriage return when saving as a csv? You could write a macro that steps through the cells and writes them to a text file if there is no option within Excel to fix your problem.
|
# ? Aug 29, 2012 16:56 |
|
I feel dumb as hell and feel like I've read every google result for this issue, but I just can't seem to get this to work properly. I have a set of code that will generate a random person and their attributes. This works exactly as I want, so that's great. I want this code to then go to the next row and repeat 10 times, so I have ten people. I cannot get a loop to work in any way, it seems to just go infinitely, and I don't understand enough to set a counter up. Any help appreciated. code:
|
# ? Aug 30, 2012 12:32 |
|
You just need a 'For' loop and to offset the range. Also, you didn't seem to be saving much effort by using 'With'. code:
Old James fucked around with this message at 14:48 on Aug 30, 2012 |
# ? Aug 30, 2012 14:23 |
|
JetsGuy posted:EDIT2: Another way to fix this is to go into pico and save as a MS-Dos format, which seems to remove the carriage returns. Nonetheless, it's a pain in the rear end step that I wish to avoid. Is there a setting to force excel to not think it knows better than me? celestial teapot fucked around with this message at 16:35 on Aug 30, 2012 |
# ? Aug 30, 2012 16:29 |
|
Old James posted:
Thanks a huge amount, I really appreciate it. Still picking this stuff up. My only issue now is that the name plucked from the list (the B4 bit down the bottom) no longer picks a random name - it just picks the same one for all ten records. It was working previously, so I'm not sure what to do now.
|
# ? Aug 30, 2012 17:01 |
|
Gooses and Geeses posted:Thanks a huge amount, I really appreciate it. Still picking this stuff up. My only issue now is that the name plucked from the list (the B4 bit down the bottom) no longer picks a random name - it just picks the same one for all ten records. It was working previously, so I'm not sure what to do now. Oops, It is picking a unique value but then replacing it with the value in B4. Change the second to last line to be code:
|
# ? Aug 30, 2012 18:15 |
|
I have an excel spreadsheet with a single column that has times in it. The times are given as strings that have the format "x hours yy minutes" or just "yy mins" (for example: "3 hours 21 mins" or "33 mins"). I would like to convert the times to decimal representations, i.e., 3 hours 21 mins would become 3.35 (3 + (21/60)), and 33 min would become 0.55. I am at a loss as to how to accomplish this in Excel. Help please.
|
# ? Sep 18, 2012 02:59 |
|
Do you need this sheet to look a certain way or something? Easiest solution that comes to mind is Text to Columns with " " as a delimiter. This will split each cell into either 2 cells or 4 cells. Sort by your new column 3, which will be empty for <1 hour values, grouping the >1 hour and <1 hour entries together. Then just do math on the hour and minute values and you're good.
|
# ? Sep 18, 2012 03:59 |
|
Jose Cuervo posted:I have an excel spreadsheet with a single column that has times in it. The times are given as strings that have the format "x hours yy minutes" or just "yy mins" (for example: "3 hours 21 mins" or "33 mins"). I would like to convert the times to decimal representations, i.e., 3 hours 21 mins would become 3.35 (3 + (21/60)), and 33 min would become 0.55. You can also try this! EAT THE EGGS RICOLA posted:Stuff like this is basically exactly what google refine was made to do. I watched the video and it'll definitely solve your problem, but I just haven't gotten aroudn to doing it yet. I'm pretty sure you can do an addif() for this too, but I loving hate addifs.
|
# ? Sep 18, 2012 15:53 |
|
ZerodotJander posted:Do you need this sheet to look a certain way or something? Easiest solution that comes to mind is Text to Columns with " " as a delimiter. This will split each cell into either 2 cells or 4 cells. Sort by your new column 3, which will be empty for <1 hour values, grouping the >1 hour and <1 hour entries together. Then just do math on the hour and minute values and you're good. Thanks, the "Text to Columns" trick worked.
|
# ? Sep 18, 2012 16:04 |
|
I have a table with interest rates and associated spreads based on a par rate for the day. I cannot come up with a formula that works correctly to first find the set of columns that corresponds to the daily par rate and then return the values for the associated interest rates. The table looks like this: So if the par rate for the day was 1.875% and I wanted to see the spread for 3.875% it would return -0.0080634. I've tried VLOOKUP, HLOOKUP, INDEX, etc. If anyone has a good starting point I'd be supremely grateful.
|
# ? Sep 20, 2012 19:07 |
|
Assuming your table is set up this: Your formula in cell L5 is: code:
|
# ? Sep 20, 2012 19:20 |
|
Aredna posted:Assuming your table is set up this: That works for the example I tried, thank you so much. I should have added this in the initial post but if I need to copy the formula down a column for each interest rate, what do I need to change for it to continue to work (if you continued down column L with descending rates)? Right now it only seems to work for the initial one I try.
|
# ? Sep 20, 2012 19:51 |
|
Aredna posted:Assuming your table is set up this: Wait I just realized how dumb my questions is and took it off.
|
# ? Sep 20, 2012 19:52 |
|
Rowe posted:That works for the example I tried, thank you so much. I should have added this in the initial post but if I need to copy the formula down a column for each interest rate, what do I need to change for it to continue to work (if you continued down column L with descending rates)? Right now it only seems to work for the initial one I try. Change any fields it is pointing to that won't be moving to be static references by adding a $ to the fields. I would guess this is what you need, but you may need to make some additional customizations. code:
|
# ? Sep 20, 2012 19:58 |
|
Aredna posted:If you do have some trouble with the changes and can post showing your layout in a bit more detail with the cells labeled I can make the remaining changes quickly. Nevermind, it works now with the cells anchored. Thank you so much again! Rowe fucked around with this message at 20:30 on Sep 20, 2012 |
# ? Sep 20, 2012 20:21 |
|
Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries?
|
# ? Sep 25, 2012 02:23 |
|
Tricerapowerbottom posted:Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries?
|
# ? Sep 25, 2012 03:59 |
|
Tricerapowerbottom posted:Simple question: I have a series of long urls that I want to be truncated into small cells. How can I either make them all display something like "url" in a batch, or force the cells to contain the text in their own boundaries? This came up a few months ago and I posted this code... Old James posted:
Just change TextToDisplay:=link to TextToDisplay:="url"
|
# ? Sep 25, 2012 05:32 |
|
Thanks!
|
# ? Sep 25, 2012 15:56 |
|
I am awful and rusty at VBA and have run into a problem. I am trying to write a macro that will do a text to column based off a space when I hit CTRL G. I don't remember how to have it do the text to column into the column I have selected. This is what I have so far.code:
|
# ? Oct 1, 2012 20:49 |
|
This is sort of out of place as I don't have a specific question directly soliciting help, but since there are _tons_ of advanced Excel users here, I figured there'd be no better place to ask for advice. (Mods feel free to delete this post if it's out of place). As many of you know, Office 13 and Office Live(the online, surprisingly full-featured version of Office) are both going to have app stores. I already have one project written and waiting for approval, but I need your advice. The solutions I write tend to be .NET/MS SQL based so I don't know what problems you guys tend run into, how you use it (I know there's a lot of VLOOKUPS and VBA in there, with some VBA thrown in but that's more or less the extent of it). I want to know what problems you commonly run into and what solutions 1) -- you wish were built to make your lives easier, or 2) -- have already built and think that other people might like If you help me out with an idea that is widely applicable to the masses, I will be not only extremely grateful but willing to collaborate with you on a free solution for your organization (with long-term support too! what a great deal!) in appreciation for your feedback. Quote this or PM me with the problem and a little bit of information about your organization*(read the edit ) and I'll see what my dev team and I can hammer out. Thanks Edit: I've gotten great feedback so far but I really want to know how you guys use your tools too. Does Cindy share your workbook with John in HR and get approval from the manager before doing X? How many people are in your organization? Use cases are real important to help me understand how you guys work. That way we maybe can identify problems we didn't even know existed and throw a solution out to save everyone boatloads of time. wither fucked around with this message at 01:04 on Oct 2, 2012 |
# ? Oct 2, 2012 00:04 |
|
wither posted:This is sort of out of place as I don't have a specific question directly soliciting help, but since there are _tons_ of advanced Excel users here, I figured there'd be no better place to ask for advice. (Mods feel free to delete this post if it's out of place).
|
# ? Oct 2, 2012 00:15 |
|
Veskit posted:I am awful and rusty at VBA and have run into a problem. I am trying to write a macro that will do a text to column based off a space when I hit CTRL G. I don't remember how to have it do the text to column into the column I have selected. This is what I have so far. Well currently you have it basing the text to columns off of your current selection, if you want the destination cells to start on the same cell, you can change code:
code:
edit: ZerodotJander posted:One great Excel tool I and a lot of people in my industry would love would be something that can convert between Google AdWords bulksheet export files and pretty, readable looking views of Search Engine Marketing campaigns. Absolutely, I'd love some cool AdWords tools. There was some Google Analytics/AdWords/BingAds combo pack .xlsm that some guy had built out that was pretty cool, but I think it's no longer supported and it was kind of clunky. I'll shoot you a PM. DukAmok fucked around with this message at 00:57 on Oct 2, 2012 |
# ? Oct 2, 2012 00:53 |
|
DukAmok posted:Well currently you have it basing the text to columns off of your current selection, if you want the destination cells to start on the same cell, you can change I will do this when I get back to work. Like I said really rusty at VBA and only recently started getting back into using it because I starting to have to need macros. Are there good open source videos to relearning VB/VBA? I found one series from a website that helped me refresh on SQL, so if you guys have any suggestions I would much appreciate it. Just FYI it's been 3 years since I've used VB back in college for programming and have avoided it to this day.
|
# ? Oct 2, 2012 00:58 |
|
Veskit posted:Are there good open source videos to relearning VB/VBA? I found one series from a website that helped me refresh on SQL, so if you guys have any suggestions I would much appreciate it. MrExcel was a great resource when I was picking a little up here and there, I think there are lots of tutorials and some great forums around still.
|
# ? Oct 2, 2012 01:03 |
|
DukAmok posted:
I love this idea. What specific things would you guys like to see with regard to workflow. I use AdWords myself, but I'd be interested in seeing how you guys would like to import your stats (I'm presuming to see performance) and ... well then what would you like to happen after that? More ideas guys, please, this stuff is fantastic. DukAmok and ZerodotJander hopefully we can work together to build a tool like that combo pack but better.
|
# ? Oct 2, 2012 01:18 |
|
I'm not even talking stats or any sort of management tool. That stuff is handled well by robust packages that agencies and companies pay a pretty penny for. I'm talking purely a visual tool that allows AdWords managers to create and share campaigns in a more visually intuitive format, so it's easier to see how an Ad Group or Campaign is structured; but be able to quickly convert between that visually appealing format and a bulksheet format that would be easy to paste into AdWords Editor to actually make changes. The big problems I've seen in attempts at this tend to be dealing with inconsistencies between how many data fields exist for each campaign/adgroup - some groups might have 1 ad, some might have 7. Some groups might have 500 negative keywords, some might have 0. One campaign might be targeting 400 different zip codes, another might have no location targeting specified.
|
# ? Oct 2, 2012 01:41 |
|
ZerodotJander posted:I'm not even talking stats or any sort of management tool. That stuff is handled well by robust packages that agencies and companies pay a pretty penny for. quote:I'm talking purely a visual tool that allows AdWords managers to create and share campaigns in a more visually intuitive format, so it's easier to see how an Ad Group or Campaign is structured; but be able to quickly convert between that visually appealing format and a bulksheet format that would be easy to paste into AdWords Editor to actually make changes. OK, so given these large variables, what would help? Do you want to aggregate the 500 keywords into groups? I'm not sure I quite understand your problem . If you'd like we could setup a screen-share (or if you could, just do a vid-cap) showing the problems along with a voice over of what you'd like or something. I'm not sure, just spit-balling solutions since you can't show me what your problem is in person
|
# ? Oct 2, 2012 02:07 |
|
Sure, let me scrub some spreadsheets and upload some examples. Obviously anybody else is welcome to jump in with their own examples. There are definitely ways to make this more robust and powerful, but I actually think that would reduce the utility of the tool to people in my kind of position while making it much more expensive to develop. Edit: Here is a sample bulksheet - http://www.zerodotjander.com/excelfiles/bulksheetsample.csv I've replaced numerical values with "xx". And here is a sample "pretty" view of most of the same data, which is much more readable to someone who doesn't eat live and breathe this type of work - http://www.zerodotjander.com/excelfiles/Messaging_Pretty_Sample.xlsx Note that this pretty version doesn't include all the data from the bulksheet - no negatives, no bids or performance info - just campaign, positive keywords, ad text, destination URL. Doesn't need to be exactly this format or anything but you get the general idea - moving from an inscrutable CSV to something someone who doesn't work with these spreadsheets all day can read. ZerodotJander fucked around with this message at 17:23 on Oct 2, 2012 |
# ? Oct 2, 2012 02:15 |
|
|
# ? Jun 27, 2024 16:59 |
|
Your .xlsx is 404'ing Can you email it to me at wither@gmail.com ? More ideas guys!wither posted:As many of you know, Office 13 and Office Live(the online, surprisingly full-featured version of Office) are both going to have app stores.
|
# ? Oct 2, 2012 03:25 |