Forum Widgets
Latest Discussions
Power 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 08, 2024Brass Contributor397Views0likes21CommentsHelp with Formula
Hello, I have a book of spreadsheets where each sheet is a month starting with June 2024. Within each sheet Column A is "Date" consisting of daily dates (e.g., 07/01/2024) and then Column D is "Weekly Total (Direct)", which is the sum total of direct clinical hours (column B) for 5 rows of individual dates (i.e., a work week). Essentially what I want is to have a formula that goes through all the monthly sheets and gives me a single average value of the weekly total values (Column D) but only up to the current date so the average does not get diluted by the empty premade sheets for the future. I hope this makes sense, thanks!RileyRodemakerNov 08, 2024Copper Contributor133Views0likes2CommentsData organized in Columns instead of Rows - Excel
Hi, In excel, I have a long list of data that I want to organized in 4 columns (First Name, Last Name, Phone and Email), the thing is that the data I have is in one column, so like in the image, the data that I have is organized downwards in a single column, as you can see in A1 is Kevin's first and last name, in A2, is Kevin's phone number and in cell A3, his email, the same for John and Angela,how can I place the information in 4 columns like it is in the table on the right? Thanks!aledNov 08, 2024Occasional Reader28Views0likes4CommentsExcel rows disappeared
Hi all, I have a problem and would love to receive some help! I just deleted some rows in Excel. Since then, my spreadsheet has been drastically reduced. It stops at 88004 rows and won't show any further rows (all other sheets of my Excel file contain the usual row limit of 1.048.576. Even if I copy in data from another File in this sheet, it cuts it of at 88.004 and deletes any additional rows. Is there any way to fix this broken sheet or do I have to redo all my analysis and pivots that are based on this. Thanks! VeraVeraExcelNov 07, 2024Copper Contributor5.5KViews0likes4CommentsLine 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 Reader30Views0likes3CommentsAuto fill Department column
Good afternoon Microsoft Community, This is my first post ever with this site. I have the following project that I need help with. I need to auto fill the Department in column A in the Raw Data worksheet, with the DEPARTMENT name on worksheet Supervisor Drop-Down list. The Raw Data worksheet has Column F with the names of the current employees. I want a function/formula that will check for the names in the Supervisor's Drop-Down list and automatically add that department to Column A on the Raw Data worksheet. I have explore many different videos on the web and have not been able to find a solution. please advise on the best approach to handle this task.MartinDeLamoraNov 07, 2024Occasional Reader17Views0likes2CommentsFilter, Sortby...help with formula needed!!
I wish to create a table on Sheet 2 extracted from a table on Sheet 1. I only wish certain columns to be used, driven by whether Col 5 is populated. These will then need to be columnised into a different order and then sorted by e.g. Col 5. Is there a formula that i can use?StokieACNov 07, 2024Occasional Reader31Views0likes3CommentsCorrupted file information
I want to relate my experience and the good final outcome for the benefit of others I recently had a situation when modifying a fairly complex spreadsheet with after making changes on the next open I was faced with the "your file is damaged, repair it yes or no". If you say no the file just closes, so the only real answer is yes. In my situation the "repaired" file was beyond use. For example all of the Tables I had been using were no longer tables, and any formula columns in nthem were lost. I had two tables that were updated by power query downloads and these tables were then accessed by a number of pivot tables. The changes I was making involved some pretty complex formulas with sumifs, averageifs etc. So lots of possibilities that might cause this problem. One possible cause was a malfunction of my storage, or a MAC vs WIN issue. But using a WIN computer put that to rest. I then went through a stepwise process to make the changes, and saving after each type of change as a different file. On stage 9 I discovered the cause. I had a private sub worksheet_open() on the worksheet I was changing. It was there to Protect the worksheet when opened so casual users would not be able to mistakenly overwrite formulas. The final changes involved accessing a different worksheet. I became tired of unprotecting the worksheet every-time so I commented out the Protect VBA code. It was the only line in the worksheet_open. But I just commented out the one line of vba code, not the whole sub. That was the problem. Having the worksheet_open(), but then no active code was resulting in the file being flagged as needing repair. Of course if you make a coding problem when setting up vba like this you get an error code as you enter it. But if you comment out the one and only line of code in the workbook_open you will get this problem. I certainly would never have expected this to happen and it was only after I switched from "frustrated" to "investigative" mode that I was able to decipher it. I hope this helps someone because an extensive search of the internet gave me no clue in this direction.BryonKarrenNov 07, 2024Occasional Reader8Views0likes0CommentsSearching column cells for similar text values and summarising in another column
Hi Experts I hope you can help here. To be honest without Macros not sure this is possible with formulas? I attach an example workbook with my desired results from the data. Basically we have a support ticket system for users and can export the "summary" or "titles" for each ticket. We want to have a formula to analyse the column with all the ticket titles and produce a list of common similar themed topics, and the number of deemed occurrences. Now I know this can be done either looking for an exact text word match but the difficulty comes where I am hoping excel can attempt to categorise similar patterns of words / phrases / text. Any solution via formula or am I expecting too much? Thank you for your help!matt0020190Nov 07, 2024Brass Contributor13Views0likes0Comments
Resources
Tags
- Excel41,539 Topics
- Formulas and Functions24,041 Topics
- Macros and VBA6,240 Topics
- Office 3655,789 Topics
- Excel on Mac2,568 Topics
- BI & Data Analysis2,268 Topics
- Excel for web1,835 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,576 Topics