|
Rakeris posted:I feel like I am overlooking a simple way to do this, but I want to create a command button/form button, to copy wtvr is in column Q and put it in column P, but I only want it to do it for certain words (either by whitelisting or blacklisting or wtvr) Mostly I just put my lists on another sheet than the one I’m working on and then read that into VBA. I think the “proper” way to do it would be to use an array - you dim the array and then populate it from your blacklist/whitelist but I haven’t used arrays enough to know how off the top of my head. Since it’s Excel though, you can google up a million examples once you know what you’re looking for. There are definitely other ways to do it because there’s always another five ways to get the result you want in excel. Create an array, populate it with your whitelist or blacklist and then do a loop to compare it. Something like the below (this won’t work because it’s just rough pseudocode to give you the outline of how vba does it). Dim my_array as array Set my_array sheets(“whitelist”).range(“a1:a100”) my_working_range = sheets(“working sheet name”).range(“q:q”) For each cell in my_working_range If cell.value in my_array then cell.address.offset(0,-1).value = cell.value End if Loop Then you go to your Developer tab, add a button and point it to your code. —- If you want a really easy way to do it without code, you can just do formulas. Create another sheet In column A you have your whitelist Back on your workingsheet In column P you have =iferror(vlookup,q1,othersheet!a1:a100,1,0),””) That will populate P with your values that match the whitelist or be blank otherwise.
|
#
?
Jun 30, 2021 21:17
|
|
|
|
| # ? Apr 19, 2024 01:36 |
|
|
Awesome, that should be all I need, thank you for the great explanation. I had considered using vlookup too, but had not played with it.
|
|
#
?
Jul 1, 2021 01:04
|
|
|
I haven't used Excel in awhile and I can't figure out what is going on with how it's parsing CSVs: I made this file: "gently caress off, excel", "this loving sucks poo poo", "gently caress, you" "I, loving, hate, excel", "gently caress, gently caress gently caress" in column 1 it has: gently caress off, excel I, loving, hate, excel which is fine and good, and is using the text qualifier double quotes as expected, but then it seems to be deciding every other column plays by different rules? column 2: "this loving sucks poo poo" "gently caress column 3: "gently caress gently caress gently caress" column 4: you" including quotes. In addition to this they seemed to have removed the text qualifier from csv/text import. Are they just trying to kill CSV as a format or what? As you can probably tell this poo poo is driving me crazy, I've got a bunch of CSV data I need to read.
|
|
#
?
Jul 13, 2021 04:09
|
|
|
Nice Van My Man posted:I haven't used Excel in awhile and I can't figure out what is going on with how it's parsing CSVs: I haven't ever used CSV's myself but I played around with things a little bit and eventually ignored my inner grammarian and removed the spaces after the commas outside the quotes and that seems to be what you want.
|
|
#
?
Jul 13, 2021 04:22
|
|
|
Nice Van My Man posted:I haven't used Excel in awhile and I can't figure out what is going on with how it's parsing CSVs: I think some of the quotes are different, not ASCII 34 (U+0022).
|
|
#
?
Jul 13, 2021 10:28
|
|
|
Aha, the spaces after commas thing was correct! Thanks! (they should be all the same character, not sure why they looks slightly different in my post but they were all me hitting the " key). Now the easiest thing for me to do is probably figure out some way to process the CSV files to remove those spaces. I swear, as someone who has to frequently output CSV docs for other people to read, Excel is the bane of my existence. From defaulting to not use commas as the delimiting character to inserting extra quotes every time the file is saved to whatever it's doing now. It's such an easy format to write a parser for that I can only imagine someone there hates it. Sorry, just had to vent.
|
|
#
?
Jul 13, 2021 14:07
|
|
|
Nice Van My Man posted:Aha, the spaces after commas thing was correct! Thanks! (they should be all the same character, not sure why they looks slightly different in my post but they were all me hitting the " key). Unless there are quote marks inside the initial quote marks after each separating comma that are important for some reason - ie. "I, "loving", "hate", excel" - then a simple find and replace in Notepad should do the trick. code:
|
|
#
?
Jul 13, 2021 14:51
|
|
|
Thanks! Got it all cleaned up, and if a few commas inside a string literal get pushed next to quotation marks I'll still be able to sleep at night.
|
|
#
?
Jul 14, 2021 02:50
|
|
|
is there a way in excel to have conditional formatting if a pair of cells match? for example in the below list, I'd like to have conditional formatting so that only the cells with john doe gets highlighted in a different font (cells a2 & b2), and none of the other ones. is this possible? Specifically, I don't want b3 or a4 to be highlighted, but do want both a2 and b2 to get highlighted. code:double nine fucked around with this message at 10:49 on Jul 22, 2021 |
|
#
?
Jul 22, 2021 10:39
|
|
|
Using this formula in your conditional formatting will work: =AND($A2=$D$2,$B2=$E$2)
|
|
#
?
Jul 22, 2021 11:52
|
|
|
that's what I needed, thx.
|
|
#
?
Jul 22, 2021 13:24
|
|
|
This isn't exactly... the right thread, but I'm not sure where else to go with it. I've been putting together a material quoting calculator for work in Google Sheets. We create a google document for every job we quote with some notes, comments, images, etc. I wanted to be able to copy the material calculation tables into a section of the google doc. It looks good on Google Sheets:  ... but if I paste it into a Google Doc, the formatting gets totally butchered.  Does anyone know how to fix this, or if there's a smarter way of doing this?
|
|
#
?
Aug 28, 2021 02:47
|
|
|
Have you tried changing the page to landscape and reduce margins?
|
|
#
?
Aug 28, 2021 08:04
|
|
|
I'm trying to figure out if there's a way to highlight a cell or row based on the contents of two cells in the same row, relative to each other. I have a spreadsheet where one column is a Category number (1,2,3 etc.) and another column is a Value (0~200 let's say). I want to write a rule or function that says for example "For each row, if Category = 1 and Value is between X and Y, highlight that row (or some assigned cell within that row)", and so on for Category = 2 or 3 or further up. Is that a thing in Excel? E: The obvious answer is to make a separate tab for each Category value since there's only four or five of those, but I'm trying to keep all rows on the same sheet if possible. C-Euro fucked around with this message at 16:59 on Sep 27, 2021 |
|
#
?
Sep 27, 2021 16:34
|
|
|
C-Euro posted:I'm trying to figure out if there's a way to highlight a cell or row based on the contents of two cells in the same row, relative to each other. I have a spreadsheet where one column is a Category number (1,2,3 etc.) and another column is a Value (0~200 let's say). I want to write a rule or function that says for example "For each row, if Category = 1 and Value is between X and Y, highlight that row (or some assigned cell within that row)", and so on for Category = 2 or 3 or further up. Is that a thing in Excel? You can do that with conditional formatting. Are you going to have the X and Y values hardcoded or will you want to be able to adjust them? An example where the X and Y values are taken from cells on the sheet:
|
|
#
?
Sep 27, 2021 18:21
|
|
|
I'm given a list of serial numbers, 200 to be exact, every day. When pasted into A1 of excel it fills all of column A up to A200. This is to be expected. Is there a function or anything that can cut and paste the bottom 50 serial numbers (A200 to A151) into B1, then the next 50 numbers (A150 to A101) into C1, and then the next 50 numbers (A100 to A51) in D1? So now all serial numbers fill from A1 all the way to D50. I'm currently just cutting and pasting manually and truthfully doesn't take long but its annoying enough.
|
|
#
?
Nov 3, 2021 20:45
|
|
|
If it’s exactly 200 each time it’s really easy, if it’s variable it becomes a bit more challenging but still achievable. It’s an easy enough one that you could hit Record Macro and then do your cut and paste work and re-use that recording each time. If you do want it variable you could consider changing the order - paste 51-100 into B, paste 101-150 into C, paste 151-200 into D. That way if you have less than 200 it will just paste up-to whatever you have without leaving gaps in the data - it will just fill up the start of each column with as many as available. The recording will be a bit messy because it picks up all kinds of stuff but you basically need something like this: Range(“a51:a100”).cut Range(“b1”).select Activesheet.paste Range(“a101:a150”).cut Range(“c1”).select Activesheet.paste Range(“a151:a200”).cut Range(“d1”).select Activesheet.paste
|
|
#
?
Nov 3, 2021 21:25
|
|
|
I had to check if there was a better way to do that to avoid using the clipboard, and there is:code:
|
|
#
?
Nov 3, 2021 21:45
|
|
|
nielsm posted:I had to check if there was a better way to do that to avoid using the clipboard, and there is: You could do this through a macro as well, just highlight the cell range and drag.
|
|
#
?
Nov 3, 2021 22:13
|
|
|
You guys are kick rear end. Really, thanks for the help. I'm going to try everything.
|
|
#
?
Nov 3, 2021 22:51
|
|
|
That’s awesome. I didn’t realise cut had an optional destination property but that’s a much neater way to handle it.
|
|
#
?
Nov 3, 2021 22:54
|
|
|
helta posted:You guys are kick rear end. Really, thanks for the help. I'm going to try everything. If you are going with a macro you may run into access/security issues depending on settings but I think the defaults just force you to enable to macro each time you want to use it. After hitting Record Macro you’ll get a small options window for a name, a shortcut command, and where to store it. If you tell it to store in a New Workbook a new file will open and that’s the one you’ll need to save and open again to run the macro (with the shortcut command you chose) for each new workbook you paste the numbers into. Since you said the numbers come from outside excel you probably don’t need to worry much if the macro doesn’t work right the first time but always test a macro before running it on the real thing.
|
|
#
?
Nov 4, 2021 00:02
|
|
|
nielsm posted:I had to check if there was a better way to do that to avoid using the clipboard, and there is: I've went with this method and its perfect. One thing I forgot to mention is that I actually want to copy the data and paste it into another application. I've added: code:to the end of routine, but that only allows me to paste within Excel. How do I go about getting the data into the clipboard?
|
|
#
?
Nov 5, 2021 15:19
|
|
|
If this isn't the right place to ask apologies in advance, but I'm looking to pay someone to build a business dashboard for a small business. Preferably someone in Canada so I can pay with e transfer but not a hard requirement. Looking for some nice graphs to track margin, sales, etc and be easy to dump data into weekly.
|
|
#
?
Dec 2, 2021 00:27
|
|
|
Is getting PowerQuery to source from relative file locations as difficult as the internet is making it look? I just want to be able to copy a folder containing the file and the subfolder to Wherever and still have it work. Having to dedicate a cell to my worksheet's current file location seems like a bonkers solution versus just using something like ./ but maybe I'm the weird one for thinking so.
|
|
#
?
Dec 8, 2021 17:28
|
|
|
Not coding really, more formatting I guess. Excel is treating 0 as negative in its conditional formatting. I made a timesheet to track overtime. It does 1904 date stuff to get negative time to show properly (i.e., when I owe the company some flexitime back). Column E is used to add time to the balance. Column G is uesd to deduct time from the balance. Column F shows the balance and is formatted as -h:mm;+h:mm Taking F10 as an example: code:For example, if F9 is 1:00, and G10 is 1:00, then F10 is +0:00 The conditional formatting is set up as cell value = 0 is blue, <0 red, >0 green, applied to $F:$F. Red and green work fine, even blue works fine if I type in 0 or 0:00 directly to a cell. But when it's an output the cell is red - using the example above, the cell shows +0:00 but is filled red. How can I get it to fill blue when accrued time balance is 0?
|
|
#
?
Dec 9, 2021 06:17
|
|
|
simplefish posted:Not coding really, more formatting I guess. My guess is that there's a small rounding error and therefore it's not exactly 0. Could change to Eg < -0.1 red, >0.1 green and between those 2 blue. If that fixes it then you can try shrinking the gap.
|
|
#
?
Dec 9, 2021 07:06
|
|
|
simplefish posted:The conditional formatting is set up as cell value = 0 is blue, <0 red, >0 green, applied to $F:$F. code:When its at 0:00 and you change the format back to a number, what happens?
|
|
#
?
Dec 9, 2021 07:38
|
|
|
Is there any excel equivalent to PARTITION BY in SQL? I'm trying to build a (stupid, thankfully temporary) spreadsheet that will calculate poo poo related to attendance, and I need to evaluate consecutive absences for each individual. If the data was guaranteed to be ordered, this is unbelievably easy; my problem here is that I cannot guarantee the rows I need to evaluate are consecutive (and in fact are basically always going to be non-consecutive). Basically the place I work is overhauling its attendance policy to not count consecutive absences against you - e.g., if you call out for a day because you're sick, that counts as 1 point. If you call out for an entire week because you're sick, that also only counts as 1 point. I need to find some way to only count the first day in a consecutive string of absences without the data being grouped together. Effectively what I need is something like: code:Rows 4 & 5 do not count because Bill is out for 3 days in a row Row 6 does count because Jeff did not call out on 1/4/22. (Side note: yes I know this is a loophole where someone out for less time is penalized more. I didn't come up with this mechanism, it's stupid, but it's what I'm working with) My thought was to use a helper worksheet with a calendar full of all the workdays and assign consecutive numbers to each (we're not open for business every day so I can't just compare the dates themselves), and use this to identify gaps larger than 1, but how to shoehorn this in to this workbook is really evading me. Anybody done anything like this and got any tips??
|
|
#
?
Dec 29, 2021 23:06
|
|
|
kumba posted:
code:code:
|
|
#
?
Dec 30, 2021 00:16
|
|
|
TheLastManStanding posted:
Beware weekends and holidays if you do this. Make sure it does what you expect it to.
|
|
#
?
Dec 30, 2021 14:05
|
|
|
TheLastManStanding posted:Then for each absence you can assign it a 1 or 0 based on if an absence exists for that person on the previous date number. This is the bit I couldn't work out and seems so obvious after you posted it. Works like a charm, thanks a ton!! nielsm posted:Beware weekends and holidays if you do this. Make sure it does what you expect it to. Noted. I've already removed necessary weekends & holidays from my calendar so I should be good to go there. Thanks y'all!!
|
|
#
?
Dec 30, 2021 15:17
|
|
|
Everett False posted:Is getting PowerQuery to source from relative file locations as difficult as the internet is making it look? I just want to be able to copy a folder containing the file and the subfolder to Wherever and still have it work. Having to dedicate a cell to my worksheet's current file location seems like a bonkers solution versus just using something like ./ but maybe I'm the weird one for thinking so. It's a headache, I haven't found a straightforward way to do it either. I've resorted to making a tab containing a Table with information I want and then making the tab unviewable. Regardless you somehow need to create a Parameter with the file path you want to use Depending on where you're pulling data from you might be able to construct the path from alternate queries.
|
|
#
?
Jan 11, 2022 16:17
|
|
|
I gave up on the whole endeavor when I discovered that an Excel file in a OneDrive folder will default to returning its location as being a web address rather than the local folder. Maybe turning off using Office applications to sync would have fixed it? But by the time I was looking at bigass functions in StackOverflow I decided there was no way it would actually save me any time or energy. 
|
|
#
?
Jan 12, 2022 02:29
|
|
|
Is there some way to remove trailing spaces from cell values without using the TRIM function? I'm going to be sent a monthly table and one of the columns I'm going to be using as a look up reference seems like it has to have 10 characters in it and it fills those characters with spaces if there wasn't 10 text characters entered. The problem is I'm generating the same references in a summary table on my end but without the trailing spaces. If all the references were the same length I could find/replace the number of spaces but they won't be. It wouldn't exactly be hard either to just insert my own column into the table, use TRIM from top to bottom, and reference the new column instead so I'm mostly just wondering if there's some easy method I don't know about.
|
|
#
?
Jan 13, 2022 00:47
|
|
|
Kibayasu posted:Is there some way to remove trailing spaces from cell values without using the TRIM function? I'm going to be sent a monthly table and one of the columns I'm going to be using as a look up reference seems like it has to have 10 characters in it and it fills those characters with spaces if there wasn't 10 text characters entered. The problem is I'm generating the same references in a summary table on my end but without the trailing spaces. If all the references were the same length I could find/replace the number of spaces but they won't be. It wouldn't exactly be hard either to just insert my own column into the table, use TRIM from top to bottom, and reference the new column instead so I'm mostly just wondering if there's some easy method I don't know about. Are there spaces IN the data or just at the end? If there are only trailing spaces you can use text to cells under the data ribbon and set the delimiter character to space.
|
|
#
?
Jan 13, 2022 00:52
|
|
|
Lib and let die posted:Are there spaces IN the data or just at the end? If there are only trailing spaces you can use text to cells under the data ribbon and set the delimiter character to space. There should only be trailing spaces if the references were done correctly. I checked your suggestion and after figuring out that I need to leave out the column header (which does have a space between two characters) and it looks that that should do the trick. Thanks!
|
|
#
?
Jan 13, 2022 01:03
|
|
|
I've got this table with some grouped/collapsed rows at the top that are mostly blank except for a few I've pre-filled to simulate some form of auto-complete functionality in the rest of the table. I fill the columns in with the usual stuff - date, reference number, a few names - and every two weeks I sort it by date so another program can import it into our accounting software. The problem is that occasionally, but not every time, I sort the table by date those blank cells at the top will get sorted into the middle of the table rather than stay where they are and that confuses the other program. I'm this close to just dropping the grouped rows at the top because the auto-complete for a few columns is a mild convenience at best but I'm also curious if anyone knows what would cause blank cells to be considered older/newer than cells with actual dates.
|
|
#
?
Jan 20, 2022 00:47
|
|
|
I have a monthly report calculator that I put together to help automate some work, but I've run into an inconstancy that maybe someone can help me identify. Let me start with two screenshots: =SUMIFS(Participants!J:J,Participants!K:K,">="&DATE(2021,9,1),Participants!K:K,"<="&DATE(2021,9,30))  In the first picture you see my formula, which I've modified to use direct dates instead of references to a cell. This changed nothing and is more for troubleshooting, and to show that I am trying to filter out info for just one month. September is one of the few problem months I'm dealing with. It thinks the total of the J column for cases where the K column is a date in September is $688,249. In the second picture you see the data sheet, where I've filtered the date column (K) for just September. When I click to highlight the whole of the J column, Excel's little info in the bottom has helpfully summed them up as $746,307. This is a pretty significant difference and I'm not sure why the formula isn't picking up some of the data that seems to be easily accessible. There are no other filters active on the data sheet. The root of my question is why do the arguments in the formula not replicate the filtering action I took, and what can I do to fix my formula going forward? I just don't understand why two different methods that should, imo, lead to the same results are coming back with different answers. For some months the formula comes through with accurate info (Oct, Dec) while others are off by tens of thousands. Any suggestions?
|
|
#
?
Jan 25, 2022 19:29
|
|
|
|
| # ? Apr 19, 2024 01:36 |
|
|
LawfulWaffle posted:I have a monthly report calculator that I put together to help automate some work, but I've run into an inconstancy that maybe someone can help me identify. Let me start with two screenshots: my first thought: your last argument is <=DATE(2021,9,30), but that means MIDNIGHT 9/30/21. Are there times hidden in column K, and are there entries for 9/30/21 that sum up to your difference (and a corresponding lack of entries on the final days of other months that do calculate right)?
|
|
#
?
Jan 25, 2022 19:34
|
|