Forum Widgets
Latest Discussions
Formula with multiple conditions
Hello!! I am not tech savvy and need help creating an Excel formula to lighten my workload. Here is what I want to do: whatever the beginning ticket number sequence is in B2 would populate C2 as corresponding truck number. I would do this in cells B2-130 and C2-130. Is this possible and if so, can someone please help me? I would appreciate it so much! Thank you If Cell B has 295414 as the 1st sequence of numbers, then Cell C=295414 If Cell B has 295415 as the 1st sequence of numbers, then Cell C=295415 If Cell B has 541894 as the 1st sequence of numbers, then Cell C=541894 If Cell B has 541895 as the 1st sequence of numbers, then Cell C=541895 Is this possible and can someone please help me out? I would appreciate it so much!! Thank you! ChantelChantel6576Nov 06, 2024Copper Contributor4Views0likes0CommentsPower Query - removing identical generic text from the start and end of multiple columns
Hi all. I am after a hand in removing some default system values from a Jira export that I am using to build a report around. When multiple values are recorded in the system, a new column is created in the export and number is appended to any duplicate columns example"Label" and "Label2". I previously received assistance in understanding the code to merge the values in these columns and rename them, thank youLorenzo.For any columns that have required merging, I have been able to address their naming as part of the process, but I have multiple columns whose headers still require attention and can be handled as part of a grouped transformation process due to each containing identical redundant values. When a custom field is created, Jira wraps the value with "Custom field (" and ")", for example "Custom field (Country of Origin)". Some of the headers have valid values contained within brackets, so I can't simply replace all brackets - I have tried using the"Custom field (" as a string to identify relevant headers to useText.Start(_,Text.Length(_)-1) to get rid of the trailing bracket andthen Text.End(_,Text.Length(_)-14) to remove the leading text... but ended up with a list of correct headers but no data. Clearly out of my depth but felt frustratingly close. I also attempted to useList.Transform(Table.ColumnNames( )and removed the leading text and bracket... but then couldn't get rid of the trailing bracket. I had a fair crack at it today but haven't managed to get it to do what want and now my brain hurts, so have created a very simplified version of what I am trying to do and am seeking your help. In the Output tab of the sample file, merging of columns has been completed and I would like the columns Custom field (Weight), Custom field (Organic (Y/N)), and Custom field (Multipack (Y/N)) transformed into Weight, Organic (Y/N), and Multipack (Y/N). I am after a solution that searches for and replaces text in the headers rather than explicitly referencing column names as the export's contents are variable and dynamic in nature, and there are far more of them. Thanks in advance🙂🤗Insert_KeyNov 04, 2024Brass Contributor309Views0likes10CommentsDouble Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n(not just a count of options =COMBIN(n,m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task. Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array. In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right. So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row. By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done! p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960sPeterBartholomew1Nov 04, 2024Silver Contributor990Views2likes19CommentsCheck Boxes
I have been patiently waiting for the checkbox tab to appear on my excel application. As of today, 11/4/2024, it is still not showing up. I have Office 365, and my Version 2404 (Build 17531.20140 Click-to-Run) is what I am showing. When I click on Update Now, nothing happens. When I clickView Updates, it takes me to the current changes page informing me "We fixed an issue where some users couldn't see checkboxes getting rendered after inserting them.", but it isn't even populating on my ribbon to view. I pay for Office 365 on two different accounts and I am not showing it on either account. If someone could walk me through getting this to populate on my ribbon, I would greatly appreciate it.Joe_SargentNov 04, 2024Occasional Reader53Views0likes1CommentFilter results into 2 columns
Hi all, I think this should be relatively straight forward, but can't find a simple solution...My data has 2 columns, a week of the year and a 'score' I need to create a spill result (so I can generate a dynamic chart), which separates the result of a filter into 3 columns: Column1: Week Column2: Scores >50 Column 3: Scores <50 getting the three separate columns is not an issue, but it's combing them into a spill result that I can't fathom - using HSTACK with three FILTE functions means the scores/weeks do not align. Any help much appreciatedSolvedrtayerstNov 04, 2024Occasional Reader123Views0likes4CommentsHow do I make text to columns automated?
Hi there - I will be doing some reporting and have one column with some data separated by commas, I need to separate the data into columns and then make that rule apply to any further data I add to the end of the list so that I don't have to manually convert text to columns each time I add more data into the column with the combined data. Is there a way of doing this? Please help!! ShakehShakeh93Nov 04, 2024Copper Contributor77KViews0likes11CommentsPosting a date based on an event
Hello Forum, Does any one know of a way thru a formula to post a date into a cell based on an event happening on other sheet. I have a sheet that makes a random selection of people from my roster whereby I copy the selection and paste the values into cells on the same sheet in a different column. The thing is however, I have another Sheet in the same workbook that I need to have the selection date, the paste event date, based on the selection of the individual(s), placed/posted/dropped into the appropriate column/cell as applicable. Can anyone help me out with this? CarlCarl_61Nov 04, 2024Iron Contributor59Views0likes0CommentsUsing Find & Replace to insert line breaks (CTRL+J) erases cell contents
Hi, First of all thank you for reading my post, I am kind of desperate right now LOL. I had to paste English text from Excel into Word in order to translate it. The Excel text is really long and has lots, and lots of line breaks. So when I pasted into Word the format was maintained with regards to the number of cells and the line breaks. But now when I paste this translated Word text back into Excel each line break is generating a different cell. In the past I've used the CTRL+J solution, involving replacing the line breaks in Word with a character that doesn't appear elsewhere in the text (I've used ****), pasting this into Excel, and then using Find & Replace in Excel to find all of the **** and replace with a line break, which used to be CTRL+J, but now this is just erasing all of the cell contents. I don't know what to do, does anyone have any idea about why this is happening? This used to work! Thanks so much in advance. Lulululu01Nov 04, 2024Copper Contributor54KViews0likes8CommentsAllocating manhours to a month-to-month from a construction schedule
Good day, I need assistance with separating manhours by month from a construction schedule. The schedule includes a start date, a finish date, and the total man-hours for the duration of each activity. I want to divide these manhours among the specified months for each activity so that I can create a Bell Curve for manhours for each month of the year. While this can be accomplished using the Excel Macros Developer program, I am unsure how to perform the extraction and separation correctly. Could someone please help me with this process? Thank you! PS: The full version has over 2,000 activities Start Finish Manhours 22 February 2026 17 May 2026 1871 11 March 2026 23 May 2026 250 17 December 2026 25 February 2027 5988 23 May 2026 08 August 2026 4790 08 August 2026 25 August 2026 250 25 August 2026 30 September 2026 1248 30 September 2026 10 October 2026 524 10 October 2026 14 November 2026 2994 21 July 2026 30 September 2026 5988pelserbenNov 04, 2024Occasional Reader157Views0likes5CommentsFunction to return the number of columns which contain information
Hi currently I am using google sheet. I need a function to return the number of columns which contain information (text or number) as in the attached file.is it possible to do that? Thanksajl_ahmedNov 04, 2024Iron Contributor218Views0likes8Comments
Resources
Tags
- Excel41,503 Topics
- Formulas and Functions24,020 Topics
- Macros and VBA6,235 Topics
- Office 3655,783 Topics
- Excel on Mac2,568 Topics
- BI & Data Analysis2,267 Topics
- Excel for web1,829 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,574 Topics