Help on Combining Data from Multiple Worksheets into a Single Worksheet in Excel

Rujinh 

This has become a rather long answer. For that I apologize. It quite frankly looks to me as if you may be making the task harder than it needs to be, by doing some intermediate processing on the raw data before you combine it into the larger table from which you run your PivotTable and other summaries. Excel is really good at taking raw data, the rawest of raw data, and extracting and summarizing solid reports from that raw data. So wherever and whenever possible, I find that the simplest of raw data databases (or tables) can be the basis for very profound summaries and Pivot Table charts. Anyway, with that by way of background, here's my more detailed response.

You wrote (in italics here and below): 1. Yes, I usually download this data for each month from our help desk and then once im done creating everything for it, I then try to combine all that data into one.

And I need to ask you what you mean by "once I'm done creating everything for it"--are you adding calculations or other features, doing other things WITH the monthly data before you combine it? If so, what and why? 

From my observation, it looks like you may be deriving such things as MonthClosed from the downloaded data which contains the full date and time. Similarly YearClosed. And calculating days between open and close dates. You actually do not need to do either of those. So long as the dates in an Excel table are legitimate date formatted dates, Pivot Table can group the data for you into corresponding months or quarters. Let Excel do that in the course of creating the Pivot Table.

2. The monthly data layout is always the same. Like the column headers are the same.

Which would suggest to me that you should be just adding that new data to the old table, without touching it. Your "raw data" should as much as possible be just that, raw data; unprocessed. Let Excel do the processing (extracting, summarizing, calculating, whatever) afterwards, with the consolidated data.

3. That [what gets added that messes everything up] I am trying to figure out because when I add a new month it will add like a whole new column that messes up everything.

Well, you need to be able to at least describe what that new column or whatever it is that is "like a whole new column" if you have any hope of getting to the bottom of it.

4. So the data is from August 2018- July 2018 and I add one every month that we come up across. The whole point of this spreadsheet is to see different trends from the data. So I am trying to create different PivotTables/Charts showing that data for easier data analysis as a whole. I already have one created and created a dashboard with all my Pivot Charts and have slicers but like I said, when I add additional months to it, it adds columns that aren't even in my tables.

Now you're saying "it adds columns" with the last word there in the plural. Is this visible in the file you initially posted? If so, please tell me what I should be looking for to see it.

If I do use Access, could I create graphs showing all my data to see the different trends. I would like to create a dashboard like I already have. I'm not so good at Access as I've never really used it so I'm not sure what its capable of.

Access is a database manager. I've not used it in a couple of decades myself (I retired nearly 20 years ago), but as I recall you'd EITHER be able to produce most of the same reports/dashboard in Access OR you'd be able to use Excel to produce those same reports or a dashboard from the Access database. But let's just cross that bridge if and when we need to. For now, I'm pretty sure we can work all this within Excel. Access would become more valuable if you were working with many more years of comparable data. For that matter, during my career (at a major pharma company) I used to write queries to a mainframe database (HR and Payroll for tens of thousands of employees) and then process the resulting tables of data through Excel for nice looking reports. Excel can serve as the "front end" or user end, for many a back end storage system.

You mentioned a VBA, how does that work? So basically, At the end of the month I will gather the data for that month and insert a table and when I go to run the query the newest table gets added to the combined data.

OK. Let's not worry about that either. VBA (Visual Basic) is a programming language that can be used to automate repetitive tasks. At this point, it sounds more like you may need to refine the repetitive tasks you go through before thinking of automating them.

And on the subject of refining the repetitive tasks, could you spell out a bit more fully what it is you do each month. That last paragraph I quoted from you has several steps that don't fully make sense to me (i.e., I don't understand what it is you're doing; they make in fact make sense). These are the steps you describe yourself doing at the end of the month:

1. I gather the data for that month,
2. and insert a table and
3. (when) I go to run the query
4. the newest table gets added to the combined data.

• What's the table that you "insert"? Where does it get "inserted"?  OR are you just saying that you turn that data into a table using the "Insert" toolbar.  [The word "insert" can be used several ways; I'm just wanting to make sure that we're on the same page.]
• What's the "Query" that you run. Is that the Power Query you referred to in your first post, the one that "adds additional information that isn't even in your worksheets?"

Have you ever tried just copying and pasting the new month's data directly onto the bottom of the comprehensive data in your "Combined Data" tab?

• If you feel like you must do those calculations to get the ClosingMonth and ClosingYear etc., those can be done in that consolidated table....(although as I said above, I don't think you need to; Pivot Tables can do that combining on the fly).