•3 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Formula to achieve more complex text to columns?
Our take
Are you looking to streamline your data management process in Excel? In this discussion, we explore a solution for automating the conversion of productivity tracking data into a structured format. With employees sharing varied status updates in a central Teams chat, consistency is a challenge. By leveraging a formula that effectively uses text-to-columns—despite the inconsistencies in formatting—we can transform raw data into a table that feeds directly into a chart. Let’s uncover innovative strategies to enhance efficiency without overburdening your team.
Hi everyone! I'm back with my latest question in my journey to maximize excel's usefulness in my office!
Currently, I'm trying to figure out how to autofill a table and from there auto convert to a chart.
To explain: The spreadsheet is tracking productivity goals for our employees (specifically the goals are to move x amount of clients into different statuses each month). As the employees complete these tasks, they are posting a message in a central teams chat with the information status, client name, client number, caseload, and the date. Once a week, management goes through the chat and copies and pastes the information into a spreadsheet broken up by employee and status.
Every month, they use the data from that sheet to fill in a chart that counts them and calculates percentage of the goals met.
Management asked me to find a way to take the monthly task of filling in the chart off their hands by making that automated. The way I was going to do this was to convert their sheet into a table and then use the table to create the chart. However, upon opening the spreadsheet, I see how much management has been just copy and pasting the raw data underneath each caseload's heading. I don't want to make more work for them by making them fill in the table, so I'm trying to find a way to automate this to. I thought about text to columns, but everyone's doing things slightly differently in their posts in the teams chat so that makes this a little difficult.
The status is pretty universally first with a dash between that and the name with is almost universally second. After that some people are not including separation between name and client number, some people are using commas, some people are using dashes, some people are using a pound sign, and some people are putting the leading zeros. So it's really messy. Obviously, I can ask management to set the expectation that this be uniform and they would be happy to do that. But I want to see if there's a way we can do this easily without changing the already existing process.
Does anyone have ideas?
Thank you for reading all of this and helping!!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to autofill data from a row to a column on a different sheet in the same folder?I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs. Hoping someone has the solution I'm looking for and is willing to share.. I've exported questionnaire results from Mentimeter to Excel. The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something. I would like to make this more user-friendly by: 1) putting each respondent's answers in their own sheet in the folder, and 2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'. Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3. The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on. What I'm looking for would ideally display answers in !AnswerA3 through to A80. When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want. When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem. I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that. Would anyone be able to provide me with the correct solution for doing this? I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally.... Thank you Excel wizards! :) submitted by /u/Lost_Mud2097 [link] [comments]
- Formatting question for automating data entryIm going to try to articulate what I need and if it’s possible to do inside excel. At my job I have to record the amount of patrons using our facilities. and specify what particular services are being used. at the end of each quarter. (3 month period) I must tally up all the numbers and provide a total for each aspect of our facility as well as the total overall. For example. 1st quarter numbers. 100 patrons used theatre. 250 patrons used Game room 450 patrons used computer lab so on and so forth. Now that you have the gist in your head. Imagine a spreadsheet where the first form is just a data entry sheet. it’s essentially just a box that never changes. You input the numbers for the week, and that data gets automatically moved to a different cell that has the total amount. so that at the end of the quarter I can easily see my total without having to backtrack or tediously add. if anyone has some insight on how I can do this Please reach out. If you have any questions about my wording or understanding exactly what I mean please also reach out. If you read all this I appreciate your time. submitted by /u/Beneficial-Yard-9006 [link] [comments]
- Creating an auto-populating visual calendar for multiple departments to view?I coordinate student schedules across multiple hospital units. I log the shifts in an hours log spreadsheet (date, start time, end time, unit, student name, etc.). Every month, I need to send each unit a schedule showing who is coming in and when so they can review and plan coverage. Right now, this part is very manual, and does not lend to tracking any data like how many students on each units, hours, etc., which we need as a department. We have over 100 students each month, so as much automizing as possible would be helpful as its a big process. Ideally, I’d like something that: Pulls directly from the hours log (no retyping or copy/paste) Is easy to read at a glance for unit managers Can handle many units, many months, and many students Lets me filter by unit + month I'm not really an excel genius, and I've been using copilot to help me with some of this and formulations, but It's not providing me with accurate formulas for the actual calendar conversion. If there are any resources on what formulas to use, what platforms that I could maybe look into for how to do this, or just honestly any advice at all. We've gotten the data tracking mostly drafted up and it's able to pull from pivot tables and show the analytics, but the calendar is my biggest hurdle at the moment. submitted by /u/JuggernautFlashy6489 [link] [comments]
- Pull static data from one sheet to use in a VLookup, then output static data + returned values? Can my process be improved?Hi all, I have a weekly process at work that I am hoping to improve, and preferably completely automate. With the help of this subreddit I have taken this process from hundreds of steps down to a few dozen, but I think more can be done – I am hoping for suggestions (and if automation seems possible, a point in the right direction as to where to start). I will do my best to describe the process without droning on, but there are a number of nuances which I feel may be important. Our largest client sends us a weekly census file for their retirees, which contains information (46 columns) that affect the type of materials I am responsible for sending, as well as the content of those materials, per household (retiree + any dependents). Each file can contain between 1 and 30 retirees. To complete my tasks, I use four separate excel files: The census file The “calculator,” which generates a contribution amount based whether the retiree (and their dependent(s), if applicable) are over or under 65, years of service and what division the employee worked in. It seems like the process is a series of IF formulas, paired with a VLOOKUP. A “premium payment tool” which essentially subtracts the contribution from the premium amounts and is formatted for output onto the master mailing list A master mailing list, which includes the retiree’s demographic info, plus their coverage options and individualized rates To say the process is convoluted is an understatement. Each household is one row on the census file – the client provides the ages and disability status of all household members. My current process: Copy/Paste the retiree’s: first and last name, DOB, SSN, address, and phone number. The order on the client’s file does not match the order that my company uses. I typically will copy/paste one column at a time for all rows in the census file, then complete the following steps one row at a time Determine the ages of the retiree, their spouse, and their children (if applicable). If all household members are in the same age group (Pre or Post-65) based on the date of retirement, only one entry on the Mailing file is required. If one household member is in a different age group than the other(s), two entries on the Mailing file are required. Additionally, if a household member has an “E” in the disability column of the file, they are treated as being Post-65 regardless of age. 3. Using the Calculator, determine the employee's monthly contribution amount based on service years, group, and section type. The process is repeated if the employee has a spouse, and again if they have a child dependent (so from 1-3 times per employee). 4. Copy the monthly contribution amount for each household member into the Premium Payment tool – there are two sheets, one for Pre-65 and one for Post-65. Each sheet has cells for the Retiree, Spouse, and Child. The contribution amount is subtracted from the premium rates and formatted for output to the Mailing file. If there is no contribution amount under one household member, the output returns a “N/A” under that coverage. Ex: https://preview.redd.it/csqgmr9038sg1.jpg?width=975&format=pjpg&auto=webp&s=aa781f5373077cf354886b63ae99349d178281ed 5. Copy the “You Pay” amount onto the Mailing file Besides the time it takes to complete this process for larger files, the sheer amount of copy/pasting required leaves a lot of room for error. What I am working with now is infinitely better than what the girl before me was doing. I was able to add enough formulas to reduce the copy/pasting by about 75% but I suspect more can be done to automate things – I’m just not sure if it’s feasible solely in Excel. I’d love to hear what the community thinks, please let me know what additional info I can provide. Thank you all so much! submitted by /u/stina__saurus [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#enterprise data management#generative AI for data analysis#financial modeling with spreadsheets#natural language processing for spreadsheets#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet#spreadsheet API integration#conversational data analysis#Excel compatibility#real-time data collaboration