|
Is there a way to automatically search an excel file for a list of words from another excel file and delete those words if they appear from the first excel file?
|
#
?
Oct 2, 2012 04:26
|
|
|
|
| # ? May 23, 2024 15:11 |
|
|
Ragingsheep posted:Is there a way to automatically search an excel file for a list of words from another excel file and delete those words if they appear from the first excel file? Does the column have a single word in it, or does it have multiple words?
|
|
#
?
Oct 2, 2012 04:52
|
|
|
Veskit posted:Does the column have a single word in it, or does it have multiple words? It'll generally be sentences which may contain one of the words I'm looking to get rid of. Ideally it should go though the whole workbook rather than just a single column.
|
|
#
?
Oct 2, 2012 04:59
|
|
|
Can you post a visual example because it soudns more like you're doing something in word rather than excel to me.
|
|
#
?
Oct 2, 2012 15:16
|
|
|
wither posted:Your .xlsx is 404'ing Edited the link and e-mailing. Another good tool would be one that took phrases from column A and removed them plus all word order variants from column B. So for example, Column A contains "pretty things" and "dumb things". Column B contains "pretty things" "things" "things dumb" "things dumb pretty". The tool should remove the 1st and 3rd items from Column B.
|
|
#
?
Oct 2, 2012 17:23
|
|
Can you email it to me at
|
Veskit posted:Can you post a visual example because it soudns more like you're doing something in word rather than excel to me. https://docs.google.com/open?id=0B06cZmYYUgZGQkQ0dDF6ajQ1S00 Data will be stored like that but there be multiple columns of it. Let's say that in a separate file, I have the words "excel" and "powerpoint", I'm looking for a way to automatically delete those words from the first file. Also, this is probably more a powerpoint 2010 related question but in the office, sometimes people are pasting in charts from excel and they're embedding in the excel workbook rather than just creating a chart linked to the data. I know that you can change the paste option but is there any place you can ensure that the default setting is the linked option? Also is there a way of stripping out the embedded workbook without having to re-paste everything?
|
|
#
?
Oct 4, 2012 01:39
|
|
|
Ragingsheep posted:https://docs.google.com/open?id=0B06cZmYYUgZGQkQ0dDF6ajQ1S00 Are you doing this with a lot of entries and need to constantly update it? Otherwise click crtl F and go to the replace tab and replace those words with a " ". Otherwise the macro recorder is pretty handy. I still can't really grasp how this all works just from that.
|
|
#
?
Oct 4, 2012 03:08
|
|
|
Range.Replace Method
|
|
#
?
Oct 5, 2012 19:25
|
|
|
Looks interesting. Thanks.
|
|
#
?
Oct 6, 2012 01:54
|
|
|
I'm making a spreadsheet recording continuing education credits for people who attended various courses, and ideally I'd do a mail merge with Word where all the data will appear on a certificate. Since a few of the courses are worth different amounts of credits, is there a way to define or tag the names of the courses with its respective number of credits? On the certificate I'd like to display the course titles as well as the total credits earned.
|
|
#
?
Oct 7, 2012 00:58
|
|
|
I am looking to make a template pivot table, where data can be pasted in each month and the results will be displayed in the pivot table. In a pivot table, is it possible to display a total of max fields? Example: Data pasted into worksheet: John, May, 1 John, Jun, 0 John, Jul, 1 Mary, May, 1 Mary, Jun, 0 Mary, Jul, 1 In pivot table, I am displaying col 1, not displaying col 2, and maxing col 3 Pivot Table Looks like this: John 1 Mary 1 Is it possible to have the total display 2? (sum of the maxes grouped by name)The only functions available are count, which would display 6, sum would display 4, and count num which would display 6. Maybe a secondary pivot table or table would work to group the data before the final pivot table?
|
|
#
?
Oct 31, 2012 15:17
|
|
|
Monocular posted:I'm making a spreadsheet recording continuing education credits for people who attended various courses, and ideally I'd do a mail merge with Word where all the data will appear on a certificate. It sounds like you are looking to make a vlookup table. http://www.contextures.com/xlFunctions02.html Use the coursename against a secondary spreadsheet with a lookup table in it to get the value of the course
|
|
#
?
Oct 31, 2012 15:24
|
|
|
Is there any way, probably through an add-on, to create a shortcut for formatting part of a cell as a subscript? It gets tedious making all the subscripts by hand and I'm sure there's some better way to do this. I'm using 2010, for the record, and had found something, but it only worked for 2005 or something.
|
|
#
?
Nov 4, 2012 21:30
|
|
|
I have a large spreadsheet, and I would like to delete all rows whose column B does not contain one of a number of substrings. That is, if the text in column B does not have substring1, substring2, or substring3 in it, I'd like the associated row to disappear. What would be helpful in doing this? I'm hoping I don't have to resort to scripting...
|
|
#
?
Nov 5, 2012 00:18
|
|
|
Ronald McReagan posted:I have a large spreadsheet, and I would like to delete all rows whose column B does not contain one of a number of substrings. That is, if the text in column B does not have substring1, substring2, or substring3 in it, I'd like the associated row to disappear. What would be helpful in doing this? I'm hoping I don't have to resort to scripting... Then add a column with the formula =if(or(A1="1",A1="2",A1="3"),true,false) assuming column A has the substrings and you want to delete when 1, 2 or 3. Paste down to all rows, then sort by this column and delete when true.
|
|
#
?
Nov 5, 2012 02:38
|
|
|
Karl Sharks posted:Is there any way, probably through an add-on, to create a shortcut for formatting part of a cell as a subscript? It gets tedious making all the subscripts by hand and I'm sure there's some better way to do this. I'm using 2010, for the record, and had found something, but it only worked for 2005 or something. I don't think you can do this. VBA will alter the entire contents of the cell and not just the first character. EDIT: Corrected by ShimaTetsuo below, thanks! Old James fucked around with this message at 14:57 on Nov 6, 2012 |
|
#
?
Nov 5, 2012 02:40
|
|
|
Old James posted:Then add a column with the formula =if(or(A1="1",A1="2",A1="3"),true,false) assuming column A has the substrings and you want to delete when 1, 2 or 3. Paste down to all rows, then sort by this column and delete when true. I probably should have been more specific; I'm looking for something more like =if(or(A1contains"1",A2contains"2",A2contains"3"),true,false). I'm trying to pick out the titles of people in an organization out of a giant list of all employees; there are like 12 different kinds of vice presidents, so I'd like to grab everyone who has "vice president" anywhere in their title. edit: figured it out! I used code:khazar sansculotte fucked around with this message at 05:05 on Nov 5, 2012 |
|
#
?
Nov 5, 2012 04:30
|
|
|
Ronald McReagan posted:edit: figured it out! I used Excellent! Just in case it matters for your use there is also Find() which has the same syntax as Search() but is case sensitive.
|
|
#
?
Nov 5, 2012 15:21
|
|
|
Ronald McReagan posted:I probably should have been more specific; I'm looking for something more like =if(or(A1contains"1",A2contains"2",A2contains"3"),true,false). I'm trying to pick out the titles of people in an organization out of a giant list of all employees; there are like 12 different kinds of vice presidents, so I'd like to grab everyone who has "vice president" anywhere in their title. This is a more general thing that I use sometimes, but when I want to find a lot of things and parse them out quickly, I use Find+Replace. Put in the term you want to find, or enough to catch it with a search, in your case maybe "vice". Then put the exact same term in the replace field, but go to more options and Format, and change the cell background to a different color. That will go through all cells and highlight cells which contain your doubled term, then you can filter or sort by color pretty quickly. Whatever algorithm is powering the Find+Replace is like 10x as fast as most looping VBA you can throw together, so for large data sets with simple finding conditions, I've found this to be pretty efficient.
|
|
#
?
Nov 5, 2012 18:16
|
|
|
Old James posted:I don't think you can do this. VBA will alter the entire contents of the cell and not just the first character. You can. Each Range object holds a Characters collection, and each Character has its own Font property. For example, Range("A1").Characters(1,1).Font.Subscript = True will make the first character in range A1 a subscript. How do you want to use this, though? Is it always the same character, or easily computable (like the first or last character, or all numbers but not letters, etc...)? That should be easy. If you want to manually highlight a character and have it change, I'm not sure there is an easy way to find out which character has been highlighted. edit: actually, you can't run a macro while in "Edit" mode (for dumb design reasons), so there's no way to select some characters in a cell and use a keyboard shortcut to format them. ShimaTetsuo fucked around with this message at 01:32 on Nov 7, 2012 |
|
#
?
Nov 6, 2012 13:54
|
|
|
Hey folks, I'm having issues combining rows of data based on a common cell. I have a massive 4000 row spreadsheet that looks like the first sheet on the following spreadsheet. I'm hoping to make it look like the second sheet. https://docs.google.com/spreadsheet/ccc?key=0Am5EbI_l2pbcdDN5TjJlN2pxZlNsZVIwZHJuLU1FcEE Basically I want the data to combine based on the "name" column. When they're merged, they need to separate the cells based on a comma. Any ideas? I'm using the latest version of Excel for Mac.
|
|
#
?
Nov 13, 2012 00:15
|
|
|
mattdev posted:Hey folks, I'm having issues combining rows of data based on a common cell. I have a massive 4000 row spreadsheet that looks like the first sheet on the following spreadsheet. I'm hoping to make it look like the second sheet. I think there definitely is a way to do this with VBA (I can't think of one with regular formulas but that's not my strong point really), but I'd like to clarify a few things before I write a macro for it. Are there only two columns, Entry A and B? Second, is this a thing you're going to need to do a bunch, or do you just need to extract some data/values from this one sheet?
|
|
#
?
Nov 13, 2012 00:24
|
|
|
There's probably a clever way to do it, but I would do this- Sort by Column A, so cells that should be merged are all next to each other. Add a column D and column E for your desired output columns. Duplicate B2 and C2 in D2 and E2. In column D, starting from D3, use this formula and fill down: =IF(A3=A2, D2&","&TRIM(B3), TRIM(B3)) In column E, starting from E3, use this formula and fill down: =IF(A3=A2, E2&","&TRIM(C3), TRIM(C3)) This will create your desired final output in the last row for each group with the same Name field. Then you just have to filter those out. You can do that with this formula in Column F and filling down, starting from F2: =IF(A2=A3,"No","Yes") This will put "Yes" next to the last value (with your desired outputs). Then you can just filter by Yes in that column, and you'll have what you need. Edit: If you do this you'll end up with some extra commas, just do Find & Replace for ",," with "," a few times. You might have to trim some leading or trailing commas as well. You can adjust the IF to check for LEN(Cell)>0 before the concatenate if you want. ZerodotJander fucked around with this message at 00:40 on Nov 13, 2012 |
|
#
?
Nov 13, 2012 00:34
|
|
|
DukAmok posted:I think there definitely is a way to do this with VBA (I can't think of one with regular formulas but that's not my strong point really), but I'd like to clarify a few things before I write a macro for it. Are there only two columns, Entry A and B? Second, is this a thing you're going to need to do a bunch, or do you just need to extract some data/values from this one sheet? 37 columns, 5428 rows. I'm just doing this once in order to dump the data into the software we use. Edit: woops, didn't see ZerodotJander's post. Let me give this a shot.
|
|
#
?
Nov 13, 2012 01:01
|
|
|
ZerodotJander posted:There's probably a clever way to do it, but I would do this- I'm pretty time-rich so I made this sample of what kind of steps would be required (some may be able to be merged, creating giant godzilla functions as a result). https://dl.dropbox.com/u/28245256/awful.xlsx Sections are colored as: Green: source data Pink: unique names Orange: row of B column items for each person. Note: these are array formulas (ctrl+shift+enter) not regular formulas, so they don't work on Google Docs and maybe not in non-Excel spreadsheet software. Yellow: row of unique B column items for each person without 0 values (could be combined with the orange formula, but holy lord that would have made it about 1000 characters long) Purple: concatenate horizontal row with commas, preventing leading and trailing commas (first column has a slightly differing formula to the rest). Blue: finished. For this to work on real-world data the orange, yellow and purple sections would need to have an arbitrarily large number of columns (enough to cover the maximum number of times a single name appears in Column A of DATA). You'd probably want to flip columns/rows as well, since actually cloning formulas properly the way I did it is tedious. Unless you have a pressing reason to do it in a formulaic way (say, repeating it often) you're much better off just brute-forcing it. (note: spreadsheet above only deals with the first column of data, has to be repeated for the second.) edit; nghhhh cell F1 is saved as a non-array formula, so for that cell to work you'll need to select the formula (as if editing it) and hit CTRL+SHIFT+ENTER. (source data is empty for that cell anyway, but I am OCD though not enough to actually fix the document). The Gripper fucked around with this message at 17:22 on Nov 13, 2012 |
|
#
?
Nov 13, 2012 14:30
|
|
|
This has been driving me pretty crazy. I'm trying to use SQL queries within Excel that have values passed as parameters. This is totally fine and works dandy when the SQL statement is simple enough that the ancient "Microsoft Query Editor" can, as it says, display it graphically. The only way to add paramters to a query is through MS Query Editor, and it won't allow parameters if the query is too complicated/can't be displayed graphically. It also can't display stored procedures graphically, so that isn't an option either. You can copy/paste an existing simple, working query with paramters and try to change the query to a more complicated one, but it throws up errors like "[microsoft][odbc sql server driver]invalid parameter number". One resource online says that you can edit the query using the "Microsoft Script Editor" (which is different from the regular VBA editor), but MS took it out of Office 2010 for reasons unknown so that isn't an option either. Any advice? Is there some plugin out there to make my life easier?
|
|
#
?
Nov 13, 2012 18:58
|
|
|
hayden. posted:Any advice? Is there some plugin out there to make my life easier? I've added parameters to queries too complex for the Query Editor before. The path was kinda unpleasant - I saved the spreadsheet I was working on to the 2003-era SpreadsheetML format, which has very scant documentation. You can then delve into the markup for the querytable and give it the parameters you need. The easiest thing to do is create two basic example spreadsheets to use as reference, one with a simple parametrised query and one with a complex query and no parameters, then combine them with a bit of trial and error. Once you've got a SpreadsheetML version working, excel will happily convert it back into normal formats without it breaking - she lacks the interface to create them but not the capacity to run 'em. Just hope and pray you never need to modify it again... I also recall a much worse trick where the query I wanted used several named subqueries, and the parameters for the query were buried in the innermost query, so you couldn't expose them without combining the whole mess into a single monster SQL statement. The way round that was to use a sql statement like: code:
|
|
#
?
Nov 14, 2012 00:35
|
|
|
Heavy_D posted:You can then delve into the markup for the querytable and give it the parameters you need. What method/tool do you use to do that? Thanks for the response, by the way! Very helpful.
|
|
#
?
Nov 14, 2012 02:28
|
|
|
hayden. posted:What method/tool do you use to do that?
|
|
#
?
Nov 14, 2012 11:45
|
|
|
Hi All, I need guidance with a function that will check domains on an email distribution list vs an approved list of domains? This is for my a report build/distributor sheet I'm making for my Dad and this email check is the only step I can't do. E.g. Each row is a client C2 has the distribution list 'joe@aaa.com; jane@bbb.com; john@bbb,com' and C3 has the approved domains for the client '@aaa.com; @bbb.com'. I would like the sheet to know if a non approved domain has been used (e.g. jack@ccc.com), notify the user and have them confirm the entry. I've looked online and can't see where to begin and my other two trusty vba friends aren't able to help either. Any tips would be appreciated.
|
|
#
?
Nov 14, 2012 23:30
|
|
|
I am but an amateur, but let me ponder until the big guns show up. I'm not totally understanding how the emails and domains are organized, but I'm guessing that you'll be checking the domains either a vlookup or some looping through a range. If you just want to check stuff after the @, um I think it's some sort of use of right(thisCell,Instr(thisCell,@)) or something like that. Others can clarify Im sure.
|
|
#
?
Nov 15, 2012 10:42
|
|
|
PokerJoe posted:I've looked online and can't see where to begin and my other two trusty vba friends aren't able to help either. Visual Basic .NET code:Shouldn't be too hard to use that to prompt the user to approve an address if necessary (after the if InArray(current_domain, a) = False Then line in Dongs(), removing the Exit For so you'll be prompted for each failure and not just the first.) I'm pretty rusty at VBA, so apologies for the varying usages of Dim and things.
|
|
#
?
Nov 15, 2012 10:53
|
|
|
Hi Gripper, Thanks for the above. I don't 100& follow it but see enough to get me going I think. Thanks again for taking time to look at it.
|
|
#
?
Nov 16, 2012 02:39
|
|
|
I was going to comment it but I couldn't remember what character was used to denote comments in VBA, but the general gist of it is: - Function Dongs(d,a) takes the values of 2 cells: the distribution list separated by semicolons, and the approved domains list also separated by semicolons. - It splits both by the delimiter ";", so you get two arrays containing each individual email/domain. - Loops through each address from the distribution array: - Splits it to get the domain (and then adds a leading @ as the split removes it) - Checks if it is in the array of approved domains using InArray. As soon as one email from the distribution list fails the test, it returns FALSE.
|
|
#
?
Nov 16, 2012 02:49
|
|
|
Hi Gripper, Want to say thank you so much for the help. It works a treat and seriously helps with the control's in place.
|
|
#
?
Nov 20, 2012 01:19
|
|
|
I have a large set of data values, and I need to count the frequency of a set of equally spaced subintervals (chi-squared test for independence of random variables for any statisticians in the thread). So for a bunch of decimals between 0 and 1, I need to count how many are less than 0.2, how many between 0.2 and 0.4, between 0.4 and 0.6, etc. There must be a very simple way of doing this but it's late and I'm tired and I can't think of it, can someone help? edit: =COUNTIF(A1:A100;<0.2) and =FREQUENCY(A1:A100;<0.2) are the sort of thing I need to use, but it's not happy with the "<0.2" part. (btw I'm using OpenOfficeCalc not Excel.) Hoops fucked around with this message at 23:57 on Nov 21, 2012 |
|
#
?
Nov 21, 2012 23:52
|
|
|
The "<0.2" part actually needs to be in quotation marks-at least on Excel
|
|
#
?
Nov 22, 2012 00:05
|
|
|
Ragingsheep posted:The "<0.2" part actually needs to be in quotation marks-at least on Excel Hoops fucked around with this message at 00:10 on Nov 22, 2012 |
|
#
?
Nov 22, 2012 00:08
|
|
|
Hoops posted:I got it to work by doing =FREQUENCY(C$8:C$32;E7:E11), where the C column is my data and the E column is my intervals, but only by subtracting all the columns above it. How can I make it only count the values between say, 0.6 and 0.8 in one function? =countif(A1:A1000,"<0.8")- countif(A1:A1000,"<0.6")
|
|
#
?
Nov 22, 2012 00:11
|
|
|
|
| # ? May 23, 2024 15:11 |
|
|
Ragingsheep posted:=countif(A1:A1000,"<0.8")- countif(A1:A1000,"<0.6")
|
|
#
?
Nov 22, 2012 00:22
|
|