|
This looks like kind of job Google Refine was built for - http://code.google.com/p/google-refine/ If you can't find a good way to automate it in Excel, this tool should make manually cleaning the data much easier compared to doing it directly in Excel.
|
# ? Jun 21, 2012 22:21 |
|
|
# ? May 13, 2024 05:24 |
|
I'm not sure how I lived before I knew Google Refine existed.
|
# ? Jun 22, 2012 01:33 |
|
I tried to make a macro yesterday that would find a string by looking at a column on one worksheet and if it found it would copy and paste it to another worksheet. It works fine for the original column I set it up for, but if I modify it by changing x to x = 7 so it looks at column G instead of E it won't work unless the row number in line 4 is set so the second number matches the x value. The problem is it then skips all rows above 7 so if there is a match above it it doesn't get copy and pasted. If that number on line 4 doesn't match x the macro doesn't work. I'm confused as to why it is behaving like this.code:
|
# ? Jun 22, 2012 15:03 |
|
Brennan posted:I tried to make a macro yesterday that would find a string by looking at a column on one worksheet and if it found it would copy and paste it to another worksheet. It works fine for the original column I set it up for, but if I modify it by changing x to x = 7 so it looks at column G instead of E it won't work unless the row number in line 4 is set so the second number matches the x value. The problem is it then skips all rows above 7 so if there is a match above it it doesn't get copy and pasted. If that number on line 4 doesn't match x the macro doesn't work. I'm confused as to why it is behaving like this. Cells(x,6) looks at row x in the 6th column (F). So when you change x=7 you are looking at cell F7 and not E6 as you intended. So if F7 is blank the Do loop never triggers.
|
# ? Jun 22, 2012 15:34 |
|
I apologize in advance, this is my first attempt at anything with code. I'm confused because when I change it to x = 7 and line 4 to If Cells(x, 7) = "newstring" Then it seems to start at G7 not F7. It will copy/paste the row if the string in G7 matches, like it will copy/paste the row if E6 matches in the original code. I've also seen conflict. I found this odd yesterday because everything I read said Cells should be (row, column) but it seemed to be reversed. Would it be better to move to two variables and just change the variables as needed? I tried to use Range first and I was getting errors which is why I switched to Cells.
|
# ? Jun 22, 2012 17:03 |
|
Does anyone know a way to count cells that don't contain a value of zero and are not in a range? I need to count the number of cells but I can only find ways that work if it is a range and not just a bunch of cells.
|
# ? Jun 24, 2012 20:44 |
|
By range do you mean named range (e.g. 'results') or cell range (e.g. A1:A500)? If the latter, unfortunately COUNTIF only accepts ranges (or one individual cell) as an argument. You can kind of get around it though if you can clearly identify the cells you don't want counted: http://www.pcreview.co.uk/forums/using-countif-specific-cells-t2532815.html What they're basically doing is one COUNTIF range subtracted from the other; I'm not sure if this would help you, or if you just need to be able to arbitrarily count specific cells (e.g. COUNTIF(A2 Q11 Z23,">0")).
|
# ? Jun 26, 2012 23:57 |
|
I have a column that has a number used for an identifier. An example might look like:code:
code:
|
# ? Jun 27, 2012 19:36 |
|
uG posted:Is there a way to do this with conditional formatting, or do I need to learn/write a macro? The macro is pretty short so I just whipped one up, seems to work for your data set and some more that I threw in to test it. code:
DukAmok fucked around with this message at 19:58 on Jun 27, 2012 |
# ? Jun 27, 2012 19:55 |
|
Just add extra rows to the end, 1 for each identifier, with no name. Then sort by the numbers to insert the extra rows where you want them. Filter your second column to blanks to delete the values in column 1, and you're done. Edit for clarity- Do this: code:
ZerodotJander fucked around with this message at 21:43 on Jun 27, 2012 |
# ? Jun 27, 2012 21:40 |
|
Does anyone know of a good tutorial for solving polynomial systems of equations in excel? I have 2 unknowns in 2 third degree polynomial equations. Ideally I would set up the sheet to have a few input values that go into the equations, and it could simply spit out the results, which I could then do some other basic calculations on to show useful information.
|
# ? Jul 5, 2012 19:46 |
|
So I have this spreadsheet of all property sales for NJ for the years 2002-2011 (let's call it "master"). In a second spreadsheet, I have observations of interest culled from the master sheet (let's call it "observations"). What I need to do is figure out a function that will search the master spreadsheet for instances where the text in columns B (seller's name) and E (buyer's name) and the date of sale in column Q matches in both tables and return the entire matching row (that is, the data in columns A to Z) to a third spreadsheet. Secondly, I need to write a function that will do the same as above only it needs to pull values only if the text in column L is "3B". To the first function can I do something like code:
code:
What if there's 4 or 5 matches in the matches for a given set of values in my observation table? Will this affect my results (that is, will it return each of the matches or only the first)? Am I better off building a pivot and doing a getpivot function (and if so, any pointers here would be welcome as I don't have a lot of experience with pivot functions)?
|
# ? Jul 6, 2012 14:27 |
|
Is there a way of restricting values entered into a cell to be only numeric? Data Validation only seems to allow this for numbers within a certain limit - I don't want to limit the numbers entered, just the data type. Essentially, the limit is +/- Infinity, but I don't see an easy way of doing that without VBA (which I'm not allowed to use in this WB). I thought about the "Custom" Data Validation option using If(ISNUM({CellAddress}),TRUE,FALSE) but that doesn't seem to allow anything (in that it chucks up a "this is not allowed" notification for any entered value, numeric or otherwise) plus it's not dynamic so I'd have to make a formula for a huge range of cells Edit: I guess what I'm really asking is: "Is there a way of doing it without setting the limits to crazy high/low numbers like +999999999999/-999999999999999 ? Sonic H fucked around with this message at 14:08 on Jul 13, 2012 |
# ? Jul 13, 2012 14:03 |
|
I doubt this is possible, but figured I would ask as someone might know. I'm trying to build a spreadsheet to budget per month what I take in and what I expense out. Right now I post my paycheck each time I receive one, and I post up what dates and the amount of when my expenses would be charged to my bank account for auto payments. I'm wondering if it was at all possible to pull the date/amount due from the sites automatically when they update so I can see without having to login each time?
|
# ? Jul 13, 2012 18:03 |
|
You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls. You have two methods of doing this - one is having your script mimic user inputs in to form fields and the other is doing direct POST calls to the relevant pages. For online banks I imagine their site security will mean option 1 is the only feasible one. I can try and help you out but there is quite a bit involved.
|
# ? Jul 14, 2012 19:04 |
|
G-Dub posted:You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls. Unfortunately I'm a beginner, so this might be too much trouble than it's actually worth. There is no problem checking all the time what the due date is and such, but I figured this would just be easier.
|
# ? Jul 15, 2012 00:20 |
|
Sonic H posted:Is there a way of restricting values entered into a cell to be only numeric? Data Validation only seems to allow this for numbers within a certain limit - I don't want to limit the numbers entered, just the data type. Essentially, the limit is +/- Infinity, but I don't see an easy way of doing that without VBA (which I'm not allowed to use in this WB). Edit: No clue if this worked outside of Excel 2010 Highlight cell. Data Validation > Custom forumla is: IsNumber(cell) e.g. IsNumber(B2) Copy the cell Paste Special > Validation
|
# ? Jul 16, 2012 16:02 |
|
G-Dub posted:You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls. I did a search for Microsoft Internet Controls, and can't really find exactly what to do. Is there a tutorial page that might walk me through what to do that you know of?
|
# ? Jul 16, 2012 17:27 |
|
I haven't seen any tutorials. I learned how to use the IE object by instancing one and loving around. Start off with the following (not written in the editor so sorry if it doesn't compile) and have a play around with the different IE methods. Remember to reference Microsoft Internet Controls code:
|
# ? Jul 16, 2012 20:14 |
|
Hello! I asked this in completely the wrong thread before so hopefully it will go down better here. I have some data arranged like this: The data in the column in green are the names of different genes. The data in the red row are the annotations that match the individual genes so I know what they are and what they do. In the Excel sheet I've got there are 400,000 of those rows. I've put this data through another program to give me some genes of interest and its given me about 350 names but that is separated from those annotations. Is there anyway I can find those 350 genes in my huge excel sheet automatically and extract their individual rows all at the same time without having to manually "ctrl-f" 350 times for each individual interesting gene and copy and paste each one myself? Sorry if I'm not explaining this very well!
|
# ? Jul 16, 2012 22:18 |
|
Olewithmilk posted:Hello! I asked this in completely the wrong thread before so hopefully it will go down better here. I have some data arranged like this: Two quick ways: 1. Add another sheet, and put in a column of your 350 gene names, and use VLookups in the columns to the right of it to pull out the useful attribute value into those columns. So if "15071_c0_seq1" was in A2, you'd have B2 as "=vlookup(A2,Sheet1!A:M,4,False)" or something similar, and copy that across to C2-M2 or however many attributes you want to pull out. or 2. Add another sheet, put in the column of your 350 gene names. Add another column to your original sheet, and in it put a vlookup across that second sheet column of names. So like, "=vlookup(A2,Sheet2!A:A,1,False)", which will just return itself if it finds it, or an error if not. Then just filter or sort by that extra column on your original data set. DukAmok fucked around with this message at 23:17 on Jul 16, 2012 |
# ? Jul 16, 2012 23:14 |
|
Olewithmilk posted:Hello! I asked this in completely the wrong thread before so hopefully it will go down better here. I have some data arranged like this: Good timing. This afternoon I wrote a macro for a coworker that basically does the same thing you want. code:
Old James fucked around with this message at 16:56 on Jul 17, 2012 |
# ? Jul 17, 2012 05:05 |
|
Parks posted:You can do this with a customer data validation. Legend, cheers. Although whether it works in 2003 is another matter (don't ask).
|
# ? Jul 17, 2012 16:40 |
|
I have a really strange Excel 2007 problem. I need to create a footer on one of my sheets in a workbook, but the field just isn't there under Page View. Header is available to create and I can insert default footers through the dropdown, but I can't edit those. This is a spreadsheet that contains formulas and formatting that my boss uses, so I can't just start over. I've tried google search to no avail. This is bugging the poo poo out of me because I feel like I'm missing something obvious. Please help, goons!
|
# ? Jul 18, 2012 16:08 |
|
ScarletBrother posted:I have a really strange Excel 2007 problem. You should be able to edit the footer then. stuxracer fucked around with this message at 16:54 on Jul 18, 2012 |
# ? Jul 18, 2012 16:50 |
|
Parks posted:When you do Insert > Header/Footer and the view switches. Do you have "Go to Footer" in the ribbon? "Go to Footer" and "Go to Header are both greyed out under the Insert > Header/Footer selection. I'm totally baffled.
|
# ? Jul 18, 2012 18:31 |
|
ScarletBrother posted:"Go to Footer" and "Go to Header are both greyed out under the Insert > Header/Footer selection. I'm totally baffled. Google has some other random things people posted that could be it.
|
# ? Jul 18, 2012 19:50 |
|
Parks posted:Haha, that is awesome. I know protected mode does that, but if you can add things from the drop-down I doubt it is that. Check anyways. Finally was able to find an answer! Or a workaround at least. It still makes no sense why the issue popped up in the first place. 1. Go to Page Layout tab. Under Gridlines and Headings, click the little arrow in the Sheet Options box. This will open the Page Setup dialong box. Click Header/Footer tab and then click Custom Footer. This will allow editing of both the header and footer. 2. Click the "Office Button" and point cursor at Print, then choose Print Preview. Click on the Page Setup icon to bring up the Page Setup dialog box and click on Header/Footer, etc.
|
# ? Jul 18, 2012 20:17 |
|
My work loves to use Excel for things other than its intended use. Case in point, every day we create new spreadsheets for each of our stations. We use them to write down any issues we have during the day. Currently, we open up three different files, rename one cell to today's date, then save it with the station call letters and today's date. So one station would be THISTV071912. What I would like to do is have one workbook with all three spreadsheets in it as different sheets, have the date entered automatically at the top, and then have it save as three separate files in different folders. Like so, code:
Auto changing the date is easy enough within the excel sheet, its the separating the workbook into three separate files and renaming them that is throwing me off. Is this even possible?
|
# ? Jul 19, 2012 15:32 |
|
diremonk posted:My work loves to use Excel for things other than its intended use. Case in point, every day we create new spreadsheets for each of our stations. We use them to write down any issues we have during the day. Try this as a Macro: code:
|
# ? Jul 19, 2012 15:45 |
|
Ok, that seems to work. But it is saving it to my document folder. I did a bit of searching and added this code to the macro but that doesn't seem to work along with having the wrong date type.code:
|
# ? Jul 19, 2012 16:46 |
|
code:
|
# ? Jul 19, 2012 16:50 |
|
ScarletBrother posted:
Not really, it is still saving the files to the My Documents folder and not appending the date onto the end of the file name. I checked with my IT admin and the share is not password protected, or at least I don't think so since I have permissions to write to it. I tried to start over from scratch and it just generated about 15 new excel files on my desktop. I'm using Excel 2010 on Win 7 if that is any help. I really appreciate the assistance.
|
# ? Jul 19, 2012 17:28 |
|
Got it!code:
|
# ? Jul 19, 2012 18:45 |
|
What is the best way to have values from one worksheet display in a cell in another worksheet. Not in a range or anything, just like cell C6's value will display in the other cell. Also is there anything wrong with just being like ='[filenameblah.xlsm]WORKSHEETNAME'!C6 ? The value comes over and it works. Before I was using a vlookup that was like =VLOOKUP(location of the cell, location of the cell, 1) and it worked. However it seems like a lot for what I need to do.
|
# ? Jul 23, 2012 21:14 |
|
Stealthgerbil posted:What is the best way to have values from one worksheet display in a cell in another worksheet. Not in a range or anything, just like cell C6's value will display in the other cell. If you're in the same workbook you don't need the [filename] notation, just the worksheet name should do it. And yep, that's totally fine. If the data you want to display will always be in C6, there's no need to use anything other than a direct cell reference. Save VLookups for when you're actually looking within a table for some data.
|
# ? Jul 23, 2012 21:19 |
|
Now that I got that working, I was wondering if it is possible to make the file location that it is retrieved by determined by a cell value. Lets say I have the worksheet determine the file name and it ends up being like 'filenameblah.xlsm'. How would I make it check like ='[C2]WORKSHEETNAME'!C6 where the name of the file is in cell C2?
|
# ? Jul 23, 2012 22:35 |
|
Stealthgerbil posted:Now that I got that working, I was wondering if it is possible to make the file location that it is retrieved by determined by a cell value. Lets say I have the worksheet determine the file name and it ends up being like 'filenameblah.xlsm'. How would I make it check like =INDIRECT("["&A1&"]Sheet1!B1") Will make the cell in your current workbook equal to the value of cell B1 on Sheet1 in the workbook named whatever is in the current workbook's A1. I've only done this sort of stuff as a temporary thing, where both workbooks are currently opened, but I believe this will still work if the referenced workbook is closed, as long as you provide the full path.
|
# ? Jul 23, 2012 22:52 |
|
Thanks this works great, although I haven't tested it yet on the network share portion. Sorry if took a couple days to get back to you about it, had to take a couple days off. If I could ask a bit more though, is is possible to have the individual sheets save to different folders? Or maybe point me in the direction on how to do it myself? It's kind of fun to get a nice macro or worksheet in Excel and look like a smart person to the boss. Again, thank you for working on this.
|
# ? Jul 24, 2012 06:12 |
|
|
# ? May 13, 2024 05:24 |
|
diremonk posted:Thanks this works great, although I haven't tested it yet on the network share portion. Sorry if took a couple days to get back to you about it, had to take a couple days off. You should be able to write a separate save path for each sheet. I can't take full credit for it, I did a fair bit of googling and copy/pasting from other forums. For the bit you want, I'd just search for 'different save paths' or something like that. Should be able to fit it in. Happy to help!
|
# ? Jul 24, 2012 15:30 |