May 02 2023 08:40 AM
May 02 2023 08:40 AM
I need to create a new table based off the info from one year and add the same columns for this year so it can be easily seen the same sections year to year
i need this data in a new sheet or table form that i can then sort it by location, #s, etc
or a way to keep this info from previous years and then add to it this current year so it can easily be viewed what was done on the previous year etc and see everything
May 08 2023 12:21 PM - edited May 08 2023 12:21 PM
Amber -- how can I put this most gently? From that image it appears that your (or your organization's) main reason for using Excel in this instance is to take advantage of its rows and columns. Although I see three instances, in Row 21, where some addition appears to have taken place -- so you may have used the SUM function in those three instances -- you're not really using Excel for its abilities in summarizing and manipulating data. You could just as readily be using Microsoft Word with its tables. Excel's only advantage over Word is that it comes with as many cells as you need, all neatly arrayed from the get-go; yes, and it can do the occasional addition when that's needed.
There's nothing particularly wrong about that, but it's also (for that reason, I suspect) why more than 60 folks here have viewed your inquiry without responding: it's not really an Excel question, per se; at least not a question that looks to take advantage of the real strengths of Excel.
To the extent that you're using Excel for its rows and columns, for the clarity those present--which is fine, as noted--you can insert columns for different years, changing the headings to include the year of the data under it, hiding and/or revealing columns based on a particular question from management. Similarly, you can add rows as needed, and display or hide those.
Or you could copy the entire sheet, creating a new one, then delete all the content under the various headings, and fill that in for the new year. Then show them side-by-side.....
Now, if I'm missing something here, please come back and say so. If the spreadsheet behind the image you've shared is, in fact, taking advantage of other Excel-exclusive data manipulation features, please say so.
Finally, if doing so would not be a violation of proprietary data, consider posting a copy of the actual workbook on OneDrive or GoogleDrive, with a link pasted here that grants access to it.
May 08 2023 12:50 PM
May 08 2023 02:59 PM
That's a tall order, not one I'm equipped to handle. You say that the guys in the field don't have tech, which is kind of surprising. Do they have iPads? If they do, they would be able to use an iPad to enter directly into the spreadsheet. I'm not at all confident that the scanning of a handwritten document would be able to get the data cleanly into the sheet in the appropriate spot, etc.
But from here (seeing only the tip of the iceberg), it seems to me that you should be talking with a local IT person about the whole process of gathering and summarizing the data.......
As I suggested in my last post, if you are able to post a copy of the actual spreadsheet, so long as it's not violating any proprietary guidelines, I and others could take a look at it in more detail. Absent that, though, there's not a lot more to suggest.
May 08 2023 09:27 PM
May 09 2023 06:04 AM
ive attached a blank copy of the form that is filled out.
in the field they dont use tablets as they are generally covered in dirt, rainy weather and dont have the time to add it to the table and we like the paper copies.
just trying to make it easier at the end of the day and for me to get multiple years of data together
we are planning on making multiple pages within the sheet to move each location to so its only one per page
May 09 2023 06:05 AM
im not sure what data clean is or ETL(extract transfer and loading)?
May 09 2023 06:24 AM
May 09 2023 07:26 AM
how do i put each set of data on a new page on the same sheet so I can print them individually
There is a way to insert page breaks between rows, if that's what you're asking. Use the Insert menu at the top:
But I wonder, seriously, whether Excel is what you should (or need to) be using. As I said right off the bat, you're not really using Excel (or don't appear to be) for what Excel offers in the way of calculations, data manipulation, etc. You're using it for its array of rows and columns. It's basically a high-tech ledger sheet for you. (Unless, as also noted above, I'm missing some off-the-screen processes that haven't been discussed.)
Just to illustrate what I mean, I've created a Word document that has all the same columns. And using the Word Table layout, you get the same array of rows and columns. There's no limit to the amount of text that can be put in any cell in Word......
And pagination, although available in Excel, is "more natural" in Word. It's more readily apparent where and how to insert page breaks when needed. Granted, it would be trickier to get those few totals that you do have in your sample......
Again, if it's possible to post a copy of the actual workbook, so I and others (e.g., @peiyezhu ) could take a look at it, that might yield some more useful suggestions. You were able to post that PDF of the data gathering sheet, so it would appear that you are able to post the Excel workbook as well.
May 09 2023 08:12 AM
May 09 2023 08:52 AM
here is a copy of a previous years log record
we have since added onto it by sorting location alphabetically and we need to find a way to add previous years data to keep going historically or appened? so we can review previous years notes etc from that location and be able to print it out on a sheet for the guys in the field to easily view then a way to update the sheet for the day with new info after
May 09 2023 10:05 AM
The attached may be a step in the direction of what you've been wanting to do. I used the FILTER function to produce some output that takes all of the rows that pertain to a given location. In the yellow cell on the Filtered Data tab, you can select from the dropdown list any of the other locations to see how it could work. [If you can't see a big set of data extracted from your main data, that means you'd need to update/upgrade your software to be able to use this function.]
A couple of things need to be pointed out.
May 10 2023 09:14 AM
May 10 2023 09:34 AM
I'd have to say that I don't see anything glaringly wrong in what you've written, but (as the expression goes), "the devil is in the details." And neither you nor I can know in the abstract what those details are going to look like.
The key to doing anything along these lines with the data you're working with is having
Whether all of that is present in what you've written, I can't tell from a distance. So I go back to the notion that you may need to work with an expert IT consultant closer to you, hopefully a part of the organization.