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

Copper Contributor

Hi,

I have this spreadsheet that I am working on and it contains multiple worksheets of data. I would like to combine all that data into one and create PivotTables out of them. I would also like to continue to keep adding additional worksheets and have it updated any time it is updated to the combined data. I tried doing this through Power Query but it never works. It always add additional information to my data that isn't even in my worksheets.

 

Every month I have new data to add and it seems like every time I do this, it messes up my combined data when I run the query with the new worksheet.

15 Replies

@Rujinh 

 

A couple of questions for further defining your needs here.

  1. Is the data added monthly coming from a source over which you have any control?
  2. Is that monthly data consistent in its layout (same columns from month-to-month)
  3. What's the nature of the "new data" that's getting added that "messes up" your combined data?
  4. Are  you needing to keep history to an unlimited extent, or is it acceptable to purge data (at least from the data being analyzed) from, say, three or four years ago?

 

There may be other questions needed to get a full description here of what you're doing....those are starters, but feel free to add information if you consider it important.

 

My reason for asking the last question is this: if your history goes back say, ten years or more, and if being able to report on historical trends is vital, you might want to consider moving the whole database to Access, instead of Excel.

 

That said, so long as this is a matter of once a month (presumably at the end) adding that month's data to the existing sheet that contains the full history, wouldn't it work to simply do it manually (or just writing a simple VBA routine if it's truly routine)? If that compiled spreadsheet has been declared or designated as an Excel Table, the Pivot Table would simply need to be "refreshed" and you'd be home free.

@mathetes  Thank you for your response, I have added my comments 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.

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

3. That 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.

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.

 

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.

 

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.

 

I have attached some screenshots of what happens. I don't get why I keep getting blanks either because all the information is always filled out.

@Rujinh 

If monthly sheets have the same structure you may collect them in separate folder and use File->From Folder connector. It combines data from all such files, you may return summary table to Excel. But better load to data model only and build pivot tables / charts from it. 

@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).

 

@Sergei Baklan 

 

I'm concerned that @Rujinh may not know where to go to implement what you've suggested.

 

Concerned because I don't know either. Where would he (or I) find that File->From Folder connector you refer to? I've looked for it--from your description here it sounds like it could be VERY helpful--but I don't find it under the File menu, the Insert or Data tool bars.....Is it possible that since I'm on a Mac, that what you're referring to is only in the Windows version of Excel? (I have the most current Mac, with full access to the Dynamic Array functions)..

 

Thanks. I always appreciate your far more advanced expertise.

@mathetes 

I'm sorry, you are right. That's Power Query and that's Excel for Windows desktop.

@mathetes 

 

Im so sorry for any confusion. I just really want to see the easiest way I can get this done. So let me just say what I do each time. Every month I have this data that I download from our help desk. I then want to try and make a better a visualization out of the data by creating a dashboard which I have shared with you in the previous posts. There are certain things I am trying to show as you probably saw. The reason why I created a column to calculate was because we want to see on average how many days it takes to complete a ticket, which is why I have those last 3 columns.

 

1. I see what you are saying here but I want to calculate the average days it takes to complete a ticket.

 

2. So you are just saying to copy and paste each time? I tried doing that in the past but it seems like too much to do each time. I have an original spreadsheet that contains data from August2018-July2020. So I created a table for each of those month and ran a Power Query to combine all those tables into one. Now my issue here is when I add another month to it, it sometimes messes up. When the combined data loads it will give me extra columns or some of the information is blank and im having to go back and enter it all in.

 

3. So I usually well add a new tab in my workbook and then go to my combined data tab to edit the query. When I do this, I combine the newest data but then like I said before it adds an extra column or will add blank information which I don't understand because the raw data has everything filled out already.

 

4. The screenshots I added in the previous post will show what I am talking about.

 

Jumping to the end of your response:

The steps you mentioned are correct. I turn that data into a table using the "Insert" toolbar. 

 

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?"

 

  • Once I have all my months added in separate tabs(worksheets) I go to data and where it says "New Query"  I go to from other sources and select blank query. From there I put in my formula =Excel.CurrentWorkbook () and enter. When I do that it pulls every table in the work book. But when I do that, it sometime will load blank information. I know this may all seem confusing and I may be making it more difficult then it should be but I just don't know how else to do it.

@Sergei Baklan 

 

My monthly sheets have the exact same information and structure each time. So if I have them in a separate folder and use file>from folder connector, can I continue to add months to it in the future and it still combine the data correctly?

@Rujinh 

Yes, you may add / remove files when needed. You may start from two-three sample files to see how it works. On first step you'll see list of all files, click on Combine Files button on Content column and follow the wizard, make other transformations if necessary.

@Rujinh 

 

I'm going to step aside for the moment, since you do have Power Query at your disposal (I don't in my Mac), and let @Sergei Baklan help you with that process. It would seem to me that it should work.

 

My own experience, without it, is to that do that monthly copy and paste of the new month's data onto the bottom of a cumulative database is to do--in just one step--what you're trying to do in many. I do this with my monthly statements from credit cards and banks, into a single database to track our income and expenses....I let Excel (Pivot Table) do all the calculating. It is work, yes, but I would point out that you've been doing work and aren't satisfied with the results.

 

So I trust that Power Query will do the trick for you, the trick of combining all those monthly reports neatly and accurately....

 

Best wishes.

@mathetes thank you for your help! I will try to do what you were mentioning as well and see what works better for me. I just wanted to find the easiest way to do this and be able to present it in a dashboard for a better visualization.

 

@Sergei Baklan could you walk me through so that I don't mess up what you were trying to explain to me?

 

Both of your responses and ideas are great, I just need to try it out and see what works better for me. I do appreciate both of your assistance with this because its been giving me a headache trying to figure it out. I wouldn't say I'm in expert in excel but I just like to always learn more and find better ways to extract big data and provide a better visualization.

@Rujinh 

To test please put 2-3 of your files in some folder, let say c:\test. Use this connector

image.png

Select the folder on next step

image.png

Next click Transform Data

image.png

Power Query editor will be opened. Click on this button

 

Here select sample file or keep first one by default, and select the sheet / table with your data - they have to have the same name for all files

image.png

Result collects data from all your files in one table. You may do other transformations if necessary. Click here on File->Close and Load to. In your case perhaps it's better to select loading to data model

image.png

After that you may insert PivotTable with data model as the source. For the testing you may load result into Excel Table as well.

@Sergei Baklan 

 

Okay, I have tried to follow your steps but it did not work so im not sure if I am doing something wrong. I have attached my screenshots for you to look at. Can you aslo let me know if my ribbons has everything I should have?

 

Thank you!

@Rujinh 

You skipped few steps. Here

image.png

click on Edit, not on Combine (in my version this button is Transform Data). After that approximately as in my previous post, perhaps your user interface is slightly different.

@Sergei Baklan okay so I did that but I keep getting nulls...I don't get why because those files don't have any empty fields. This is what my issue was before.