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 2. Total comments: 106

  1. Hi, I would like to compile multiple data from multiple excel tabs into a table in 1 master worksheet. Is it possible to do that?

    1. Hello Jolene,
      If I understand your task correctly, you can solve it using our Combine Sheets tool. Please check out the add-in's manual here, I believe it'll be useful.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  2. Hi, is it possible to add each sheet name into the consolidated Sheet?

  3. Table 1:
    BOOKING ID CHECK IN CHECK OUT NO OF ROOMS Tariff
    AGVK4701 10/19/2019 10/20/2019 3 7674
    AIUV9058 10/27/2019 10/28/2019 1 1427
    BQMZ7562 10/20/2019 10/21/2019 1 1228
    BZWG1548 10/16/2019 10/19/2019 2 8272
    CRGT0534 10/19/2019 10/20/2019 1 2155
    CRWJ9574 10/24/2019 10/25/2019 1 892

    MAster Data
    Booking ID Guest Reconciliation status Booking Status
    ABJC1972 Mr C Santhosh Completed Checked Out
    ABTR8361 kuna Anveshkumar Completed Checked Out
    ACVM0475 Brahmaiah Manam Completed Checked Out
    ACVS5914 Prasanth Completed Checked Out
    ADWG1024 Parul Shankhala Completed Checked Out
    AENP3549 Parul Shankhala Completed Checked Out
    AFEK2374 rakesh Completed Checked Out
    AFPN6754 N Completed Checked Out

    I am looking the ID of the First work sheet with that of MAster sheet using the following formula =VLOOKUP(A2,'Booking Summary HYD770 Sorted'!$A$2:$A$1126,1,FALSE) in one of the column in Table 1. However it is finding the ID's that are available in various rows. However along with the ID I want a particular column from the Master sheet to be copied into Table 1 and in place of '1' I have given 29 as the cell data I want to retrieve before FALSE parameter in the above formula it is giving REF error. How to get it.

  4. My husband is trying to merge two work sheets. However when he uses consolidate so information goes missing. How is this resolved?

  5. Good Morning,

    I have two excel workbooks that I am trying to merge together. One is an old one, one is a new one. Both have multiple tabs. Is it possible to merge the data so that it goes into each tab? For instance:
    Tab1 (Old) is named: Bay Valley Foods
    Tab1 (new) is named: Bay Valley Foods
    Tab2 (Old) is named: Ralcorp
    Tab2 (New) is named: Ralcorp

    I am trying to combine Tab1 (new) into Tab1(old) and Tab2 (new) into Tab2 (Old).

    As this is a work computer, I am also not allowed to download anything including the merge table wizard. So I would need to know if this is possible without that.

  6. Hi,
    I'd like to automate the process of merging data from two separate sheets. Is there a macro or a VBA script that will allow me to use the Merge Two Tables option to quickly merge data? When I try to record a macro for the Merge Two Tables feature it just shows up blank. Thank you!

  7. my question is how to combine multiple column data into the single cell? below given data is from multiple columns and i am trying to consolidate all in single cell in sheet2.
    For Example:
    Description "From Sheet1" - from sheet1
    [SNOW:XXXXXXXX (From Shee1) SOLMAN:XXXXX (From Sheet1)]

    Please advise

  8. trying to combine 14 sheets together....only 8 columns each, only 40 rows each. Only combines 13 together. No matter which one i start with it always leaves one out????

  9. in Combine multiple worksheets into one with Copy Sheets if i a have the same range for all my work sheets can i apply it for them all in the same time or do i have to reselect it for every work sheet

  10. Hi
    How can you do these on Google sheets?

  11. Hi, is it possible to add each sheet name into the consolidated Sheet?

  12. I have a requirement. I have data in sheet 1, Sheet 2 and sheet 3 in a excel. I have around 100 records in each sheets. I want to combine all this data in another sheet, say sheet 4. The order of the record in sheet 4 should be like 1 record from sheet 1, next record should be from sheet 2 and next should be fro sheet 3 and again go to sheet 1 and so on... Note all the column names are the same in all the sheet. Any short cut techniques other than copy paste

    1. Once you install the plugin, then under the plugin/addon use "copy sheets --> Data from the selected sheets --> select row you want to copy from multiple sheets" then set your preferences to create the new sheet(in your case sheet4). It should work, but you have to give the row range for all the sheets not sure if there any program to run this method. Let me know how it goes, even I'm working to get the data in similar way, will share you if I have anything

  13. Hi,
    I'm using your add-in for the first time. Thanks for it, save lot of time.
    But I have an issue when using it, for the first time when I try to consolidate the data(specific row range) from multiple sheets(6 sheets) into one master sheet it worked. But for the second time it won't allow me to specify the data range, is it not letting me because I use trial version? let me know
    Thanks,
    Yeshwanth

  14. How can i Marge all worksheet into a single worksheet within the workbook..
    Headers are same

  15. Say you have an employee name list with salaries as well. Some employees have now received a raise and their updated salaries are in a new table along with their names. How would I merge the new salaries to replace the old ones?

    1. Hello, Dean,
      Thank you for your question.

      If we understand your task correctly, please have a look at our Merge Tables Wizard. It allows you to update the data in the main table with the data from the lookup table based on the key column(s).

  16. Hi,

    I need to creates a macro that would merge data in two sheets to one. The number of columns and the column headers are the same.

    The only catch here is every time we run the macro we will have different number of rows in each of these sheets so we cannot hard code the row numbers in to the VBA code as it will keep changing. I want a general Macro which would paste the data of one sheet after the other sheet into a new sheet.

  17. I have 7 worksheets.
    Each worksheet has several matching fields but they also have several different fields.
    IS there a way to merge them so the similar fields are in the correct column and the different fields are in their own column?
    The different fields will have blank cells were the data doesn't pertain. Also the number of rows increases each day in all worksheets.

  18. I have 14 different spreadsheets and each spreadsheet has some of the same data but they also have different data for some of the columns and rows. For instance one sheet will go to column N while another one will go to column AB. I want to create a master spreadsheet that combines the data of all 14 spreadsheets into one. We support different groups and the main objective is to try and see what groups have what. It will be a very big workbook. Does the consolidate data tool work for this type of functionality?

  19. How do I copy all 3 different reports to its own worksheet within a single excel file

    1. Hello, Joseph.
      Thank you for contacting us and for your question.
      Please have a look at our Combine Worksheets Wizard for Excel. It allows you to combine data from multiple sheets into one at a glance.

  20. Sub MergeSheet()

    'Declaring the Variables
    Dim LastRow, ShtCnt As Integer
    Dim ShtName As String
    Dim NewSht As Worksheet

    'Assinging a Sheet Name by UserInput
    ShtName:
    ShtName = InputBox("Enter the Sheet Name you want to create", "Merge Sheet", "Master Sheet")

    'Count of Total Worksheet in the present workbook
    ShtCnt = Sheets.Count

    'Using For Loop check if the worksheet exists
    For i = 1 To ShtCnt
    If Sheets(i).Name = ShtName Then
    MsgBox "Sheet already Exists", , "Merge Sheet"
    GoTo ShtName
    End If
    Next i

    'Create a New Sheet
    Worksheets.Add.Name = ShtName

    'Assigning NewSht as Current Sheet
    Set NewSht = ActiveSheet

    'Moving Worksheet to the beginning of this workbook
    NewSht.Move before:=Worksheets(1)

    'Copying all the data to the New Sheet Using For Loop
    For i = 2 To ShtCnt + 1

    'If i=2 Then copy all the data from the second sheet including header.
    If i = 2 Then
    Sheets(i).UsedRange.Copy NewSht.Cells(1, 1)
    Else

    'If i is grater than 2 then copy all the data excluding Header(1st Row).
    Sheets(i).UsedRange.Offset(1, 0).Resize(Sheets(i).UsedRange.Rows.Count - 1, Sheets(i).UsedRange.Columns.Count).Copy NewSht.Cells(LastRow + 1, 1)
    End If
    LastRow = NewSht.Cells.SpecialCells(xlCellTypeLastCell).Row
    Next i

    'Displaying the Message after copying data successfully
    MsgBox "Data has been copied to " & ShtName, , "Merge Sheet"

    End Sub

    1. ya hoo Great
      Done Thanks

    2. Thank you so much!!! Worked perfectly.

    3. LOVE U

    4. hi
      thank you a lot
      kiss you

  21. 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.

  22. 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

  23. 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"

  24. 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.

  25. 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

  26. 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.

  27. 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

  28. 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

  29. 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.

  30. 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.

  31. 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?

  32. 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

  33. 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 .

  34. 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

  35. 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.

  36. 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

  37. 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.

  38. Hi,

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

  39. 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.

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

  41. 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.

  42. 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?

  43. 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 :)