creating a table/new work book that filters data

[Amber1023]

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

[#2]

@Amber1023 

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. 

[#3]

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.


we are try8ing to move each "section" of this sheet to its own page then have a sheet for each year with each "location" on a new page and then we want to be able to sort the data based on different variables when we need to and keep building new years onto it as we do this job a few times a year and need to be able to review previous notes/info from the last times we did it etc

please help lmk if theres a better way (we also need to be able to print this for our guys in the field to use as they dont have tech in the field and they will then handwrite new notes for the day and it will need to be added to the sheet for that year(hopefully microsoft lens would be able to read the handwritten notes on a blank sheet exactly the same as the workbook and insert into the corrrect columns if we scan it in? or do you know of a way to do this?)

[#4]

@Amber1023 

[#5]

@Amber1023 

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.

[#6]

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

Do you mean data clean or ETL(extract transfer and loading)?
I guess this need more efforts than you think of.

In another words, no build-in tool to accomplish it.

[#7]

@mathetes 

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  

[#8]

@peiyezhu 

im not sure what data clean is or  ETL(extract transfer and loading)?

[#9]

how do i put each set of data on a new page on the same sheet so I can print them individually

[#10]

@Amber1023 

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.

[#11]

we need to be able to sort the data when needed, by location, by date
we also need to keep an expandable historical sheet that gets the new info added to it with the correct totals.
as we need to send in data at the end of the week of what was done that week
but my guys in the field need to be able to view prior data from when they did that same area before to read the notes to see if they had any problems/how they did it etc

not sure if we need to use query for it
append the data etc

we just need to be able to have previous years data easily viewable and the ability to add new data in and have it updated
we could do sheets by the year, by the job location,
we just tried making a page of each location but we are also unsure if they will work correctly with the expanding historical data

i wish there was a 1800 number to call because its not a simple task we just need to find the correct way to do it with formulas.

[#12]

@mathetes 


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

[#13]

@Amber1023 

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. 

• The FILTER function itself, first of all, requires Excel 2021 or newer (a subscription to Microsoft 365 works too)
• You will benefit from cleaning up the data, making it into a valid Excel table. Such things as
  • consolidating the two data sheets into one (it's not clear why there are two)
  • eliminating blank rows
  • making sure that locations are entered exactly the same (I suggest Data Validation be used) 
    • right now, for example, "West Pond" appears twice in the list of locations; that's because one of them is "West Pond" while the other is "West Pond "  -- a space following, invisible to the human eye, but from Excel's point of view, that's a different location.
• Right now the FILTER formula (you'll find it in cell F2 of the "Filtered Data" tab; all of the rest of the data "spills" from that single cell) only uses a single criterion, that being location. It would be possible to fine-tune that so it selected, for example, a range of dates, or a selected type of Jetter Equipment. There's a lot of flexibility.

[#14]

so i did some research and created a plan can you please let me know if this is a correct way of doing this and if theres anything else i need to do or help me get this figured out

I am going to make a new workbook that will be a running working copy and then create a master copy that will keep all historical data & use a macro to automatically sync and to the master copy going forward.

1.) find previous years sheets that have data i need in my new workbook
-create a sheet for each year of data and then in each worksheet create a table with the historical data from that year
-then create a separate worksheet that will become my master copy that will combine all historical data from each year
-sort it by location and then date to properly input it into my new workbook that will be organized based on location in alphabetical order making all data that will be on one long flowing sheet
-going to input using from other sources/get external data/from workbook (then possibly use formulas (index/match or ?? ) to transfer it to the new table i create for this sheet to contain all historical data
-then i will use power query to transform the data into the new sheet unless there's a better option
-add in formulas that will calculate each area location by the area, and then a running calculation by the job for that area and possibly a total by year for that area
-create a master and "working" copy so the guys can add in a new row when they need to add in data and add formula to automate transferring the historical data to be synced to the master copy which stores all data and is not altered using (macros?)

i was told to use structured references or dynamic ranges, absolute cell references to keep formulas accurate and up to date



Please let me know if this sounds correct or where i am wrong and if thats the order of doing this task

[#15]

@Amber1023

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

• a clean and consistent set of data. That's at what we might call the INPUT end of things.
• It's also crucial that you (your organization) be clear on what the expected OUTPUT is, i.e., the reports or results from all that data.

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.