|
Probably a dumb question, but I have no idea what the correct search terms are for the thing I'm trying to do. Is there a way when copying a formula, to have the formula advance by specific multiples of steps per cell, instead of by 1 per cell? For instance, if I copy the formula "=A1" to the cell immediately to the right, I might want it to say "=D1" instead of "=B1" as it would normally behave.
|
#
?
May 28, 2019 07:36
|
|
|
|
| # ? May 13, 2024 11:49 |
|
|
Copy value from every nth column
|
|
#
?
May 28, 2019 07:55
|
|
|
That was quick! Thanks buddy.
|
|
#
?
May 28, 2019 08:45
|
|
|
Can you guys help me figure this macro out? I need to be able to process through this sheet for customer numbers and all of the associated payment values while having it all moved onto a sheet until the process completes. After that I'll import the block to another workbook that will process all of the values against a ledger. I can do that part just fine. I'm bad at scraping data and transposing. Here's the first screen. The '10361' is the customer number, the values highlighted below are all costs associated with this customers statement for the month.  This is what I'd like it to look like on the next sheet  Thanks so much for any help, even if it's just linking a tutorial. I might just not know how to search for this to figure it out on my own =[
|
|
|
#
?
Jun 7, 2019 21:01
|
|
|
I just used 1/CountIf(range with many duplicate entries, this entry) and summed that column in a PivotTable to find how many unique entries there were. This has bugged me constantly.
|
|
#
?
Jun 7, 2019 21:33
|
|
|
nickhimself posted:Here's the first screen. The '10361' is the customer number, the values highlighted below are all costs associated with this customers statement for the month.
|
|
#
?
Jun 7, 2019 22:30
|
|
|
I've been learning vba during my free time at work over the last year to automate some tasks and now a few other teams learned that I know vba so now I'm just coding vba all day, with the goal of automating the formatting of about 20 different workbooks. The formatting used to take a few hours to do, and now it takes minutes at most. I should ask for some sort of raise right? My biggest concern is that if any of them been I'm the only one that would be able to fix it. Job security I guess.
|
|
#
?
Jun 8, 2019 02:48
|
|
TheLastManStanding posted:Convert it to a table. Filter column A by "Customer #", copy out column B. Filter column A by "Total Due:", copy out columns B:G. Jesus. The simplest solutions are often the most overlooked. Thank you!!
|
|
|
#
?
Jun 10, 2019 14:43
|
|
|
Harminoff posted:I've been learning vba during my free time at work over the last year to automate some tasks and now a few other teams learned that I know vba so now I'm just coding vba all day, with the goal of automating the formatting of about 20 different workbooks. This is going to sound like a jerk-rear end thing to say but sure ask for a raise. But also look to either get out or get rid of the workbooks entirely with a real solution, because in my experience businesses that rely on using VBA to make Excel usable are eternal trash fires of organization.
|
|
#
?
Jun 10, 2019 18:46
|
|
|
Just demand that all VBA work be paid in 1995 dollars.
|
|
#
?
Jun 10, 2019 19:05
|
|
|
Yeah that's true. We just use it to create categorized spreadsheets of data that we pull out of sap. Manually formatting it which they have been doing for the last 5+ years just takes forever and I'm amazed some other solution hasn't already been done. With that said, I am learning Blue Prism in the next few weeks so that should be able to automate a bit more of the work as well. Hopefully that's something useful to add to my resume.
|
|
#
?
Jun 10, 2019 19:12
|
|
|
WTF is happening in my spreadsheet/mail merge? I'm entering student grades and something weird is happening in one of the columns and I don't know why or how or if it's loving with any of my other marks so I'm a little stressed (although no one has mentioned anything in the 3 years I've been using this system). I enter some scores as a number with a single decimal place (5.2, 5.7, 5.5, etc.) manually into the table. When I do the mail merge to send the marks to the students, these numbers have 0.000000000000002-0.000000000000004 added or subtracted from them. Here's what the numbers look like code:edit: I've created a new spreadsheet and entered the numbers manually again, there are no formulas or macros going on.
|
|
#
?
Jun 12, 2019 14:55
|
|
|
Has to do with how Excel is actually storing numbers behind the scenes (from here):quote:Excel stores its data as 32 bit binary floating point notation. Thus the decimal number 59.3 is actually stored as 59.299999 and 52.9 would be stored as 52.900002. These numbers are rounded for display in Excel, but when read by Word mail merge (any version from 2002 to the present) the underlying data is read and so you see 59.299999 or 52.900002 or whatever in your merge document.
|
|
#
?
Jun 12, 2019 15:09
|
|
|
Thanks! So if I'm reading it correctly, all of the calculations in the spreadsheet are accurate and unaffected by this 32 bit binary floating point thingy that I don't really understand. And if I see it, I can just clamp down on digits after the decimal and go about my business?
|
|
#
?
Jun 12, 2019 15:16
|
|
|
Sample data set:code:
|
|
#
?
Jun 27, 2019 19:24
|
|
|
Create a column that you can hide later and put in:code:code:
|
|
#
?
Jun 28, 2019 03:40
|
|
|
Yeah enter that data in multiple columns, and if you absolutely need that presentation then make an extra column that assembles the raw data columns into the presentation format. It also looks like maybe the third number can be looked up from the first (experience required to reach next level?), so you could get that from a lookup-table instead.
|
|
#
?
Jun 28, 2019 10:11
|
|
|
Scenario: I have a data dump across multiple CSV files. I'd like to import them all into a single workbook with some tables and formatting rules for better presentation, and I'd prefer to not have the tables linked to the original CSV files (so the Excel file can be distributed without recipients getting warnings about broken links etc.) Is the answer just to write a macro?
|
|
#
?
Jul 2, 2019 13:59
|
|
|
Hey folks. I need a bit of help. My team leader has given me a problem to solve, and I'm utterly stuck. Here's the story... I work at an organisation that has clients that have treatment pathways. Our board would like a quarterly spreadsheet that shows how many active clients each service has each quarter. I've worked out how to count the ones with dates correctly, but I can't work out how make it also count the clients who don't have an end date (i.e., currently active clients who have yet to complete their service). Here's some screencaps, and the formula I have so far (customer IDs blanked because of obvious reasons): What the results need to look like:  What the report data on sheet 2 (Customer Start End) looks like in excel (ignore it being in a table, that was me trying different things):  My current WIP formula: =COUNTIFS('Customer Start End'!B:B,"<=31/12/2014",'Customer Start End'!C:C,">=01/09/2014") (this would be for C7) Any help would be amazing. I've tried a bunch of different things, but this sort of thing isn't my forte in excel. Ideally, the solution would be as simple as possible to the end user, so anyone can just grab the report and jam it into the second sheet. We try to have a keep it simple policy, so anyone can pick up a job quickly if someone is off ill. Thanks in advance!
|
|
#
?
Jul 2, 2019 18:29
|
|
|
Xaerael posted:Hey folks. I need a bit of help. My team leader has given me a problem to solve, and I'm utterly stuck. Here's the story... You could count blank end dates and add the two together for a nice easy solve: =COUNTIFS('Customer Start End'!B:B,"<=31/12/2014",'Customer Start End'!C:C,">=01/09/2014") + COUNTIFS('Customer Start End'!B:B,"<=31/12/2014",'Customer Start End'!C:C,"") In your report table you may want to consider adding start/end dates, then you don’t need to hard code dates of each formula, you can just reference the above rows and paste the same formula across (I used calendar years instead of financial so adjust as necessary). You can hide these rows if you need a certain layout/look to it.  =COUNTIFS('Customer Start End'!B:B,"<=" & C5, 'Customer Start End'!C:C,">=" & C4) + COUNTIFS('Customer Start End'!B:B,"<=" & C5,'Customer Start End'!C:C,"") start date is before the quarter end date AND end date is greater then the quarter start date + start date is before the quarter end date AND end date is blank. e:fixed a typo in formula DRINK ME fucked around with this message at 22:46 on Jul 2, 2019 |
|
#
?
Jul 2, 2019 20:52
|
|
|
DRINK ME posted:You could count blank end dates and add the two together for a nice easy solve: Awesome. Thank you so much. I'll check out the second idea, I have the spreadsheet at home. You're a superstar!
|
|
#
?
Jul 2, 2019 21:49
|
|
|
I have a quick question about excel that is making me nuts: The following formula is meant to sum the nightly cost of a hotel (Hotels!$I3:$I20) between the checking date (Hotels!$C3:$C20) and the check-out date (Hotels!$D3:$D20) for a hotel in an array of such hotel data for a vacation of mine. B2 is the specific date in question. It keeps returning 0. code:AND statements work when I breakdown the individual inputs into discrete values outside of the array, so I don't know why the sumif is giving me grief on this logic.
|
|
#
?
Jul 12, 2019 00:32
|
|
|
Cheesemaster200 posted:I have a quick question about excel that is making me nuts:
|
|
#
?
Jul 12, 2019 01:31
|
|
|
mystes posted:Maybe I'm missing something because I don't have your spreadsheet in front of me, but do you really hotels where the check-in date is >=B2 and the checkout date is <=B2? Assuming the checkout dates are always strictly greater than the check-in dates, isn't it natural that nothing will match these requirements? Yeah, that would do it. Always stupid poo poo like that. Also, needed the checkout date to be only a ">" rather than ">=", otherwise it double counts the days you check out and check in. I will chalk it up to trying to do this at 1AM. Thanks for the help.
|
|
#
?
Jul 12, 2019 14:59
|
|
|
I'm trying to create a function that will tell me what the change to our bottom line is when a client either joins or departs our service. The table currently looks like this: Client A onboarded in February, and we performed work through June. Client B stayed constant through the year, with no change to our bottom line. Client C received services through March but was then offboarded. Client D was onboarded in February but offboarded in April, which cancels out any gain we may have seen. My goal is to have row H display the total effect to our bottom line over this time period, as shown in the above. Client A increased our recurring revenue by $500, Client B didn't change it, we lost $1000 by losing Client C, and Client D ultimately didn't have an effect either. Ultimately this formula/function will be used across a variety of date ranges (quarterly, annually) and across a large number of clients, so manually entering a count isn't feasible. tl;dr: Is there a way to compare cells against one another such that if there is a change from NULL to (Data) a count will go up, and if there is a change from (Data) to NULL the same count will go down, all within the same row (range)?
|
|
#
?
Jul 17, 2019 00:16
|
|
|
(If(g2="",0,1)-If(c2="",0,1))*b2 or something?
|
|
#
?
Jul 17, 2019 00:26
|
|
|
I was hoping not to have to tweak the formula every time I change the number of columns, which I believe I would have to do with the IF approach (e.g. test Jan/Feb, then test Feb/Mar, then test Mar/Apr, et cetera). Something I could set once and have it work for a range would be ideal, rather than an ever-expanding chain of IF statements.
|
|
#
?
Jul 17, 2019 00:40
|
|
|
I have a list of assets that looks like this: dSCRAQSS9 dSxAXP47 dSBPW17 dSDEEPS15 dSyQyC22 dSyQyC21 dSyQyC20 dSyQyC19 dSSPxS47 dSBPW16 dSDEEPS14 dsxaxP41 dSxAXP42 dSxAXP43 dSxAXP44 dSxAXP45 dSxAXP46 dSxAXP35 dSACRAQ1 dSACRAQ2 How can I determine the highest value per asset? So dSxAXP's highest number is 47 and dSDEEPS is 15. Not all names are 6 letters, not all numbers are 2 digits. I'm about at the point where I just add ghost data to make it count correctly from an external source.
|
|
#
?
Jul 17, 2019 16:33
|
|
|
Nazattack posted:I have a list of assets that looks like this: Take a look at this page. If you know all the asset strings you should be able to list them out without numbers and use a combination of =maxifs() and that page to do what you want
|
|
#
?
Jul 17, 2019 18:08
|
|
|
kumba posted:Take a look at this page. If you know all the asset strings you should be able to list them out without numbers and use a combination of =maxifs() and that page to do what you want I ended up just adding some math to cheat the answers. 
|
|
#
?
Jul 17, 2019 20:32
|
|
|
I wouldn't use =MAXIFS, I'd use a pivot table, where the string part is my row header, and the value is computed with the max function.
|
|
#
?
Jul 17, 2019 20:35
|
|
|
FreshFeesh posted:I'm trying to create a function that will tell me what the change to our bottom line is when a client either joins or departs our service. The table currently looks like this: What I think you need to do is add a couple of cells for selecting Start period and End period, use data validation | list and point it to c1:g1. Then in your Change formula you could use something like this =Index(c2:g2, match([Start date cell], c$1:g$1, 0)) - Index(c2:g2, match([End date cell], c$1:g$1, 0)) Make sure you lock the header range (using the $ symbol) so when you copy the formula down to the other clients it will still work. Hlookup if you’re not comfortable with index(match), same result and unless your dataset is crazy big there’s no difference. This should allow you to change your start and end dates selection to see the change for whatever period is required and continue working if you insert a new column for July, Aug, etc.
|
|
#
?
Jul 18, 2019 08:11
|
|
|
nielsm posted:Scenario: I have a data dump across multiple CSV files. I'd like to import them all into a single workbook with some tables and formatting rules for better presentation, and I'd prefer to not have the tables linked to the original CSV files (so the Excel file can be distributed without recipients getting warnings about broken links etc.) I think if you use queries to pull the data into hidden sheets in the workbook the end user would only get warnings if they hit refresh data since it saves the queried data with the workbook. Queries also pull the data into neat little tables and allow you to specify formatting. I do this process with several workbooks where I dump data from our ERP to update the query sources.
|
|
#
?
Jul 20, 2019 05:01
|
|
|
I'm dealing with some data conversion between systems and I have a question about how to format an export from one system as an import into another that I'm hoping someone can provide some guidance on. I don't have any way to customize the export itself (and that's one of the reasons we're moving away from this poo poo system), so I have to do some finagling in between to get it in the right format. Here's the rough idea of my data:  This is employee punch data from our timekeeping system, so these correspond to punches in / punches out. Ignore the fact that some of these punch pairs are identical times (e.g. rows 77 & 78) for the moment. Here's what I'm having trouble with: I need to insert a column to the left of all the timestamps that, for each row under a specific employee, contains the value in the current Column E. So for this example, I need to shift all those timestamps into column B, and in column A, I need the value 1344 in rows 69 - 78, and the value 1842 in rows 80 - 97. The number of rows is going to fluctuate between employees. I'm assuming there's some way to look for the closest row above with a value that is not a timestamp in a column, and retrieve the ID from said row? I just can't wrap my head around how to accomplish that or if I'm making this too difficult and there's an obvious easy answer. There's a few other things I need to do but I think I mostly have those settled, this is my sticking point. Any help would be much appreciated!!
|
|
#
?
Aug 2, 2019 13:59
|
|
|
kumba posted:I'm dealing with some data conversion between systems and I have a question about how to format an export from one system as an import into another that I'm hoping someone can provide some guidance on. I don't have any way to customize the export itself (and that's one of the reasons we're moving away from this poo poo system), so I have to do some finagling in between to get it in the right format. The quick way to do it is select from E1:E whatever you last row is. Ctrl G (for Go to), Special, Blank cells, OK - this will select all blank cells within you current selection. Then type = and press the up arrow key, hold Ctrl and press Enter - this will enter the formula to pick up the value from the cell above. Now you should have employee numbers on every row, then you can copy-paste as values if needed, otherwise just cut the column and insert in column A.
|
|
#
?
Aug 2, 2019 14:26
|
|
|
kumba posted:So for this example, I need to shift all those timestamps into column B, and in column A, I need the value 1344 in rows 69 - 78, and the value 1842 in rows 80 - 97. The number of rows is going to fluctuate between employees. I'm assuming there's some way to look for the closest row above with a value that is not a timestamp in a column, and retrieve the ID from said row? I just can't wrap my head around how to accomplish that or if I'm making this too difficult and there's an obvious easy answer. You want Excel to fill in the blank cells in column E with the value from the cell above. To do that, Select column E only from the first ID to the last row you want an ID in. Hit CTRL+G to bring up the Go To menu, select Special, select Blanks, hit ok. Type the equals sign, hit the up arrow, then CTRL+Enter. The hard part is over, but now you'll want to change the formulas you just entered to values. Select column E, copy it, then Paste Values (Alt, H, V, V).
|
|
#
?
Aug 2, 2019 14:27
|
|
|
the two of you just absolutely blew my loving mind with how easy that is. this is perfect, thank you both!!!!
|
|
#
?
Aug 2, 2019 15:06
|
|
|
sorry for double post, but another question: apparently I also need to convert the format the timestamp is in and I'm trying to figure out if there's a way to do it en masse and not 1 by 1? rows 20 & 21 are in the proper format; rows 22 & 23 are not I selected the whole column and formatted to custom (yyyy-mm-ddThh:mm:ss), but the values don't actually update until I actually edit the cell (e.g. F2 then Enter) and I'd really like to avoid having to do that for potentially thousands of cells. I saw somewhere that Shift + F9 should recalculate the whole tab, but that doesn't seem to be doing anything for me, and doing CTRL ALT F9 just recalculates formulas, it's not actually updating the format What's the best way to accomplish this?
|
|
#
?
Aug 2, 2019 15:26
|
|
|
kumba posted:sorry for double post, but another question: apparently I also need to convert the format the timestamp is in and I'm trying to figure out if there's a way to do it en masse and not 1 by 1? I think I've done this by selecting the cells that need to be corrected, opening the VBA editor (ALT+F11), and in the Immediate Window entering "selection.value2 = selection.value2", or multiplying those cells by 1 (enter 1 in any cell, copy it, select everything that needs to be corrected, paste special, multiply). ¯\_(ツ)_/¯
|
|
#
?
Aug 2, 2019 16:14
|
|
|
|
| # ? May 13, 2024 11:49 |
|
|
I've got a workbook with two different sheets of items in stock.  I've got a third sheet in the same workbook, where I want to consolidate all the items that are low on stock and need to be ordered (shown by the column with test in it)  Ideally, this would only be a list of those things, and not the items that don't need ordering, but I have a macro that can hide empty rows if need be. My question is what kind of formula or macro can I write to pull the information from the stock sheets into the needs-ordering sheet? I've been mucking about with some if and or statements but can't quite pull it off because I'm not super familiar with excel formula syntax.
|
|
#
?
Aug 19, 2019 08:03
|
|
