Issue of irregular data, from multiple sources I have no control over
Our take
So, in my working environment, I'm usually tasked with making some sheets for specific needs that take as input data from up to 10 different sheets managed by different people in different ways, we're talking all manual formatting, different everything mostly, albeit very basic with close to no actual calculations, and of course no usage of advanced instruments.
My issue is - I always have to make uniform data from all that mess and then do my task, the first part takes 80% of time actually, so I wonder how I can automate that.
Examples of issues being, like, data written in different formats, same fields related to one person having slightly different data in nature or form in different sheets, namings not up to standards etc etc.
I absolutely can't influence other actors in any way.
I'm not sure how to tackle that issue mainly because I will need to somehow implement safeguads which will somehow track data conflicts, overlaps, duplication and such.
And second part is to somehow taking into account all types of slightly different ways data is represented among one type of data.
Will be grateful for any insights into how you deal with such chaos without ability to influence other people work processes.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What repetitive data tasks are you still doing manually?Lately I've been working a lot with CSV files from different sources (banks, exports, random tools), and I keep running into the same issue: - inconsistent column names - messy date formats - duplicate / empty rows I end up fixing things manually more often than I’d like, even though I know it should be automatable. I’ve tried Power Query and some scripts, but it still feels like there are always edge cases that break the flow. Curious — what’s a repetitive data task you still do manually even though you know it shouldn’t be? submitted by /u/CodigoSinBugs [link] [comments]
- What’s the most frustrating part of cleaning messy Excel/CSV data?I’ve been working with a lot of messy spreadsheets lately (duplicates, inconsistent formatting, mismatched columns, etc.), and it feels like everyone runs into slightly different issues depending on their data. Some people rely on Power Query, while others do things manually, but I still see workflows break when the data isn’t consistent to begin with. Curious what tends to slow you down the most when cleaning or organizing data? Is it duplicates, formatting issues, inconsistent columns, or something else? submitted by /u/SmitleyData [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]
- How many people typically collaborate on complex Excel files? How do you prevent overwriting each other’s changes?In my line of work, complex Excel files (financial models, budgets, business analyses, quotes, project tracking, etc.) are rarely created by a single person. For example, the workflow might look like this: One person maintains the source data One person modifying formulas or the model One person creating charts and presentations One person reviewing the results Multiple people simultaneously editing different sheets or sections But this often leads to: Files being sent back and forth, ending up as “final_v7_really_final.xlsx” No one knowing who changed which formula Two people editing the same sheet, forcing a manual merge A cell being overwritten and not noticed until much later Difficulty comparing “which version is better” Difficulty reverting changes after an error I’m curious about everyone’s actual workflows: How many people typically work together to maintain a complex Excel file? What are the usual responsibilities of each person? How do you avoid overwriting each other’s changes? Has anyone tried version control, comparison, rollback, or even merging for Excel, similar to Git? submitted by /u/qqwwbb [link] [comments]