|
I've had those kind of problems before - if it's a massive file and the sorting is simple, it may be easier to filter and create the order you want in a different sheet via simple brute force copy & pasting as nauseum If the file is stored somewhere with auto save on (e.g., onedrive or SharePoint) try turning that off - for sorts on big files I've found that to be a huge bottleneck
|
#
?
Sep 14, 2022 00:15
|
|
|
|
| # ? Apr 26, 2024 03:32 |
|
|
HootTheOwl posted:What if the cell is create via a forumula? that's where turning off the auto calculations comes in. it sounds like the sort is making all 11,000 cells recalculate (and more if other sheet/cells are also referencing the sorted columns) and that's causing it to hang up
|
|
#
?
Sep 14, 2022 00:21
|
|
|
kumba posted:I've had those kind of problems before - if it's a massive file and the sorting is simple, it may be easier to filter and create the order you want in a different sheet via simple brute force copy & pasting as nauseum Yes, auto-save can be a real killer and is one I come across in my day job. If the calculations are taking a decent amount of time Excel can try auto-saving while that is going, and I think it gets into a tizzy because it usually calculates and then saves but also doing that while already calculating.
|
|
#
?
Sep 14, 2022 00:36
|
|
|
I have a report pulled from a database that has a description field. Most--but not all--of the descriptions contain an ID number along with a description of the product (e.g. "this is a can of red paint, product ID F1234567, that contained blue paint instead"). Product IDs are always F followed by a seven-digit number. Is there a way for me to check the description column for an ID number, if it finds one there return the ID number but nothing else from the description, and if it does not return "N/A"? Bonus optional question: while exceedingly rare, it is possible for the description to contain two ID numbers, is there a way to additionally validate for that, and return both? I've looked at the various formulas, and nothing I've tried seems to be able to return just the string I'm looking for; I can get it to find the ID numbers by doing a wildcard match for "F???????", but can't figure out how to get it to return that with XLOOKUP,. MATCH, or INDEX. I could probably get it done in Powershell, but I'd prefer a user-friendlier option.
|
|
#
?
Sep 30, 2022 20:23
|
|
|
I think you're going to the up needing to add something that can do regular expressions to really zero in on that. You could approximate it with SEARCH for "F???????" and then using the result in SUBSTR, but you would get every eight letter word starting with F, and it wouldn't work if the cell had multiple IDs. Actually the multiple ID problem is beyond my ken anyway.
|
|
#
?
Sep 30, 2022 21:47
|
|
|
I'm using a VBA code I googled to alter the enter key behavior for my sheet, to make it move right by default instead of down. The problem is, this code affects Excel permanently even in other files and persists through Excel restarts, until I manually run the code again to toggle it off. It looks like this:code:
|
|
#
?
Oct 13, 2022 10:31
|
|
|
AG3 posted:I'm using a VBA code I googled to alter the enter key behavior for my sheet, to make it move right by default instead of down. The problem is, this code affects Excel permanently even in other files and persists through Excel restarts, until I manually run the code again to toggle it off. It looks like this: I'm looking it up, but you're applying your change to the application object (excel) when it sounds like you only want to apply it to she worksheet, which I don't think you can do. Basically you're changing a setting in excel which is why it applies to your other sheets. It's not actually a change to the workbook, but to excel itself. I also don't think you can use macros to tap into .net events (Ie, When the application closes, do something) But I also think you can use windows built in language hotkeys? e: Yup, this can only be done to applications: https://learn.microsoft.com/en-us/office/vba/api/excel.application.moveafterreturndirection e: Oh! You can have macros run at prescribed events as documented here oh and there's event handlers too! Ok, give me some time and I think I can do this to you. If you want to do it yourself read this: https://www.exceltip.com/events-in-vba/workbook-events-using-vba-in-microsoft-excel.html Looks like you can just slap these into your workbook. I wonder if this means you can have a macro which then just updates these handlers for you too? EEE: Use the activate and deactivate events, imo. This will change the setting every time you enter the sheet and again when you leave it HootTheOwl fucked around with this message at 13:41 on Oct 13, 2022 |
|
#
?
Oct 13, 2022 13:25
|
|
|
AG3 posted:I'm using a VBA code I googled to alter the enter key behavior for my sheet, to make it move right by default instead of down. The problem is, this code affects Excel permanently even in other files and persists through Excel restarts, until I manually run the code again to toggle it off. It looks like this: Another solution might be to use autohotkey? Catch the enter keypress and change it to a tab? You can get fancy with that by having it run in the background and only be active when that specific Excel sheet is open
|
|
#
?
Oct 13, 2022 16:03
|
|
|
The easiest way would be to just put it in workbook activate/deactiviate Put this in the ThisWorkbook module Private Sub Workbook_Activate() Application.MoveAfterReturnDirection = xlToRight End Sub Private Sub Workbook_DeActivate() Application.MoveAfterReturnDirection = xlDown End Sub This way, whenever you click onto that workbook it'll change it to the right direction. When you click into another workbook, it'll change it to down.
|
|
#
?
Oct 13, 2022 17:10
|
|
|
Problem: Someone spit out a giant spreadsheet that I'm now responsible for and many of the cells in one particular column have duplilcate data in the same cell. Example:
The original CSV file exports with the same issue every time according to the person who feeds me the file. Obviously, they should fix their software but, the point is, I have to work with what I'm given for now. Solution I tried: I tried moving the column to a new tab and then I tried using text to columns with a line break as the delimiter (Select "Other" as the delimiter then click the box, then hit Alt+J) but Excel keeps the "3" in the original column and deletes all the other data without moving it into the next columns and I have no idea why so I have yet another problem  
|
|
#
?
Oct 14, 2022 03:04
|
|
|
Can you try putting the text formated as you'd like it and then press Ctrl+e (it should be flash fill but it's only available in the newer versions of excel)
|
|
#
?
Oct 14, 2022 03:27
|
|
|
Add the CSV file as a data source (Power Query > From File > From Text/CSV), ignore whatever happens in the first dialog and hit the Edit button to open up the PQ editor. Then on the far right-hand side, click the gear icon to the right of the Source step on this new query and play around with the Line Breaks and Delimiter options. You've kinda already done this so it may not work either. The Transform > Split Column > By Delimiter option may be useful too.
|
|
#
?
Oct 14, 2022 03:43
|
|
|
I would start with something like =left(b2, len(b2)/4) If the data is more complicated than that I would find the point where the first line is repeated again (i.e. a new line followed by the first line of text) and extract the lines before that. =LEFT(B2, FIND(CHAR(10)&LEFT(B2,FIND(CHAR(10),B2)),B2)-1) Also it's ctrl+J not alt+J for a new line character, so that may have been the issue.
|
|
#
?
Oct 14, 2022 03:59
|
|
|
I’ve done something similar before but had to use code/vba because I couldn’t find a solution that worked in the sheet. I had to google this because I couldn’t remember the steps but pretty much the accepted answer on this. https://stackoverflow.com/questions/54929316/how-to-delete-duplicate-words-within-a-cell Then you call that as a formula with your line break as the optional delimiter, usually either char (10) or char (13) =RemoveDuplicateWords(A1, char (10)) —- Really odd that the Awful app won’t preview post or post if I use char (10) without the space before the brackets… assume it’s the app but could be the site though.
|
|
#
?
Oct 14, 2022 04:07
|
|
|
Harminoff posted:The easiest way would be to just put it in workbook activate/deactiviate Thank you, this works great! Several of the other solutions would've been fine for me personally, I didn't even mind the way it worked originally, but the worksheet I'm making is going to be used by people who can charitably be described as "not tech savvy". I need anything that can make this thing work by itself and not cause more tech support work for me.
|
|
#
?
Oct 14, 2022 07:49
|
|
|
I have a table of data with the following structure with data about ~1000 people. For each person, the first row has their year, forename and surname. But the other rows for that person are just their class name. What I would like to do is shorten this down so it just tells their information about a specific class. If I filter blank rows out, I'd get the names but the class code would be incorrect. If I filter by just specific class codes, the year/forename/surname would be mainly blank. Manually adding the year/forename/surname to the blanks would allow me to filter but that's incredibly tedious. Best solution?
|
|
#
?
Oct 25, 2022 09:20
|
|
|
Sad Panda posted:
Here's a quick way to fill in all the blanks: ctrl+a, ctrl+g, "special," "blanks," "=A1," ctrl+enter Edit: "=A1" should be switched out for the cell above your first blank, so "=A2" in this example. Chickpea Roar fucked around with this message at 10:39 on Oct 25, 2022 |
|
#
?
Oct 25, 2022 10:14
|
|
|
Chickpea Roar posted:Here's a quick way to fill in all the blanks: That’s the best solution, then you just copy-paste values on the whole table / those columns and you’re home free
|
|
#
?
Oct 25, 2022 10:28
|
|
|
DRINK ME posted:That’s the best solution, then you just copy-paste values on the whole table / those columns and you’re home free What do you mean? You shouldn't have to do any copy/pasting, unless I've misunderstood something about the problem.
|
|
#
?
Oct 25, 2022 10:43
|
|
|
Definitely not mandatory but speaking from personal experience somewhere down the line the data gets sorted and the first user will suddenly have 100 marks for a class because the order has changed and all your =above are looking at the wrong value.
|
|
#
?
Oct 25, 2022 11:14
|
|
|
Chickpea Roar posted:What do you mean? You shouldn't have to do any copy/pasting, unless I've misunderstood something about the problem. When they said copy past the values, they meant to strip out the formulas for sorting. E: Sorry I hadn't had my morning coffee yet and posted this because it took me a minute to understand the connection between what DRINK ME was saying in their two posts. They already said this.
|
|
#
?
Oct 25, 2022 12:40
|
|
|
Can I ask a Google Sheets question here? I've got two columns, A & B. Both have drop-down menus with YES or NO. Can I use conditional formatting to colour B red only when A=YES and B=NO ?
|
|
#
?
Oct 25, 2022 14:20
|
|
|
it dont matter posted:Can I ask a Google Sheets question here? Yes. In the conditional formula menu, choose "Custom Formula Is" and set it to something like: =and((A2="YES"),(B2="NO")) And the range should be your data in Column B. The custom formula you use should be the relevant formula for the top left cell of the conditional formatting range - the formula I wrote above was if cell B2 is the top of your data range. esquilax fucked around with this message at 14:37 on Oct 25, 2022 |
|
#
?
Oct 25, 2022 14:34
|
|
|
esquilax posted:Yes. Yes that worked, thank you. What if I also want B to turn red if it's empty? Would that be something with ISBLANK ?
|
|
#
?
Oct 25, 2022 15:01
|
|
|
it dont matter posted:Yes that worked, thank you. Have it be red by default and only turn green when it's yes
|
|
#
?
Oct 25, 2022 15:20
|
|
|
it dont matter posted:Yes that worked, thank you. You can use ISBLANK(B2) or something like: B2="" You could implement that either by rewriting the first conditional formula to account for both No and [blank], or add a second conditional formatting formula.
|
|
#
?
Oct 25, 2022 15:23
|
|
|
That's great, thanks everyone.
|
|
#
?
Oct 25, 2022 15:56
|
|
|
I have a sheet that is being sent to a variety of people to fill out with inventory items, and I want to have a second sheet that can collect the data from all the copies of the first. I realize this is a bit on the "using excel when you should have a proper database" spectrum, but I'm pretty much just working within the limits of what my IT department will let me do. Is there a way to set up the data import tools to have a pre-set range that they're querying, and put them in a sheet in a way that doesn't overwrite the data that's been imported previously? Or at least be conditional so that if the location is X, that data is saved in column X, etc.? I'm basically trying to semi-idiot proof it for my manager. For bonus difficulty, our corporate license is for Office 2016, so running it on that is ideal.
|
|
#
?
Oct 25, 2022 17:48
|
|
|
How many recipients do you have that need to fill out the form? If it's a manageable number, consider making a folder with N copies of the blank form, all named after the recipients, and ask them each to fill out their file in that folder. You can then have the master workbook use an append query to collect from all the individual workbook files and present in a single table. By having all the individual files pre-created you can prepare the master workbook ahead of time. This is of course assuming it isn't a problem that all the recipients might be able to see and mess with each others' files. And also that they aren't idiots who can't follow instructions and maybe instead make a copy of the file to their desktop, edits that file, and then emails it to someone.
|
|
#
?
Oct 25, 2022 18:34
|
|
|
It's ~30 different branches, with all different levels of ability, so I'll probably just set up a folder for my manager to use, and fill it with blank forms for the time being.
|
|
#
?
Oct 25, 2022 19:10
|
|
|
Chickpea Roar posted:Here's a quick way to fill in all the blanks: Thank you so much. That was absolutely perfect. Never knew that was possible. Saved me so much time.
|
|
#
?
Oct 25, 2022 21:08
|
|
|
C-Euro posted:Can you define the values that populate the drop-down list without needing to reference cells elsewhere in the sheet? That appears to be my only way of getting more than one value onto the list. If you want to get super-fancy you can use Tables and INDIRECT so you only have to define the range once, and it'll update automatically if you add anything to the list of acceptable values. You'll still need a range in the spreadsheet for the list though. If you have a Table named Options with a column named Choices with all your list values, you can go to Data Validation on a cell and set it to " =INDIRECT("Options[Choices]") " Modifying the table will modify the pull-down list, so you never need to worry it's grabbing the right range. Nice for adding a quick value to the end, or avoiding extra blanks when deleting unnecessary ones. Plus you can sort the column! With a little more fuckery you can change which table you reference and make dynamically-allocated pull-down lists! Hughmoris posted:Any M-code gurus out there using it daily? If so, your thoughts? I've recently started exploring Power Query inside Power BI, seems like a fun little language. Not daily but I used it a bunch for a custom project, it's a fun way to attack some problems sideways For example Wandering Orange posted:Add the CSV file as a data source (Power Query > From File > From Text/CSV), ignore whatever happens in the first dialog and hit the Edit button to open up the PQ editor. Then on the far right-hand side, click the gear icon to the right of the Source step on this new query and play around with the Line Breaks and Delimiter options. You've kinda already done this so it may not work either. The Transform > Split Column > By Delimiter option may be useful too.
|
|
#
?
Oct 26, 2022 01:15
|
|
|
A Real Happy Camper posted:I have a sheet that is being sent to a variety of people to fill out with inventory items, and I want to have a second sheet that can collect the data from all the copies of the first. A Real Happy Camper posted:It's ~30 different branches, with all different levels of ability, so I'll probably just set up a folder for my manager to use, and fill it with blank forms for the time being. I'm gonna pimp Power Query again as this is another great use case for it that doesn't require any coding to set up and would allow you an unlimited number of forms that are all compiled into one master file at the click of the 'refresh' button. The only requirement is that you all have access to SharePoint or OneDrive, including all of your branches, although technically a network shared folder would work too...
|
|
#
?
Oct 26, 2022 04:09
|
|
|
DRINK ME posted:Definitely not mandatory but speaking from personal experience somewhere down the line the data gets sorted and the first user will suddenly have 100 marks for a class because the order has changed and all your =above are looking at the wrong value. Thanks, that makes sense 👍
|
|
#
?
Oct 26, 2022 17:09
|
|
|
Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range? For example I want to put BobsFaveFoods in A1 and then use =SUMPRODUCT(COUNTIF(B2:B10,A1)) instead of =SUMPRODUCT(COUNTIF(B2:B10,BobsFaveFoods)) so that I can have this across a bunch of rows/tabs and change which named range I'm referencing on the fly if possible. Really stumped on this but probably just being stupid.
|
|
#
?
Nov 8, 2022 01:12
|
|
|
Jack the Lad posted:Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range? INDIRECT is probably the function you want, it can pass the value of a cell along as part of a formula
|
|
#
?
Nov 8, 2022 01:25
|
|
|
Jack the Lad posted:Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range? this sounds like a lambda.
|
|
#
?
Nov 8, 2022 01:37
|
|
|
Jack the Lad posted:Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range? Using INDIRECT and naming cells will probably do what you want
|
|
#
?
Nov 8, 2022 01:40
|
|
|
Jack the Lad posted:Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range? a little more info on the INDIRECT function: I set up a basic sheet and A1 to BobsFaveFoods and set the named range to B2:B10, the original formula functions as intended, but it also works as such: code: You can even get really fancy and create formulas from multiple indirect cell references like this:  e: in your case you might want to use something like code:Lib and let die fucked around with this message at 03:02 on Nov 8, 2022 |
|
#
?
Nov 8, 2022 02:43
|
|
|
|
| # ? Apr 26, 2024 03:32 |
|
|
Awesome, thank you folks! I feel dumb (but glad) that it was so simple.
|
|
#
?
Nov 8, 2022 03:08
|
|