|
It's just raw UTF-8 CSV files, opening them from explorer. My little machine with 16gb or ram would choke to death copying.... Lemme check real quick....225x24000 out of a csv file and pasting values...back into a csv file. If I had my way, we'd just be getting full SQL dumps to load and work with but.... Yes, that's 225 columns of data. Exceed Beyond is....silly.
|
# ? Mar 3, 2022 17:39 |
|
|
# ? May 11, 2024 15:57 |
|
Oh god you're dealing with that sort of stuff. Yeah I would be using SSIS/SQL or even Access if I had to as Excel is not going to be friendly.
|
# ? Mar 3, 2022 17:43 |
|
Wandering Orange posted:Oh god you're dealing with that sort of stuff. Yeah I would be using SSIS/SQL or even Access if I had to as Excel is not going to be friendly. It's both shocking and completely unsurprising that the companies that do business with Nonprofits will do anything and everything the can to prevent customers from leaving their jank-rear end systems, up to and including making the export process as difficult as possible.
|
# ? Mar 3, 2022 17:46 |
|
I'm trying to figure out how to approach a problem, or to see if it is even possible in Excel especially for me, who is a novice. I have five categories A B C D E I have 2,000 email addresses which are aligned with at least two of the categories, and a single email address may align with all five categories. I have been asked to figure out the overlap in the categories. For example, how many people in A/B A/C A/D A/E B/C C/D C/E and so on. Is this even possible? Aside from the two-category combinations, I need to consider who is in three, four, and five categories. That's a ton of combinations, right?
|
# ? Mar 24, 2022 13:50 |
Each category is a binary yes/no. That means you have 2*2*2*2*2 = 32 combinations, possibly minus one if the "no categories" option is invalid/impossible.
|
|
# ? Mar 24, 2022 13:58 |
|
Thanks - the good news (I think) for me is I just found out they only need these combos: A B C A B D C D A B C D I still have no idea how to do it efficiently.
|
# ? Mar 24, 2022 15:35 |
|
me your dad posted:Thanks - the good news (I think) for me is I just found out they only need these combos: What structure is the data in? Does an email address have a row for each category (e.g., the table structure is Email Address | Category) or is it one row per email with multiple columns (e.g., the table structure is Email Address | Cat A | Cat B etc with a value of 0 or 1 or whatever) How you have it structured will inform your easiest approach
|
# ? Mar 24, 2022 15:45 |
|
Sounds like maybe Conditional Formatting could help you with this? I do something similar at work where I have to compare long columns for duplicates, and this resource is bookmarked on my browser because I can't bother to memorize the formulas https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/ Specifically the parts about "Compare two lists and highlight records in both of them" help me a lot
|
# ? Mar 24, 2022 16:05 |
|
kumba posted:What structure is the data in? Does an email address have a row for each category (e.g., the table structure is Email Address | Category) or is it one row per email with multiple columns (e.g., the table structure is Email Address | Cat A | Cat B etc with a value of 0 or 1 or whatever) After a lot of looking at it, I found it very messy. But I was able to clean it up a bit. Each row contains a category and an email address. Category | Email timp posted:Sounds like maybe Conditional Formatting could help you with this? I do something similar at work where I have to compare long columns for duplicates, and this resource is bookmarked on my browser because I can't bother to memorize the formulas https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/ I have indeed utilized conditional formatting and it has been helpful. After some back and forth with the person I am working with, I have reduced the number of records to about 800 and I am working my way through them manually right now.
|
# ? Mar 24, 2022 16:31 |
|
Sounds like you want CountIfs
|
# ? Mar 24, 2022 16:42 |
|
me your dad posted:After a lot of looking at it, I found it very messy. But I was able to clean it up a bit. Each row contains a category and an email address. here's my maybe overengineered shot at it - you can assign unique values to each category such that each individual combination of categories arrives at a distinct sum (think binary operations). so for example (EDIT: lol oops you can do it with 1/2/4/8 instead of 1/4/8/16 but whatever you get the idea) so for the combinations you want, you end up with: so now we wrap it up: here's my faked data with a vlookup to the points table from there you do a sumifs for each unique email and then vlookup the total and voila, you get your desired combinations and you can filter out the #N/A rows that don't match your needed combos there is probably an easier way to do this but this is where my mind went!
|
# ? Mar 24, 2022 16:50 |
|
Are you just counting how many times a combination occurs? That is, for example, 10 emails appear under categories A/B/C. Or do need to have the emails displayed if they match a combination?
|
# ? Mar 24, 2022 16:55 |
|
I’m having some issues with VBA in Word, but there is no Word VBA thread around these parts. Would this belong here?
|
# ? Mar 24, 2022 17:25 |
|
kumba posted:here's my maybe overengineered shot at it - you can assign unique values to each category such that each individual combination of categories arrives at a distinct sum (think binary operations). so for example Thank you - this is a good approach! Kibayasu posted:Are you just counting how many times a combination occurs? That is, for example, 10 emails appear under categories A/B/C. Or do need to have the emails displayed if they match a combination? I just need the number - 10 in category AB, 125 in category ABD, etc.
|
# ? Mar 24, 2022 17:57 |
|
Falconier111 posted:I’m having some issues with VBA in Word, but there is no Word VBA thread around these parts. Would this belong here? this is the closest we have to a VBA thread i think and there's definitely excel VBA questions in here, go hog wild
|
# ? Mar 24, 2022 18:20 |
|
kumba posted:this is the closest we have to a VBA thread i think and there's definitely excel VBA questions in here, go hog wild First of all: extremely inexperienced and self-taught, so keep that in mind. I’m doing a screenshot let’s play right now, and I get the text for my updates by copying it over from the original script in the game files, then running it through a couple VBA scripts that are just big find-replace functions to bring it more in line with SA BBcode. After the first script, I get text looking like this: quote:"She forces a smile. It’s not very convincing." Each line either starts with double quotes for narration or a letter or two to indicate the speaker. I need some code that will look at each section enclosed by quotes, check if it has the same speaker as the previous line, and if it does, append the two, ideally removing intermediary speaker markings and quotes in the process. I’m hoping it ends up looking like this: quote:"She forces a smile. It’s not very convincing." At this point I usually make necessary manual edits before running it through the last script and publishing it. I’ve been removing the unnecessary bits by hand, but I have mobility issues that are getting in the way; text to speech works miracles for prose and I have little issue with standard editing (I got through my last LP just fine), but the software doesn’t accommodate coding and having to edit these marks out over and over for thousands of words/write code by hand to experiment with it is killing me. The rest of the process is under control, it’s just this one area is a sticking point. Does anyone know how to go about researching or implementing a solution?
|
# ? Mar 24, 2022 23:32 |
|
I’ve never done macros in Word but I do a lot of Excel stuff. I think it would be like a For loop? This is sort of pseudocode based on rough excel VBA from memory. I’m sitting on a very boring work call so thought I’d give it a shot but I don’t think I’ve quite got it all figured out, might give you an idea though of how to manipulate stuff - providing this works in Word. I figure VBA should be somewhat similar across MS applications. I’m not sure what reserved name spaces exist in Word but guessing stuff like Document and Line and Paragraph so instead of saying “for each line in document” I’ve changed it. Dim this_line as line Dim next_line as line Dim this_doc as document Next line = this_line.offset(1,0) For each this_line in this_doc If left(this_line,2) = left(next_line, 2) then 1. take the quotation mark of the end of this_line /something like left(this_line, len(this_line)-1) 2. add a space to the end of this_line /this_line = this_line & “ “ 3. trim next_line to remove everything up to and including the first quote /right(next_line, find(“””, next_line)) 4. Might be needed to remove the linebreak / paragraph break. Chr(10) or Chr(13) I think but not sure how to address this… End if Next I think I might have missed an obvious one - might be too simplified for the narration lines, maybe two different checks? If left(this_line, 1) = “””” then action for narration Elseif (above check) action for speaker End if
|
# ? Mar 25, 2022 01:16 |
|
Falconier111 posted:
Use a code tag E I can't tell on my phone what you're looking for HootTheOwl fucked around with this message at 01:39 on Mar 25, 2022 |
# ? Mar 25, 2022 01:27 |
|
Ok I'm at my computer and I think you overthought it: First, format your input into an array of strings so it's on per line. It looks like you have this already but in case you don't, use the Split function to achieve this. Then define an output array of strings Then, run a For Each loop over the split input array, with each line being an element in the group. Inside this loop: If The output array is empty, add the current line to the output, except the last character (which should be a ") Next If the output array wasn't empty, then check the first character in the Line. If the line begins with a " then concatinate it onto the last element in the output array (remember to remove the leading and trailing " characters) Else, concatenate a " character onto the last element in the output array. And then Add the current line as a new element into the output array, removing it's trailing " Next, End For My VBA is pretty rusty but it should look something like: code:
HootTheOwl fucked around with this message at 02:43 on Mar 25, 2022 |
# ? Mar 25, 2022 02:31 |
|
I am sure this is some floating point bullshit but I have literally never seen Excel do something like this on a simple calculation what the actual gently caress is happening to the blue rows and why is the orange row fine
|
# ? Apr 14, 2022 23:17 |
|
Excel has some issues with decimal places, 8 bytes / 15 decimal places of precision. I’m not entirely sure if it applies to your example but there’s a detailed write up here on floating point errors. To be honest I’m hungover and only sort-of following it, which is why I’m not sure if it covers your issue. Last time I saw it at work I gave the person who asked this link to MS and they agreed to round their numbers in future (since no one cares about 0.00032 minutes or whatever fraction they had).
|
# ? Apr 15, 2022 01:02 |
|
I only expanded the decimals out to that many places to show that I wasn't hiding a digit in the trillionths place or whatever, the numbers I'm using are really simple. It's gotta be related to that example in your link of not being able to store the value 0.1 in binary, but what is confounding me is that I using the same numbers and getting two different results, e.g. my calculation of 2.4-1.5 = 0.9 and the same exact calculation 5 rows up has a different answer. In what you linked, you can reproduce the binary truncation going on in every case cause the math is always the same, so you would think if it were a binary translation problem or a rounding problem, it would be uniformly wrong but that is not the case!
|
# ? Apr 15, 2022 02:55 |
|
I see what you mean now and agree, I’d expect the error to be consistent each time. Are F and H just values? I have both Sheets (google) and Numbers (apple) on my phone and just did a quick test on them and both are also limited to 15 decimal places by default, so 8 bytes as well. I also tried the 2.4-1.5 and 3.35-3.45 that you originally showed and no issue with either of them. Something specific Excel is doing…
|
# ? Apr 15, 2022 04:42 |
|
So have a column of dates. I want Excel to highlight entries that are a year or more old. My understanding of how to do it is that I put this formula into the Conditional Formatting tool (assuming the information is in column A): code:
I suspect the reason is that Excel doesn't like working with functions like TODAY() in conditional formatting, because I can make a column of Boolean values with the exact same formula just fine, but some Google results for how to do this suggests that it should. In another thread I got a reply: Stickman posted:Yeah, basically the formula for conditional formatting is the formula for the top left cell in the formatting range, and the rest are applied as if they were autofilled. So considering this, is there a way to get Excel to do what I want?
|
# ? Apr 28, 2022 02:59 |
|
Mak0rz posted:So have a column of dates. I want Excel to highlight entries that are a year or more old. Yes, that reply is correct. What you need to do is change the conditional formatting formula to something like: code:
edit: This basically the same response as what you got in the math thread esquilax fucked around with this message at 03:14 on Apr 28, 2022 |
# ? Apr 28, 2022 03:12 |
|
The way I’d do it is: Click on A1 Conditional formatting | New Rule Choose Use a formula =(Today()-A1) > 365 Add the format/highlight Then change the “Applies to” section to be A:A like you need. That should do what you need with minimal fuss, just ensure you have either A1 or $A1 so it looks at each row.
|
# ? Apr 28, 2022 04:28 |
|
esquilax posted:Yes, that reply is correct. That worked, thank you. esquilax posted:edit: This basically the same response as what you got in the math thread Whoops. I somehow missed that one DRINK ME posted:The way I’d do it is: That works too, yeah. I got in the habit of using DATEDIF for working with months, because it takes into account the variable month length. For a whole year it probably doesn't matter much.
|
# ? Apr 28, 2022 17:29 |
|
Mak0rz posted:That worked, thank you. Don't forget leap years!
|
# ? Apr 28, 2022 19:43 |
|
esquilax posted:Don't forget leap years! Yeah I considered that but a creep of one day every four years is not going to be a big deal for my purposes. Regardless I'm sticking with DATEDIF anyway so it's moot.
|
# ? Apr 28, 2022 20:02 |
|
hey thread, I've got a problem I'm hoping you can all help me with. My boss has asked me to get something together to help aggregate PM tasks that need to be completed on some of our machinery at certain hour intervals. Along with that, the's requested that I add in some way to easily list off what parts, materials, and supplies will be used when performing these tasks. He's given me the freedom to accomplish this as I see fit, and I want to make it into a tool that we can use for the next long while, and maybe incorporate it across the entire company. I've decided the "best" way is probably going to be via Excel spreadsheet. First thing's first, I've been making an actual spreadsheet/grid, where each line lists a task. From left to right, it lists the task type, a small description of what the task is, the actual PM number in the machine manual, and then on the far right, there's a grid set up, where each column is an hour interval, and if the task should be done at that hour interval, then there's a check mark in that spot of the grid. I'm also compiling a second sheet, where each line has the PM number, "consumables" used(gloves, rags, etc), and then part numbers, if applicable, and then tools, if applicable. What I'd like to be able to do, is have a box where a user can input the hour interval, and then have either a separate section, or a separate sheet, populate with all of the PMs for that hour interval, along with all the materials needed for those PMs. Preferably broken down by the PM number, so that if, for example, you're at the 100 hour interval, and you type that in, each line would populate like: |____PM#____|_______________Description_______________|_Consumables_|__Part Numbers_|___________Tools___________| |_PM-B0010_|_Check tension of oven stationary chain__|__Rags, gloves__|_____None______|___________None___________| |_PM-B0059_|_Check oven locking pins, replace if worn_|______None_____|_752134009(4)_|_Socket wrench, Allen keys_| etc, etc, etc What's the "best" way to accomplish this? I've been googling around, and it seems that conditional formatting might be able to solve all this, but I'm way out of my depth when it comes to this stuff. If needed, I can move the required materials list to the same sheet as the grid, but I like the grid as it currently is just for how visually simple it is to look at. I'd also like to be able to expand the grid to add other machines and PMs to it as needed, if possible.
|
# ? Jul 20, 2022 22:16 |
|
Not sure what the best way would be. I did something similar several years ago with different types of data, unfortunately I don't recall exactly how I did it, I might be able to find one of the old workbooks... But I used one sheet as a data sheet and kept it hidden, (so people didn't try to break it) populated all the data I wanted to pull from on there, and used data validation to create drop downs so users could select what week they wanted (insert wtvr you want said drop down to be, hours, etc), that would then pull the data from the data sheet and populate the main sheet with said data. I know that is a really rough outline...hope that helps or gives you something to look at maybe. =\
|
# ? Jul 21, 2022 00:19 |
|
I'd look into the Filter function if you have a newer version of excel. It can filter and spill all the data where you need it. https://www.youtube.com/watch?v=4Fgb8qp0NxU
|
# ? Jul 21, 2022 00:36 |
|
My wife says you want a pivot table.
|
# ? Jul 21, 2022 00:50 |
|
I would guess a pivot table or two with a filter for the hour number, maybe a slicer depending on how many options you have (people seem to get excited if I include slicers). It’s hard to know for sure what would work best without knowing how much data needs to be returned. Then you just select the hour on the filter or slicer and it shows you a nice table of data.
|
# ? Jul 21, 2022 01:50 |
|
Harminoff posted:I'd look into the Filter function if you have a newer version of excel. It can filter and spill all the data where you need it. Anytime I see these new functions, I get annoyed that my work has not upgraded us to desktop O365 yet (we can only use the web versions), so many nice changes in excel.
|
# ? Jul 21, 2022 04:14 |
|
Rakeris posted:Anytime I see these new functions, I get annoyed that my work has not upgraded us to desktop O365 yet (we can only use the web versions), so many nice changes in excel. drat so no xlookups? I'd dread having to go back to vlookups and index/match
|
# ? Jul 21, 2022 05:36 |
|
Lib and let die posted:I don't know that this is the best thread for it, but since Excel is so often used for working with csv files...is there a better tool? Or a specific set of options I can turn on to have Excel not do any of its allegedly helpful things with say, date and time stamps? Depends what you're trying to do, but powerquery if you need to clean/shape the date or access (lol) to query and write back to it.
|
# ? Jul 21, 2022 07:09 |
|
Harminoff posted:drat so no xlookups? I'd dread having to go back to vlookups and index/match Nope....indexing and matching over here. When I first saw xlookup I was like holy poo poo that is awesome! Then found out a few minutes later I couldn't use it hah.
|
# ? Jul 21, 2022 11:28 |
|
I'm creating an agent performance dashboard at work. I run a report and paste the data in excel then have a dashboard that summarises it by employee. I have a bunch of rules that filters based on the employee name that's input into cell B3. I currently have the following macro for filtering a pivot table based on the employee name in B3 to show the assessments that were completed for themcode:
|
# ? Jul 21, 2022 12:24 |
|
|
# ? May 11, 2024 15:57 |
|
I started using xlookup recently and it is really great, I do kind of like index match but I’m never going to use it again. On new functionality, I got sent a spreadsheet with the new lambda function in it the other day, it’s a neat idea but our current enterprise version of Excel doesn’t support lambda function so the spreadsheet barely opened and then just hung “calculating”. I’m still waiting for a response to “if you want us to use this spreadsheet you need to make it compatible with our Excel”. If anyone hasn’t seen it: Lambda function.
|
# ? Jul 21, 2022 12:40 |