Comments on: Consolidate data in Excel and merge multiple sheets into one worksheet

The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after – consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column. Continue reading →

Comments page 3. Total comments: 106

  1. I have 50 excell sheet. With same structure and column. I want to combine them all into one sheet. It should simply copy sheet one after other using vba.

    1. Hello, Mohammed.
      If we understand your task correctly, our Combine Worksheets Wizard can help you combine multiple sheets into one without copying and pasting your data.

  2. Hello Sir/Madam,
    Kindly help me to fetch/merge data from various sheets {input from various location to same sheet(Left column and top row header are same)} data in to one sheet. For example I have a sheet having 15 columns and 2000 rows data for 15 locations. Each location entered their data into 15 columns. Now I want to merge all the data into my main sheet (15 columns and 2000 rows). How I can do it fast without copying individually. Please give me solution.
    Thanking you,
    Kamlakar Chavan

  3. I want to take work sheet from several different files and combine them , but when I Browse and get the file and try to add it "states consolidation reference not valid"

  4. I have 6 sheets with each column total of 10 columns representing a location. Each column i will enter a item#. The master sheet i want to be able to enter the item# and it will pull the info from all 6 sheets for that item# and give me the location. I want to try and eliminate having to use CTL+F every single time.

  5. I have previously worked in home loan as a loan processing executive. there I have found that 1st work sheet takes input 2nd worksheet process and 3rd worksheet is output .

    1st worksheet takes input when I click on finish button on 1st sheet it directly goes to 3rd sheet which is only for printout. It doesnot show 2nd sheet it directly jumps to 3rd sheet.

    I want to create one of this with a little change here, in 1st sheet i want to input or insert images along with data ( alpha and numberic data).

    can anyone guide me or help me how to do with the excel or something else.

    thanking you

  6. Hi everyone,

    First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.

    I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.

    In Addition to this I have to merge all the worksheets and have them in another sheet which is called "Übersicht" (Overview). However there is a different column in the sheet and it's between "Nr." and "Thema" columns (which are in A1 and B1 in all the 9 Sheets) and this different column called "Kategorie" (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this "Nr. (A1), Kategorie (B1) and Thema (C1).....".So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in "Übersicht" (Overview) sheet needs to update by itself. How can I do this?

    P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows (in each row there is different Information for different Projects). This also means the number of rows can increase or decrease.

    I hope I explained it well. Thanks a lot in advance!

    I wish you merry Christmas and a happy new year!

    oduff

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  7. How to use excel to compute variance(thus the differences ) for or among three figures e.g 2,000, 2,400 and 1,900 and report the result in a percentage (e.g 19%) and then compare it with a benchmark figure (e.g <=20%). Thanks

  8. Hello, i have 6 worksheets. All with the same header, so merge them and linked to just one consolidated worksheet is not problem.
    But what is problem, that when i update my source woksheets, there are increasing number of rows (some new customers and products are appearing in sales). How can i set that consolidated worsheet automatically will take into consideration also new rows and new range(and of course updated numbers of old rows)?

    Thanks

  9. I have data with same type having 10 different worksheets. The problem is that, when merging all data i need only one row from 10 different worksheets. Means row A2 from 10 worksheets and combine to make one worksheet. second one is row A3 from 10 worksheets and combine to make one worksheet.

    1. Hi Mahesh,

      The tool which can help you is called "power query". It's an add-in by default its comes in 2016 editions, but earlier versions of 2016 we have to download externally. you might wanna have to search the tutorials in youtube how to use.

  10. Everything here is contained in 1 workbook.
    I have multiple spreadsheels which (when printed) are forms.
    Each one has multiple cells but the format for all the forms is identical. I would like to be able to input data into each form and have it update my master spread sheet automatically. I cannot use the range option explained above because the cells are not consecutive.

  11. I'm trying to find out if there is a way to combine multiple excel files into one file but on multiple pages within that file. Is there an easy way to do this?

  12. Hi

    I have 6 tabs, I want to combine first 4 tabs data into combined worksheet. But I do not want remaining 2 tabs data into combined tab. Is there any macro where I can mention tab names which i do not want to be touched and copy the rest of the tabs as usual?

    Thanks
    Mani

  13. Hi,
    I want to consolidate different city data into a master sheet but i am unable to do so because there is some columns are merge and its not working properly please help me out .

  14. Hi,

    I am looking for a way to merge multiple worksheets into the one consolidated sheet. There is text and numbers that I need to move, no pieces of information are the same.

    I want to be able to update the individual worksheets and have it update the main sheet.

    I used to do this @ 15 years ago, but have forgotten how it is done. I thought it was a lookup formula, but after 3 hours at it, I can't seem to get it to work.

    Hope you can help.

    Sam

    1. Hi Sam,

      Please try Ablebits Consolidate Worksheets Wizard:
      https://www.ablebits.com/excel-suite/combine-sheets.php

      You can select the option Create links to the source data, which automatically updates the results when the source data are changed. However, if some data are added, it will not work. To add the missing data, you can use Merge Tables Wizard:
      https://www.ablebits.com/excel-lookup-tables/index.php

      Both these add-ins can be found in Ultimate Suite for Excel. Feel free to install its fully-functional trial version from this page:
      https://www.ablebits.com/downloads/index.php

  15. Hi, I am trying to consolidate multiple sheets (with same tables) into 1 by using the Ablebits wizard.
    However as indicated here in the example, when I select consolidat worksheets, I am only able to consolidate the (numerical)data and not the text fields. So in your example there are 5 steps to complete in the workflow, but in my case it only consists of three steps where I do not have the possibility
    how I want to consolidate the data (I can only select the function to consolidate the data).
    I am using Excel 2010.

    Hopefully you can help me to figure out how I can use the consolidation function of the wizard as explained in the tutorial.

    Kr.

    1. Hi T,

      Feel free to contact support@ablebits.com for help. Please attach a sample workbook with your data and the result you want to get. Our support assistants will do their best to help.

  16. Hi - I wish to consolidate data from multiple sheets into one sheet BUT the source data from these sheets will change (and be added to). Which of the above methods is the best to ensure that the end product is dynamically updated?

    Thanks

    1. Hi Oliver,

      Please try Consolidate Worksheets Wizard:
      https://www.ablebits.com/excel-suite/combine-sheets.php

      It has the option Create links to the source data, which automatically updates the results when the source data are changed. However, if some data are added, it will not work. To add the missing data, you can use Merge Tables Wizard:
      https://www.ablebits.com/excel-lookup-tables/index.php

      Both these add-ins can be found in Ultimate Suite for Excel. You can install its fully-functional trial version from this page:
      https://www.ablebits.com/downloads/index.php

  17. In one worksheet, I have 4 columns, each 2 columns have same heading but different data, want to combine these columns in new workseet.
    Data as:
    Message qty Message Qty
    Hi. 10 Hello. 20

    Need ouput as :
    Message Qty
    Hi. 10
    Hello. 20

    1. Hi Jyoti,

      You can cut the columns 3 and 4 and paste to the new worksheet and then use our Combine Worksheets tool from the Consolidate Worksheets Wizard add-in to solve this task.

  18. Hi,

    Is it at all possible to merge multiple worksheets into one when the column and row lengths all differ in the individual worksheets?

  19. Hi,

    I have multiple sheets in single folder and want list of particular (C6, E17) from sheet 1 from every workbook.

    I need summary for that two cells in A & B column.

  20. Hi,

    How can I combine 3 different sheet in one single sheet which in every sheet i have specific column that will be related to other sheet? This is the example:
    - in sheet 1, i have to take ship number and net value
    - in sheet 2, i have to take ship number and billing doc
    - in sheet 3, i have to take bill doc and total price
    I need to summarize all sheet to make a final report with that all variable ( ship number, net value, billing doc, and total price)

  21. Hi,

    How can combine three different workbooks with three different worksheets into one workbook for sharing information?

    1. Hi Val,

      You can use our Combine Worksheets tool from the Consolidate Worksheets Wizard add-in to solve this task.

  22. I have 2 workbooks to compare one old one new, each with multiple worksheets. I want to combine all worksheets in each into 2 single worksheets for comparison. I then want to highlight additions and changes in the new one and the deletions in the old one. Can you reccomend a method?

  23. Hi,
    I have 3 sheets in each three sheets in 'column A' there are common and different numbers in 20 to 30 'rows' . how do i get 'All' these no. but NOT get repeated in my another "final" sheet,
    Second, I want to get the sum of numbers in 'column I' corresponding and representing to 'columnA' in each sheet to a 'Final sheet'.

    So, for First result i tried
    =IFERROR(VLOOKUP('1st Month'!$D4,'2nd Month'!D4:D22,1,0),IFERROR(VLOOKUP('2nd Month'!$D4,'3rd Month'!D4:D23,1,0),IFERROR(VLOOKUP('1st Month'!$D4,'3rd Month'!D4:D25,1,0),"0"))) formula and not sure if this right.
    for second Result i tried =SUMPRODUCT(SUMIF(INDIRECT(""&test&"''!$D$4:$D$19"),$D4,INDIRECT(""&"'!$I$4:$I$19")), Pls. help me in getting the expected answer. Thanks!

    1. Hello, Rahul,

      To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)