Forum Widgets
Latest Discussions
Dynamic Arrays - Extend the last logical row value in a 2D array
One more for experts. Again, with my new found knowledge I still was not quite able to make this work. Simple balance sheet values that I need to extend into the forecasts as is. i.e. the last actual value in the row of that block - BUT, the block has several rows each for a different entity and I have an option for have staggered actuals based on the data loaded. i.e. if Entity A has values to 30th Sep 2024 then this is the latest value to carry forward for Entity A. But entity B may only have actuals up to 31st August so I need to take the value for August to carry forward. I prefer to use flags for the logic. Sample attached Appreciate the helpSolvedJames_BuistNov 07, 2024Brass Contributor262Views0likes10CommentsHow to Append Tables with Different Column Order with Automatic Refresh
There are 4 tables that I am wanting to append together, though their columns aren't in the same order (even some with more columns than other) that need to be in one table and have the ability to automatically refresh them when I update the data each month. Can this be done? If so, tips and tricks would be greatly appreciated. The outcome I am looking for is: - Tables all appended with the correct columns all lining up - Automatically refresh when new data is pasted into the data tableslankyman22Nov 07, 2024Occasional Reader17Views0likes5CommentsPower 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 07, 2024Brass Contributor359Views0likes18CommentsLine Graph Should Ignore Blank Cells
Greetings, I have an Excel line graph that doesn't seem to be working properly and I'm not sure why. It's acting more like a bug with the Excel file itself - or perhaps the Excel program - than a problem with my graphs and formulas. Click here for my test sheet in Google Drive. My data has a number of blank cells. On the graph, I want any blank cells to just not plot anything. The file is saved in SharePoint/OneDrive. When I open the file from my desktop computer using the local Excel App - not the web app - the blanks display as zero... When I open the file on the browser - not the local Excel App - it does the same thing... When I saved the file as a .xlsx file in Google Drive to share it in this forum post, the file ignores the blanks when it ?opens in Google Sheets?. This is what I want! Here are my settings for Hidden and Empty Cells... I can't get it to work in Excel on the Desktop App which is where I'll be using the file 100% of the time. Any ideas what I'm missing?Flopbot2Nov 07, 2024Occasional Reader10Views0likes1CommentConvert Number to Words LAMBDA (Very Large Numbers)
I created this LAMBDA Function "Number_To_Words" in order to convert Numbers to Words (eg. 2813 can be written as Two Thousand Eight Hundred Thirteen in words) First Parameter of the function is Number and second is Optional (By default Indian No. System, 2 for International No. System, 1 for Indian) It can convert up to 100 Trillion (International Number System) and Lakhs of Crores (Indian Number System). Avoided conversion after decimal for now but that can be implemented as well. If Number has decimal, function will Round it first and then give results. YT Video Link -https://www.youtube.com/watch?v=u1gzAcwmlpo Gist (GitHub) Link -https://gist.github.com/Bhavya2502/8413a0e6af783ad18e72419eca47ad09 What are your thoughts on this ? PeterBartholomew1SergeiBaklanBhavya250203Nov 07, 2024Copper Contributor5.5KViews1like12CommentsFilter function
Dear Experts, Greetings! I have a data like below:- Each row has two rnti's (Col-C and Col-D), and their corresponding numofPrb are in Col-F and Col-G, I want to prepare something like Col-J and K, for the rnti's with their corresponding numOfPrbs in a single column. Thanks in Advance, Br, AnupamSolvedanupambit1797Nov 07, 2024Iron Contributor15Views0likes1CommentInput 1 to 7 SKU numbers to return groups of rows.
My actual document has 300 cases of product listed in rows, I trimmed it down to 20 cases of product for this example and replaced the names for confidentiality. I want to be able to input these three components 9800006353, 9800006357, 9800006356 and return rows 3-6, 12-15, 24-27. Because the Case SKU in column A has more than just those three components. I want to be able to do this with any of the hundred or so components exist and then return the Case SKU with all of the Component rows. First image is what it would like to start with, then I would input the three components 9800006353, 9800006357, 9800006356 and the second image is what I am looking for. Second image below.robodan70Nov 07, 2024Occasional Reader17Views0likes1CommentExcel copy and paste error
Usually, when I copy something in Excel, it highlights the cell and keeps it highlighted until I finish pasting it on the Excel or if I copy something else, even on another app or browser. But now when I copy something from Excel, paste it on the browser (URL bar), copy something else from the opened page, and then try to paste the browser data back to Excel, the initially copied cell is still highlighted, and it pastes that data only, not the browser data. I have to then paste the browser data from the clipboard. Can someone confirm this issue or suggest a solution?Prince20Nov 07, 2024Copper Contributor939Views0likes3CommentsExcel Copy & Paste / Clipboard
When I copy one or multiple cells from excel, and then go over to another program on my computer and use the "copy" function again, and then go BACK to excel, and paste in a different cell - the original copy from excel is the one that pastes. It's almost like the second copy function from another program on my computer does not slate clean the clipboard - and almost like the excel has its own clipboard that separates itself from the rest of the computer. If i copy a cell in excel, and then copy other stuff from a different program (non microsoft, microsoft, anything really) I want the original excel copy to CLEAR. This is something new and randomly starting occurring over the past year or so to me and all my colleagues. This problem is most prevalent with Bloomberg for me fyi. Thanks for any help!thebearjewyNov 07, 2024Copper Contributor58KViews1like2CommentsTracking attendance at training using Polls on MS Teams and Excel
I am trying to establish what players are A. at training, B. not there and C. are there but not participating. I want to organise and track this data using Excel. If I post a Poll on MS Teams, I can download the result for each player through an excel document. It is displayed in the following format: Order of response Player name Poll Response 1 Player A Yes 2 Player B Yes 3 Player C No 4 Player D Yes but injured 5 Player E No 6 Player F No 7 Player G Yes but injured 8 Player H Yes 9 Player I Yes 10 Player J Yes 11 Player K No The first thing that I want to do is to arrange the names in a list according to all the A. yes, B. No, C. There but injured. The second thing that I want to do is: There are 45 player on the panel. If only 30 of those respond to the poll, there is no quick way to see who has and has not responded. Is there a way to have a set list. Import the above data from the poll. Automatically fill in Option D. No Contact if there is no data for their name? The third thing that I want to do is to track this information over the course of say maybe 30 training sessions. That would be one sheet with a row for each player name their attendance for each sessionkellym385Nov 07, 2024Copper Contributor11Views0likes1Comment
Resources
Tags
- Excel41,522 Topics
- Formulas and Functions24,034 Topics
- Macros and VBA6,238 Topics
- Office 3655,787 Topics
- Excel on Mac2,568 Topics
- BI & Data Analysis2,268 Topics
- Excel for web1,833 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,575 Topics