|
Thanks to everyone for all the ideas! I'll be working on this next week, I'll post again if anything interesting happens.FAN OF NICKELBACK posted:I didn't get to your $0 thing because I wasn't sure exactly what you meant. Basically, not all departments use all accounts. If a department uses accounts 1000 and 3000, but not 2000, then on their report, rather than code:
code:
|
# ? Jul 12, 2014 16:59 |
|
|
# ? May 27, 2024 02:20 |
|
Okay, after some tinkering I found a lot more problems with the formatting of my exported Excel files (multiple rows in the pdf being exported to the same row in the excel file, etc.). BUT, I've discovered that exporting from a PDF to a Word file, and then copy/pasting the contents of the Word file into Excel, produces a much cleaner result than just exporting straight to an Excel file. I have no idea why this is the case, but whatever. Now the problem is quickly converting 700 Word documents to Excel files. I'm expecting to make a macro in a blank word document (in the same folder as all my to-be-converted files) that does something like this: code:
|
# ? Jul 14, 2014 18:37 |
|
Use a 3rd workbook\word doc to control the other two. Then you can loop through every file in a directory, export pdf to word, find the open word document, convert to xls, close the word document, close and save the xls, repeat. Should make life easier for you.
|
# ? Jul 14, 2014 18:41 |
|
Okay. It's I've got a logistical problem to work out. I have five workbooks. Lets call one of them MASTER workbook. The others are 1-4. MASTER workbook has 17,296 rows, and three columns for Email Address, First Name, Last Name. Workbooks 1-4 have varying rows, and three columns for Email Address, First Name, Last Name. I need to take any row from 1-4 which exists in MASTER workbook and remove them from MASTER workbook. I can consolidate workbooks 1-4 into one workbook to make the comparison easier. EDIT - I think I've got it, but I'd appreciate someone telling me I'm right. I merged Workbooks 1-4 into a single file, and then pasted the contents in columns D, E, F of the MASTER workbook. The result was: code:
Then I inserted a column in the middle (which is why I reference column G) and used this formula: =NOT(ISNA(VLOOKUP(C1,$G:$G,1,FALSE))) That returned "True" for a bunch of them, and spot checks indicate they were in both columns. I deleted the TRUE rows. This should do it, right? me your dad fucked around with this message at 22:17 on Jul 18, 2014 |
# ? Jul 18, 2014 21:07 |
|
Should work just done delete both of them at tge same time. Im also sure excel has some kind of remove duplicates function somewhere.
|
# ? Jul 19, 2014 16:59 |
|
Hmm, this has got me stumped. I have a list of event attendees with two columns that I'm trying to sort in a useful way. The columns are: year, and last name. The issue is that not all the rows have a value in the 'year' column (i.e. people who are attending as guest of their spouse). I want to sort by year, and then by last name, in order to produce something like this: code:
|
# ? Aug 5, 2014 10:07 |
|
El Grillo posted:Hmm, this has got me stumped. I have a list of event attendees with two columns that I'm trying to sort in a useful way. The columns are: year, and last name. I suggest a third column "Sort year" that fills in the missing data with an if statement. You can later hide this column.
|
# ? Aug 6, 2014 04:08 |
|
I am trying to compile a report to pull numbers for people I manage and I've come across a scenario where my sheet is double counting certain instances of things and I can't figure out how to filter out duplicates. Example date:code:
# Submitted by A # Submitted by B # Withdrawn My issue is with the withdrawn status - I have a relatively simple COUNTIF counting "Person A" under the "Submitter" column, however of course I don't want the 3rd issue that was withdrawn counted. Is there an easy way to exclude it? I was trying to use =COUNTIFS(C:C,"Person A",D:D,????) Maybe a Match statement nested in an IF nested inside the ??? That seems way more complicated than it should be considering if I have 5 different people I would need a different match to account for each person, so that sucks. There's probably something ludicrously simple I am overlooking here.
|
# ? Aug 8, 2014 20:24 |
|
=COUNTIFS(C:C,"Person A",D:D,"<>Withdrawn")
|
# ? Aug 8, 2014 20:33 |
|
See, I knew it was something stupid. Thanks a ton.
|
# ? Aug 8, 2014 20:38 |
|
Hey everyone. I need a simple formula that a very unskilled excel user can copy/paste into and run easily. Here's how it works: A query runs that pulls everyone from specific stores with a store code, and their salary. How could you run a formula that would pull the TOP 3 highest paid person from each store, while keeping it functionally simple Veskit fucked around with this message at 18:51 on Aug 12, 2014 |
# ? Aug 12, 2014 17:23 |
|
Veskit posted:Hey everyone. I need a simple formula that a very unskilled excel user can copy/paste into and run easily. Here's how it works: =LARGE([Range],3) will return the 3rd largest value in the specified range. But you will need to sort the ranges by each Store Location Code for the following to work. CELL E2: =large(offset($B$1,match(1,$B:$B,0),0,countifs($B:$B,1),1),3) CELL E3: =large(offset($B$1,match(2,$B:$B,0),0,countifs($B:$B,2),1),3) Then add the following formula next to each row of the salary (example for cell D2): =if($C2>=choose($B2,$E$2,$E$3),$A2,"") It won't concatenate a list for you, but will flag the people you want to look at.
|
# ? Aug 12, 2014 20:10 |
|
Old James posted:=LARGE([Range],3) will return the 3rd largest value in the specified range. But you will need to sort the ranges by each Store Location Code for the following to work. You're the best Old James
|
# ? Aug 12, 2014 20:20 |
|
I'm still working here and there on the project I posted about at the top of this page. I've converted the pdfs to docx files (full of tables) and successfully written Word VBA code to strip 98% of the garbage out of them. Now I want to convert them to xlsx files and I'm just having a terrible time with it for some reason. Here is what I have:code:
|
# ? Aug 15, 2014 23:56 |
|
Ronald McReagan posted:I'm still working here and there on the project I posted about at the top of this page. I've converted the pdfs to docx files (full of tables) and successfully written Word VBA code to strip 98% of the garbage out of them. Now I want to convert them to xlsx files and I'm just having a terrible time with it for some reason. Here is what I have: Can you try moving the line "objWord.Visible = True" BEFORE you open the document? Word may be prompting you for some reason or other (document is corrupted, caused an error previously, must be converted somehow, is locked for editing, etc...) but you are not seeing it and it may be waiting for your reply forever.
|
# ? Aug 16, 2014 01:07 |
|
ShimaTetsuo posted:Can you try moving the line "objWord.Visible = True" BEFORE you open the document? Word may be prompting you for some reason or other (document is corrupted, caused an error previously, must be converted somehow, is locked for editing, etc...) but you are not seeing it and it may be waiting for your reply forever. That's exactly what it was, thanks! I managed to get a lot of what I want to do working, but now I'm having an issue getting the code to loop through all the files. I have code that looks like this: code:
edit: nevermind! I solved this myself by changing code:
code:
khazar sansculotte fucked around with this message at 02:39 on Aug 16, 2014 |
# ? Aug 16, 2014 02:36 |
|
Dir returns file names only ("myfile.docx"), not the full path. If you don't supply a full path to Documents.Open, it will probably guess you mean something like your current working directory or something like that.
|
# ? Aug 16, 2014 18:25 |
|
Makes sense, thanks! I've now got nice excel files which more or less look how I want them to look, but on a few of them, due to bugginess in the pdf-->docx conversion process, things which should be on separate lines have been crammed together on the same line. E.g., this: code:
code:
|
# ? Aug 17, 2014 14:23 |
|
nevermind, dumb question with a dumb answer
khazar sansculotte fucked around with this message at 05:47 on Aug 18, 2014 |
# ? Aug 18, 2014 05:05 |
|
I am dealing with some fragments of string in an excel file and I need a command to pull out selected bits of string from a possible 5000 matches. That is, I want to extract some predetermined portions of string based on a long list of possible matches. I can do this easily in other software, but since I am collaborating with a person who uses excel I need to do this within excel. To help people visualize what I am talking about, here's what my current file would look like: code:
code:
code:
|
# ? Aug 18, 2014 07:39 |
joepinetree posted:Any help on that? code:
code:
|
|
# ? Aug 18, 2014 08:53 |
|
Thanks. Seems straightforward enough. I will play around with it and see if my collaborator can also understand this.
|
# ? Aug 18, 2014 21:48 |
|
*Ignore me I am a COMPLETE moron.
A Tartan Tory fucked around with this message at 11:06 on Aug 19, 2014 |
# ? Aug 19, 2014 10:59 |
|
I'm having a bit of trouble with my macro in Excel 2010 that opens another variable .xlsm file. If I just run the macro, I select the file, click enable macros, and it opens the new workbook up in design mode and quits out of the macro. If I use F8 to go step by step, it runs fine. I thought it might have been .DisplayAlerts but I removed that and it's still happening. Any help would be appreciated.code:
|
# ? Aug 29, 2014 19:22 |
|
Im confused about what exactly you are asking here? Which workbook has the security setting problems? What exactly are you trying to do via code that fails due to security/trust centre?
|
# ? Aug 29, 2014 19:35 |
|
Sorry, phone posting. The macro posted is the start of my workbook (destwb) macro. The source my workbook opens also has its own macros, but I don't need them. If I run my macro normally, it quits after the workbook.open line, with this new workbook open, but in design mode, even if I push enable macros when prompted. If I go line by line with step into (F8), it will open the source workbook just fine, and I can run my macro the rest of the way through.
Afterbirth Aftermath fucked around with this message at 20:28 on Aug 29, 2014 |
# ? Aug 29, 2014 19:55 |
|
So basically you want the second wb to open not in design mode? Phone posting here so unable to check. I know trust setting stuff can be a pain. First thoughts are, is the target wb saved in design mode? Try resaving in the way you want it to open. Secondly I would try setting default opening settings of excel (may be required to be in the registry remeber to remove when done). Also if the path of the code isnt returning to your initial wb try having the new wb opening be the end of a sub and have the target call a sub in the master wb in on open or somewhere similar.
|
# ? Aug 29, 2014 20:45 |
|
In reference to the question above I had a couple of weeks ago, I've found a process that leaves me with line feeds within a cell separating two accounts. Basically, if I can properly "split" a row of cells, each with a line feed, into two rows, I'll be golden. I have the following code that takes care of this problem nicely: code:
khazar sansculotte fucked around with this message at 21:41 on Sep 1, 2014 |
# ? Sep 1, 2014 21:38 |
|
Ronald McReagan posted:In reference to the question above I had a couple of weeks ago, I've found a process that leaves me with line feeds within a cell separating two accounts. Basically, if I can properly "split" a row of cells, each with a line feed, into two rows, I'll be golden. Usually you want to avoid modifying a collection (adding/removing elements) while you are iterating over it. It's just confusing, but it's also because the list of elements that will be iterated over may only be evaluated once at the beginning, so even if you add members to the list inside the loop it will only iterate over the list as it was when you entered the loop. For example, if you had a "For i = 1 to N" loop and you modified N on the inside, it would still iterate up to the original N (compare with "While i <= N", where the condition is evaluated again each iteration, so it would go up to whatever N is at that point). I'm not sure how the "for each" works in VBA, however (IIRC in VB.NET modifying the collection in a for each will throw an exception), but that may be to blame. In any case, the key is probably to build up a second list as you go, not modify the original. Something like: 1. Iterate over your range as you are doing. 2. At each cell, use the "Split" function. 3. Add the result (an array with 1, 2, 3,... members depending on how many vbLf's you had) to a NEW list (on a new sheet, or the column next to the original one, whatever). 4. Once you are done, you can replace your starting range with this one.
|
# ? Sep 2, 2014 00:30 |
|
As usual the solution was not nearly so interesting. I fixed it by changingcode:
code:
So the code was producing the behavior I wanted in terms of including inserted cells as part of the "For Each" even after the loop began, I just wasn't telling it the right places to split the strings.
|
# ? Sep 2, 2014 03:30 |
|
Cast_No_Shadow posted:So basically you want the second wb to open not in design mode? Phone posting here so unable to check. I know trust setting stuff can be a pain. First thoughts are, is the target wb saved in design mode? Try resaving in the way you want it to open. Secondly I would try setting default opening settings of excel (may be required to be in the registry remeber to remove when done). Also if the path of the code isnt returning to your initial wb try having the new wb opening be the end of a sub and have the target call a sub in the master wb in on open or somewhere similar. After 4 wonderful days off I came back today to deal with this. Can someone tell me if the following is normal behavior? If I try to run the macro with an assigned hotkey (CTRL+SHIFT+R in this instance), I kept getting the design mode issue I described. When I created a "PUSH ME" button form control, it runs just fine. It works so I'm going with it, so I'm just curious at this point.
|
# ? Sep 3, 2014 18:22 |
|
Is there any easy VBA way to split a cell with a delimiter into multiple cells on the same row, that also pushes any data currently residing immediately to the right of the original cell further to the right instead of overwriting it altogether? That is, turncode:
code:
Edit: I got around this issue by moving cells around in such a way that I only had to do a TextToColumns thing once, which is good enough for my purposes right now. I'm still curious if there's a way to do what I outlined above though. khazar sansculotte fucked around with this message at 00:58 on Sep 4, 2014 |
# ? Sep 3, 2014 23:41 |
|
I would use a combination of the Join function to add ";" between the cells and then the Split function to split it back up again (i.e. both the original delimiters in the cells and the new ones between the cells). Not very efficient but whatever.
|
# ? Sep 4, 2014 01:14 |
|
Ronald McReagan posted:Is there any easy VBA way to split a cell with a delimiter into multiple cells on the same row, that also pushes any data currently residing immediately to the right of the original cell further to the right instead of overwriting it altogether? That is, turn If the text in the cell is literally [cat;dog;ferret] in one cell, and [car] in another, and they always have square brackets and are always semicolon delimited, then I'd: 1) make an array of cells with the equation =MID(A1,2,LEN(A1)-2)&";" for each original cell. This gets it in the format of item1;item2;item3; and you can click and drag that to get that on everything. 2) Choose a column to the right of everything and do =D1&E1&F1&G1.... to join your big semicolon-delimited string together. 3) RTRIM to len(bigcolumn),1 to remove the trailing semicolon (probably not necessary) 4) text to columns it That's the bruteforce way I'd have done it.
|
# ? Sep 4, 2014 11:36 |
|
Hi Excel geniuses. I'm attempting to edit a macro with a dangerously weak understanding of VBA, and don't even know how to begin Google searching this question. The goal is to have a macro that will allow the user to select multiple part numbers (presumably they are all listed in the same column) and run the macro to perform a VLookup on the selection, and insert an item description to the right of the selection. So far everything is good with the formatting and inserting, but when the formula copies in it puts in some parenthesis for reasons I don't understand. The randomly inserted parenthesis break the formula. Here is the specific line that's giving me issues: Selection.Formula = "=VLOOKUP(RC[-1],'F:\Staff\DrKennethNoisewater\Part Numbers\[PartNumberMasterList.xlsx]Sheet1'!A:B,2,0)" When it puts the formula into the selection, it shows up as follows: VLOOKUP(RC[-1],'F:\Staff\DrKennethNoisewater\Part Numbers\[PartNumberMasterList.xlsx]Sheet1'!A:(B),2,FALSE) With the bold portion being the problem. Why does it insist that there should be parenthesis around that B, but leaves everything else alone? Thanks.
|
# ? Sep 4, 2014 22:04 |
|
http://stackoverflow.com/questions/8164867/excel-macro-formula-adding-quotes-around-formular-causing-vlookup-to-not-work TL;DR - You cannot mix RC and A1 style references in the same formula.
|
# ? Sep 4, 2014 22:23 |
|
Wandering Orange posted:http://stackoverflow.com/questions/8164867/excel-macro-formula-adding-quotes-around-formular-causing-vlookup-to-not-work So I guess the right Google search was "excel macro formula adding quotes around formular causing vlookup to not work". Thanks!
|
# ? Sep 4, 2014 23:22 |
|
Yeah I had to refine the search quite a few times before anything close turned up. Sometimes it is just dumb luck!
|
# ? Sep 5, 2014 00:30 |
|
I'm on the home stretch of the thing I've been working on (on and off) for a couple of months. Here is what I hope is the last problem I'll need to solve: Some of my spreadsheets show only an account (e.g., 5440), others show only its subaccounts (e.g., 5441, 5442, 5443, 5444). I want to take the sheets that only show subaccounts, and add up the values of the subaccounts, and then change its account number to the acccount. In other words, I want this: code:
code:
code:
When I step through it, apparently "rowfind" is never becoming anything other than "Nothing." Am I doing something wrong with the Find method? Is the ? wildcard inappropriate (really all I want to find is anything from 5441 up to 5449)? Should I try something totally different? edit: New strategy. I've inserted the number for the main account, sorted the column increasing (so the subaccounts will definitely appear below it). I'm trying to use a Do While loop to add up what I need and delete the rows as I go, but I can't get that working either (again, the "base" variable simply does not seem to be taking a value; I tried xlFormulas instead of xlValues just for the heck of it, but it didn't seem to matter): code:
khazar sansculotte fucked around with this message at 02:25 on Sep 7, 2014 |
# ? Sep 6, 2014 23:40 |
|
|
# ? May 27, 2024 02:20 |
|
With way too little detail to understand the problem fully, could you look at using a combination of SUMPRODUCT, and INDEX/MATCH?
|
# ? Sep 7, 2014 09:05 |