|
TheEffect posted:I have two Excel documents that I need to combine. One has multiple sheets and 3 columns in each. The second one has the same three columns, one sheet only. I need something that will look at the first column in document B and search for a match in document A, and when it finds a match it will copy the next two columns associated with that row to the other workbook. Do you need to do this one time, or do you need like a button that does this regularly? If one time, vlookup() works across workbooks, and offset(match()) also does this but is more flexible about the order of your criteria (and is also faster). If you're matching on multiple criteria, these will work too but you'll need an array formula. These are common tools and google will provide lots of examples so you can nail down an implementation that meets your requirements.
|
#
?
Feb 19, 2015 17:49
|
|
|
|
| # ? Apr 26, 2024 06:25 |
|
|
Thanks! I'll start poking around but if I can't figure it out I might just buy a license for that plugin.
|
|
#
?
Feb 20, 2015 16:02
|
|
|
I'm dragging some tables from a page over to Excel (2013) it works fine except with dates, namely, anything in September This is how the date column looks like, September automatically gets formatted.  When I change the format to text, the September values get converted somehow  Why is this happening?
|
|
#
?
Feb 20, 2015 17:23
|
|
|
Most of those dates are stored as text, your September dates were stored as actual dates. Actual dates are stored as a number, which is how many days it's been since 1/1/1900 (or 1/1/1904 if you have that option checked). It gets displayed as a date because you format the cell as a date. When you format the cell as text, it just shows the number. You'll just need to retype those September dates to make them text (or convert all of your other ones to actual dates, depending on your needs).
|
|
#
?
Feb 20, 2015 18:44
|
|
|
You may also have success using the following process to mass convert your strings to numbers. Enter 1 on a random cell and copy it. Select cells containing strings you want to convert Right click selection and click Paste Special... Select Paste > Values and Operation > Multiply radio buttons Click OK If retyping your date would cause Excel to think it's an actual date instead of text/string, the above should work.
|
|
#
?
Feb 20, 2015 18:56
|
|
|
Whoops, I should've mentioned that the "page" I'm getting the data from is a web page and in the html, all dates are just a string in a table cell. It's only when I've selected the whole thing and drag it over to the excel worksheet that September gets set as a date type while all the other ones are not. So far I've just manually re-written the September values since the date column can just as well be simple text like it is on the web page. I just found it odd that it only happens to September and none of the other months.
|
|
#
?
Feb 20, 2015 20:23
|
|
|
Excel date parsing is complicated and locale dependent. Are you in a locale where the 9th month is called "september" but the other months differ from their english names? The worst is when a date string is ambiguous and Excel insists on converting to a date anyway, sometimes by checking the current date (so it won't even parse the same in the future...).
|
|
#
?
Feb 20, 2015 20:45
|
|
|
ShimaTetsuo posted:Excel date parsing is complicated and locale dependent. Are you in a locale where the 9th month is called "september" but the other months differ from their english names? Ah, that's definitely it.
|
|
#
?
Feb 20, 2015 21:40
|
|
|
I'm planning my wedding and currently have all of my guests' addresses stored in an Excel sheet. Is there a way I can arrange these names into cells to print on a 3x10 grid? What I'm hoping to do is to print the names onto a sheet or two of labels to save a little time in writing envelopes (the labels come in sheets of 10 rows and 3 columns), we have a Word template at work to print labels for sample storage that's kind of what I'm going for, but the cells contents there are linked together for sequential numbering, where I want each spot on the grid to have a unique entry, which is why I'm trying to figure out how to get it in Excel.
C-Euro fucked around with this message at 03:35 on Mar 2, 2015 |
|
#
?
Mar 2, 2015 03:24
|
|
|
Like this?
|
|
#
?
Mar 2, 2015 03:58
|
|
|
I'm trying to make up a worksheet for a job I'm working on. I've got 3 worksheets, None of the below quantities are the actual stuff, I've simplified the numbers. The first is Units, which has quantities, small example: code:code:The third is where I'm trying to do the calculations. I need to be able to price up Unit A with price group 1, group 2 and group 3, Unit B with the same, C with the same. I've got a poo poo ton of calculations to do, so what I was hoping to do would be to create a function that pretty much allows me to say =Pricing(2,1) where 2 is the row that the unit I want to do is, and 1 is the pricing group I want to multiply it by. Which would plug the 2 rows into the formula: =(('Unit'!B2*Pricing!B1)+('Unit'!C2*Pricing!C1)+('Unit'!D2*Pricing!D1)) I just need to figure out how to feed excel the variables, but I can't figure it out for the life of me. I've got 10 units to price, with well over 30 things in each one to do, on top of 8 different pricing options, so if I can figure out how to get this to work, it will save me a lot of cutting, pasting and editing to make the stuff correct. I know I'm missing something easy, but I can't figure out even how to phrase the question to find it in google. Edit: Offset still requires me to do the calculation for each time. I also tried Indirect, with no luck there either. AFewBricksShy fucked around with this message at 00:05 on Mar 15, 2015 |
|
#
?
Mar 14, 2015 23:46
|
|
|
Check out the offset function.
|
|
#
?
Mar 14, 2015 23:59
|
|
|
Use array formulas: =sum('Unit'!B2:D2*'Pricing'!B1:D1) Instead of pressing enter to put in the formula press ctrl+shift+enter. You'll know you did it right because the formula will display with curly brackets around it. Edit: Actually, I think this is what SUMPRODUCT is for but I'm not that familiar with it so maybe look into that.
|
|
#
?
Mar 15, 2015 00:05
|
|
|
Isurion posted:Use array formulas: Sadly, my true formulas need to be way more complicated than the easy example. Not all of the quantities on the unit sheet correspond directly to a price on the pricing sheet. I also need to include other variables that don't change from sheet to sheet, as well as calculating sales tax and labor. If I can just type the absurdly huge formula I need to type once into it's own function, it will make it much easier. In reality, my formulas would be closer to the following: =((('Unit'!B2*Pricing!B1)+('Unit'!C2*Pricing!C1)+('Unit'!D2*Pricing!D1)+('Unit'!B2*b2)+('Unit'!B2*b3))x1.08)+('Unit'!E2*b4)) Edit: VV Exactly, but I can't figure out how to make it work. I can make the giant formula no problem, but I can't figure out how to make vb recognize the variables. AFewBricksShy fucked around with this message at 00:21 on Mar 15, 2015 |
|
#
?
Mar 15, 2015 00:10
|
|
|
AFewBricksShy posted:Sadly, my true formulas need to be way more complicated than the easy example. Not all of the quantities on the unit sheet correspond directly to a price on the pricing sheet. I also need to include other variables that don't change from sheet to sheet, as well as calculating sales tax and labor. If I can just type the absurdly huge formula I need to type once into it's own function, it will make it much easier. Sounds like a good case for a VBA udf
|
|
#
?
Mar 15, 2015 00:15
|
|
|
I'm just going to use the indirect function and copy and paste it a bunch. Should work okay. It's not pretty but it will do.
|
|
#
?
Mar 15, 2015 03:08
|
|
|
We are starting cycle time tracking on samples at work, and currently housing the information in Excel spreadsheets. Right now we're logging sample IDs, the date and time we receive them, and the date & time we release them in our inventory (usually same day or next day). Currently someone has to go into the sheet at the end of each day and manually calculate and record the cycle time for each sample, which we report in hours (i.e. an hour and a half is reported as 1.5). There has to be a way to have Excel auto-calculate this, right? Even with the fact that samples often come in on a given day but don't get released until the next morning?
|
|
#
?
Mar 25, 2015 15:24
|
|
|
Just have column that subtracts the in time from the out time and format the cell to display only hours. Edit: Ctrl+; and Ctrl+Shift+; with a space in between will auto add the current day+time as a fixed value, which should save time if you are having people manually type in the time when they have an order updated. For formatting it would be under custom, [h]:mm. That would report in total hours with minutes. If you want fractional hours you'll have to do math using the hour and minutes functions, something like: code:TheLastManStanding fucked around with this message at 21:49 on Mar 25, 2015 |
|
#
?
Mar 25, 2015 21:32
|
|
|
What about something where a sample comes in at 11 AM today but gets released at 10 AM the following day? Is there a way to factor in a date from a different cell? I think I figured out how to get it if you have the data & time in the same cell but I'd like to be able to keep our current sheet format if possible.
|
|
#
?
Mar 25, 2015 22:51
|
|
|
What do you want the result to be in that case? It may be helpful to share a sample of your sheet, or at least a screenshot.
|
|
#
?
Mar 25, 2015 23:02
|
|
|
If you have start date in column A, start time in column B, end date in column C and end time in column D, then ((C1+D1)-(A1+B1))*24 is the time difference between them in hours, regardless of which day they occur on. You can round it to whatever you need after that (nearest half hour?). It's because dates are stored as integers and times are decimals between 0 and 1, so a date + a time is the corresponding date-time, and differences of those give you day fractions.
|
|
#
?
Mar 26, 2015 00:22
|
|
|
ShimaTetsuo posted:If you have start date in column A, start time in column B, end date in column C and end time in column D, then ((C1+D1)-(A1+B1))*24 is the time difference between them in hours, regardless of which day they occur on. You can round it to whatever you need after that (nearest half hour?). That is super-helpful to know, also I found the MROUND function that lets you round to the nearest user-defined multiple so I think I've got exactly what I was looking for. Thanks!
|
|
#
?
Mar 26, 2015 02:20
|
|
|
I'm sorry I dont know a better place for this. I'm trying to parse the following XML with VBA. code:
|
|
#
?
May 1, 2015 11:11
|
|
|
Swink posted:I'm sorry I dont know a better place for this. Well it's possible obviously but, easier than what? Using MSXML is pretty easy and it will be much more flexible when your requirements change (e.g. what if there's more than one "url" tag and you need to decide which one to get, or you only want it if status is ok, or whatever). Just add a reference to "Microsoft XML, v6.0" (or whatever version you have), and then: code:
|
|
#
?
May 1, 2015 16:01
|
|
|
Thansk for taking a look and I should have mentioned in my first post, the reason I'm finding it impossible to parse correctly. Your code, like all of mine, throws "Object Variable or With block variable not set" on the last line - GetUrlValue = doc.DocumentElement.SelectSingleNode("data/url").Text It's making me think my XML is broken or otherwise weirdly delivered. If you have any more suggestions, thats great, but in the interests of just getting this thing written I'm going to dump it all to a text file and search it out.
|
|
#
?
May 3, 2015 11:37
|
|
|
I'm back! This time I have a spreadsheet with a bunch of details regarding boxes of documents we're storing. I'm trying to add a column where I mark things are keep-able, shred-able, and stuff that needs review- The "Clean-up Disposition" column is the one I added, however I want to make it a column that I can sort by entries like the other columns (notice the arrows for drop-down menus). I have no idea how to do that though, help me out please?
|
|
#
?
May 8, 2015 04:42
|
|
|
It's not part of the table. If you click something in the table there should be an outline of the whole table which you should be able to adjust. Otherwise just select all your data and table it again. Once it is part of the same table you can toggle the drop down menus on and off by clicking the filter button in the data tab.
|
|
#
?
May 8, 2015 05:33
|
|
|
TheLastManStanding posted:just select all your data and table it again. Once it is part of the same table you can toggle the drop down menus on and off by clicking the filter button in the data tab. Do that. It's not in a table, just a filter applied to those rows. Click somewhere in the data, Ctrl+A to select it all, Insert tab, Table.
|
|
#
?
May 8, 2015 13:00
|
|
|
Or select columns A:whatever by dragging across the column labels at the top, go to the 'data' tab and press the 'filter' button that has the funnel icon. That will add the dropdown filter arrows to the first row.
|
|
#
?
May 12, 2015 06:48
|
|
|
How would I go about getting Excel to ignore the empty cell entries in a multi-level category bar chart, such that there aren't any big awkward gaps between the 'Old' and 'New' columns and they're centered appropriately. None of the settings hiding cells under 'Select Data' appear to work for me.
|
|
#
?
May 31, 2015 09:09
|
|
|
Poizen Jam posted:How would I go about getting Excel to ignore the empty cell entries in a multi-level category bar chart, such that there aren't any big awkward gaps between the 'Old' and 'New' columns and they're centered appropriately. I usually have to have separate data sections for plots and tables. Often on different tabs. What looks god in a data table never seems to be the format that works in graphing. Sometimes excel prefers na() to "" for blank data.
|
|
#
?
Jun 1, 2015 02:39
|
|
|
I have a list of "items" in a master sheet. Then a zillion sheets that track different things to do with these items. Is it possible for me to set up the "zillion other" sheets so that when I add a new item to the master sheet, a new row is created in the same spot, the new item copied in, and all the existing data belonging to each item shifted down? Right now I have to go manually update all my other sheets when adding new items. For Example: Inserting a new row into Sheet 1 and adding "Item BB", should create a new row in Sheet 2 and shift everything down as shown.  edit: Oh you can group sheets together and do things like insert rows. That's easy. I was expecting to need to use macros and all sorts of stupid things. Kreez fucked around with this message at 01:13 on Jun 2, 2015 |
|
#
?
Jun 1, 2015 22:23
|
|
|
I have a huge spreadsheet that for some reason is broken up into groups of 25 lines of data, then 2 blank lines, 25 more, ect. Possibility for printing. When a new line is added this causes things to spill over into the next group of 25, ect until every group of 25 down the line needs to be changed. The people here have been doing the shift manually for all 5000 lines or 200 groups of 25. Surely there must be some way to add some automation to this. edit: Playing around with it, I think this is the right direction to head. I know nothing at all about VBA so the syntax is going to be a slog code:Xenoborg fucked around with this message at 22:00 on Jun 3, 2015 |
|
#
?
Jun 3, 2015 21:27
|
|
|
Xenoborg posted:I have a huge spreadsheet that for some reason is broken up into groups of 25 lines of data, then 2 blank lines, 25 more, ect. Possibility for printing. When a new line is added this causes things to spill over into the next group of 25, ect until every group of 25 down the line needs to be changed. The people here have been doing the shift manually for all 5000 lines or 200 groups of 25. Surely there must be some way to add some automation to this. I would find out what those two lines are for, in case they're not actually really useful. When you say "for printing", are you saying that, by doing this, page breaks end up exactly where you need them, so you get 25 lines per page? Seems like it would be way easier to have your data in contiguous rows, but set the page breaks to every 25 lines (I don't think that's built-in, would need to use VBA). You could probably have the VBA page break-realignment code run in the Workbook.BeforePrint event so that it gets fixed up whenever you print. In any case, if you must absolutely have empty lines in there, maybe it's easier to "store" the data contiguously in one sheet, but "visualize" the data with extra breaks on demand (in another book)? It would also remove the issue of users adding lines but forgetting to run the code, what happens when lines are deleted, etc.
|
|
#
?
Jun 4, 2015 00:31
|
|
|
Long story short, a government spec wants them there. The lines aren't actually blank, they just don't have the 5000 or so data lines on them. They have header/footer type stuff on them, but also some formulas based on the data in the 25 lines. Your idea about keeping the data in one single sheet and having it feed into another format to print is something I will look into.
Xenoborg fucked around with this message at 00:40 on Jun 4, 2015 |
|
#
?
Jun 4, 2015 00:36
|
|
|
Page breaks by rows are built in (check Views, Print View). You should be able to accomplish the formatted page without vba. A combination of OFFSET, IF, MOD and ROW functions should do it. edit: On the data page, have an index column of =ROW(A1)+(FLOOR((ROW(A1)-1)/25,1)*2) in A1, etc. On your print page, match everything with =IFERROR(OFFSET(DataPage!$B$1,MATCH(ROW(A1),DataPage!$A:$A,0)-1,0),"footer stuff") in A1, and change the offset column from 0 to 1, etc as you work across your fields. You will need to adjust a bit if you have headers, etc. fosborb fucked around with this message at 15:37 on Jun 4, 2015 |
|
#
?
Jun 4, 2015 13:43
|
|
|
I have a bunch of excel macros that takes text from outlook, parses it and spits it into a excel workbook. I wasn't the one who put this together, so i don't know enough to debug it. when i'm attempting to run it, it gives me a runtime error(438, object doesn't support property/method), and debug points me to the bolded line below Sub CallEmailForwardProcedurefromOutlook() Set myOlApp = CreateObject("Outlook.Application") myOlApp.SaveAllEmails_ProcessAllSubFolders ' Save a copy of e-mail to the local folder End Sub e: this appears to be the one giving issues, this is being run using outlook 2007 and excel 2007 for reference. Elizabethan Error fucked around with this message at 18:29 on Jun 5, 2015 |
|
#
?
Jun 5, 2015 18:26
|
|
|
I've never used the Outlook object model but there doesn't seem to be a "SaveAllEmails_ProcessAllSubFolders" member in the Outlook.Application class, as your error message mentions. Do you have, anywhere in your code, a subroutine called "SaveAllEmails_ProcessAllSubFolders"?
|
|
#
?
Jun 5, 2015 21:49
|
|
|
ShimaTetsuo posted:I've never used the Outlook object model but there doesn't seem to be a "SaveAllEmails_ProcessAllSubFolders" member in the Outlook.Application class, as your error message mentions. here's the code I readded: http://privatepaste.com/e965888335
|
|
#
?
Jun 5, 2015 22:23
|
|
|
|
| # ? Apr 26, 2024 06:25 |
|
|
In your original code, references to the Outlook namespace were late-bound (with CreateObject("Outlook.Application")). In your new code you also have some early-bound references (the first one that gave me an error was "Fld As MAPIFolder" in the declaration of "GetFolder"). When you have early-bound references you must add the library reference manually: in the "Tools" menu, under "References...", you want to include "Microsoft Outlook 12.0 Object Library".
|
|
#
?
Jun 6, 2015 01:09
|
|