Combine data from multiple sheets in excel
Question:
Problem description (simplified of course):
I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each TAB is a separate month (first is Jan, 2nd is Feb, etc.). I have 12 tabs (12 worksheets) in workbook. Each TAB, a single month, has a list of ID numbers. Some IDs may repeat on different worksheets, that is, some may be in multiple months and some may be in just two or three months or just one month. An ID number will shown only once in a month for a single task (duty). Abbreviated example is below.
Is it possible to combine the data, by function, or formula, or VBasic) to a 13th worksheet automatically and:
1. Show a list of all ID numbers in order (without repeating).
2. Show Jan data in col B, Feb data in col C, etc., and some columns will be blank because the ID had no assignment that month, and will not be on the worksheet for that month.
Is there a formula, or function, or does it have to be done in VBasic? (Is it even possible?)
I have the workbook with 12 tabs in it, and now have to manually put the ID columns side by side and copy and slide down one side on the other to get them to match, and repeat the process 12 times to get the yearly data on one worksheet.
Ex:
For Jan:
ID Duty Asgn.
01 C
05 F
09 D
15 X
23 P
For Feb:
ID Duty Asgn.
02 M
05 Q
08 A
12 R
20 W
Combing Jan and Feb would be:
ID Duty Asgn.
01 C
02 M
05 F Q
08 A
09 D
12 R
15 X
20 W
23 P
This would be repeated for each month to build all 12 col months.
Very Respectfully,
Dave Bonar
Answer:
First I thought your question required vba but here is the answer using only Excel formulas.
Maximum ID number from multiple sheets
Formula in cell B1:
Recommended article
Merge two columns into one list in excel
Question: How do I merge two ranges into one list? Answer: Formula in C2: =IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)ROWS($A$2:$A$6)), "")) Copy […]Comments(50) Filed in category: Combine/Merge, Excel
Create a sorted unique distinct list of numbers originating from multiple sheets
We are going to use the number calculated in B1 (28) in array formula in cell A4 (bolded):
copied down as far as needed.
How to create a unique distinct list sorted alphabetically:
Create a unique distinct alphabetically sorted list extracted from a column
The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Comments(53) Filed in category: Excel
Combine data from multiple sheets
Formula in B4:
copied down as far as needed.
Recommended article:
Merge two columns with possible blank cells
Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]Comments(28) Filed in category: Combine/Merge, Excel
Formula in C4:
copied down as far as needed.
Recommended article:
Consolidate sheets in excel (vba)
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]Comments(11) Filed in category: Combine/Merge, Excel
Formula in D4:
copied down as far as needed.
Recommended post:
Comments(7) Filed in category: Combine/Merge, Excel
Download excel tutorial file
Getdatafromeachsheet.xls
(Excel 972003 Workbook *.xls)
Functions in this article:
IF(logical_test, [value_if:true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROW(reference)
Returns the rownumber of a reference
SMALL(array,k)
Returns the kth smallest row number in this data set.
MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.
FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.
MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value
ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE
Merge Ranges AddIn
Merge Ranges is an addin for Excel 2007/2010/2013 that lets you easily merge multiple ranges into one master sheet. The Master sheet is instantly refreshed if the data in one of original sheets changes. Once the formula is entered in a workbook, it works automatically without user interaction.
Features
 Master sheet is instantly updated when values in a range are edited/added or deleted.
 Possible to merge up to 255 different ranges.
 Easy to use custom function.
 Get all data from all ranges.
 Column headers in master sheet selects what column data to merge.
 Blank records are not added to master sheet.
 BONUS: Fetch unique distinct records or values from all ranges.
 BONUS: Filter duplicate records or values from all ranges.
Example 1  Get all data from all ranges
In this example there are four sheets. Master sheet and three sheets to be merged.
Sheet2
Sheet 2,3 and 4 contains data about people (Fake randomly generated data). The ranges also have some blank records. Click to expand.
Master sheet
Master sheet contains all data from all three ranges merged into one list. Nothing is removed except blank records. Column headers in master sheet rearranges columns automatically. Compare above picture with picture below and see how the order of column headers can be changed in any way you like.
How to use custom function in excel 2007
The user defined function:
=MergeRanges([filter_type], lookup_header(s), range1, range2, ...)
filter_type
0  Get all records from all ranges
1  Get unique distinct records from all ranges
2  Filter all duplicate records from all ranges
lookup_header(s) is a cell reference to a range of values (column headers)
range1, range2, ... are cell references to cell ranges to merge. Each cell range have column headers in first row. There can be as many as 255 cell references.
Example 2  Filter unique distinct records from all ranges
In this example there are four sheets. Master sheet and three sheets to be merged.
Sheet2
Sheet 2,3 and 4 contains data about people (Fake randomly generated data). The ranges also have some blank records. Click to expand.
Master Sheet
Master sheet contains unique distinct records from all three ranges merged into one list. Nothing is removed except blank records. Column headers in master sheet rearranges columns automatically. Compare above picture with picture below and see how the order of column headers can be changed in any way you like.
How to create unique distinct records using custom function
The user defined function:
=MergeRanges([filter_type], lookup_header(s), range1, range2, ...)
filter_type
0  Get all records from all ranges
1  Get unique distinct records from all ranges
2  Filter all duplicate records from all ranges
lookup_header(s) is a cell reference to a range of values (column headers)
range1, range2, ... are cell references to cell ranges to merge. Each cell range have column headers in first row. There can be as many as 255 cell references.
Example 3  Filter duplicate records from all ranges
In this example there are four sheets. Master sheet and three sheets to be merged.
Sheet2
Sheet 2,3 and 4 contains data about people. (Fake randomly generated data). The ranges have column headers in first row (requirement). There are also some blank records.
Master sheet
The Merge Ranges Addin found one duplicate record from all three ranges combined.
What you get
 Merge Ranges Addin for Excel 2007/2010/2013 *.xlam file.
 Instructions on how to install.
 Instructions on how to use custom function.
 Excel *.xlsm example file.
 2 licenses, home and office computer.
 You can buy VBA source file for $10 more.
Purchase Merge Ranges AddIn for Excel 2007/2010/2013  Price $19 US
Purchase Merge Ranges AddIn for Excel 2007/2010/2013 (unlocked, you can view and edit vba code)  Price $29 US
Questions:
Do column headers in master sheet need to be arranged in the same way as column headers in other sheets/ranges?
No, you can rearrange column headers as you like. A requirement is that they have identical spelling (not case sensitive).
Do I need to have as many column headers in master sheet as in the other ranges?
No, you can have fewer column headers in your master sheet, if you like.
How do I enter this user defined function? It is an array formula.

 Type user defined function in formula bar.
 Press and hold Ctrl + Shift.
 Press Enter once.
 Release all keys.
Is there a money back guarantee?
Sure, you have unconditional money back guarantee for 14 days.
Can I view the vba source code?
No, it is locked for viewing but you can buy VBA source file for $10 more.
I have more questions?
Use this contact form to let me know.
Purchase Merge Ranges AddIn for Excel 2007/2010/2013 (unlocked, you can view and edit vba code)  Price $29 US
How the Purchase Process Works?
 Payment is accepted via PayPal.
 After you finish payment, you are redirected to the download page. You will also receive an email with the download link.
 You have five attempts to download the file.
 The download link will expire in 120 hours (5 days).
If you can´t downloading the file, contact me.
Category: Combinemerge
Merge two columns into one list in excel
Question: How do I merge two ranges into one list? Answer: Formula in C2: =IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)ROWS($A$2:$A$6)), "")) Copy […]Comments(50) Filed in category: Combine/Merge, Excel
Merge two columns with possible blank cells
Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]Comments(28) Filed in category: Combine/Merge, Excel
Merge three columns into one list in excel
Question: How do I merge three columns into one list? Answer: Excel 2007 array formula in D2: =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, […]Comments(26) Filed in category: Combine/Merge, Excel
Consolidate sheets in excel (vba)
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]Comments(11) Filed in category: Combine/Merge, Excel
Comments(7) Filed in category: Combine/Merge, Excel
Group a number of rows together by the first column
Mike asks: Oscar, I'm hoping you can help. I am trying to group a number of rows together by the […]Comments(5) Filed in category: Combine/Merge, Excel
Automate excel: Update list with new values
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]Comments(4) Filed in category: Combine/Merge, Excel
Excel udf: Combine cell ranges into a single range while eliminating blanks
This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]Comments(4) Filed in category: Combine/Merge, Excel
Tracking a stock portfolio #2 in excel
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]Comments(3) Filed in category: Combine/Merge, Excel, Finance
It looks like you are assuming that everyone knows that this technique can return the # of unique data entries from a real 3D range.
=SUM(IF(FREQUENCY(Jan:Mar!$K$1:$L$3,Jan:Mar!$K$1:$L$3)>0,1))
I am pretty sure that no one has ever done this before, so congratulations!
David Hager,
Thanks!!
/Oscar
Hi Oscar,
I am not sure if my problem is something which is similar to what you have done here or not. I hope that you would know and could help me so I will give it a try....
I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The columns I am interested in each workshets are "Date Plan", "Date Compelted" and "variance" and "Project Code"
I then want data from all these column to be extracted in a Report worksheet and later want to do a trend chart by sorting all dates in chronological order.
Key bit to start is how do I get data out from worksheets. Esentially I want all the data without any loss. There are chances that some of the data between worksheet1 & 2 could be identical, apart from project code.
Also, preference is that this coding or formula should work for any future addition to worksheet data and workbook worksheets.
I am not sure if I manage to explain everything very clearly. Please do not hesitate to email me if you need more clarification.
Best Regards,
Jignesh
Hi Jignesh!
Interesting question!
I´ll post an answer as soon as I can.
Thanks Oscar,
Really appreciate your input and help. I am wondering if there would be a way to write a formula with lookup function to get the date from various worksheet...
say if I ask to search for January data then I am expecting January data from all work sheet to be extracted and copied in my Report worksheet.
I can then have 12 X 4 ( For Key Columns  date plan / completed / variance and project code) different columns with all monthly data from various worksheet.
I would then need to plot a chart using the variance data....
Problem is I am not sure if this is possible and if yes, how to write such fomula :(
Hello, Oscar,
First of all id like to thank you for your blog. I have found many very usefull tips and answers, but still i have one problem that i cant solve by my self. So im asking for your help.
Here is the problem:
i have a data table with 2 columns:
A B
2.93 12.8
2.94 12.2
3 8.38
3.03 6.76
3.04 5.33
3.06 6.36
Lets say i have a cell with number 3. I need to find a number in column A that has a number >= than 3, but also has the smallest number in column B.
(with my cell = 3 it would be 3.04 from A and 5.33 from B)
Simple vlookup gives me first >= number, but in most cases in column B is not the smalest number.
I hope you can help me,
Best regards,
Liudas
Liudas,
See this post: https://www.getdigitalhelp.com/2010/03/24/lookupusingtwocriteriainexcel/
Jignesh,
I think I can solve your problem using vba. I hope I can post a solution here soon.
Jignesh,
see this post: https://www.getdigitalhelp.com/2010/03/29/consolidatesheetsinexcelvba/
Thanks a lot Oscar! You are genius! Thanks again!
hi..i have been working on my problem for quite sometime now...i wud b very thankful if u cud provide me the solution.
problem: i have created a workbook containing 12 sheets each corresponding to a month. each sheet has 3 fieldsregion,premium, claims and ICR. Mow i want to consolidate this data using drop down menus such that wen i select say january month from the drop down, january months data for all the regions shoud be dsiplayed ans similarly for all other months. in addition region should also be in a drop down i.e. wen viewing the data for a particular month, i could select a region and then view its data.
Pls help me in this
Hello,
Is is possible to get this work with IDnames not numbers?
Here: "Create a sorted unique distinct list of numbers originating from multiple sheets"
Lets say if i have the id's like ALFA, BETA, GAMMA, DELTA etc. is it possible to collect all these unique names and combine some data from their rows and sheets? I assume the function small doesn't work anymore?
Thanks for good work!
Tjena JanErik!
As far as I know using formulas, no!
But maybe pivot tables can solve your problem?
Excel Pivot Table Tutorial  Multiple Consolidation Ranges
Hi,
Same logic i need to be use , but there is the problem when my id nos are more then the No.of row values i.e in excel 2007 total no of row is 1048575 , but hear my id value is more then rowno value i.e 5505982 so that i am not able to get the output .
can you plz sugest ..
Thanks
R Thamu
Thamu,
Interesting question!
I never thought row function created numerical limits. 1 to 1048575.
I´ll comment here as soon as I have an answer.
Hi Oscar,
I have a question about merging two excel worksheets into one masterfile.
I have a first worksheet with daily stock price data for hundreds of firms over a couple of years (panel data). I have the following columns: Firm Number, Date and stock price.
Now I have a second worksheet containing accounting data for the same firms over the same years. Since the accounting data is yearly data, this sheet has less observations.
The columns I have in this sheet are: Firm number, Date,Fiscal Year, FiscalYear end month and a few accounting variables.
Now, I want to add in the columns next to the daily stock market data the accounting variables for that specific fiscalyear.
So for example:
I have a row with stock price of firm #1 at 27122010. The accounting worksheet shows that the fiscalyear of firm #1 ends on 31092010, so first I have in someway to determine in the daily stock price file that 31122010 is fiscalyear 2011 for firm #1 and consequently, in the columns next to the stock price per 27122010 I want to add the accounting variables of firm #1 in fiscalyear 2011.
I would like to do this with normal excel formula's (not vba), but I cannot find the solution...
I really hope you can help me!
Thanks in advance!
S.W.
Hi Oscar, i hope you can help.
i have a workbook consisting of several sheets. each sheet depicts data for several critera related to that specific sheet (the row fields are the same across each sheet) which is further broken down into categories specific for that area. in the summary sheet which also share the same rows i should be able to select a category (from a dropdown) which will give me a summary of those fields in each category.
i.e
the headings for the categories on each sheet would be 1 to 100(area 1, area 2, etc). furthermore fields making up the rows for those headings(e.g apples, pears, oranges, etc)
areas might overlap in town sheets.
the sheets are named by town (town 1, town 2, etc)
the summary sheet  i wish to see how many apples, pears, oranges was sold in a specific area across all the towns.
if i select say 'area 2' in the area dropdown, it will search across the sheets and display the totals of each of the items sold for area 2.
thank you for your help...
I need to consolidate some excel workbook into one to make a master list.and i also want to have duplicate to identify on that master sheet,you think you can help me?
Hi I have the same question however with different ID. In my case I have several sheets which I like to combine in to one, like the example but each sheet has the date and time in the first column and a value in the second. So the idea is to have a master sheet with all possible date/time in the first column and depending if there is a value in the second column for the first sheet the third column for the second sheet and so on ...
Dear sir,
You worksheet is awesome but when I type 29 or above numbers in ID it shows error.
Kindly advise.
Kind Regards,
Sanjeev
Hello Oscar,
Hope u could help me. I have a workbook that contains multiple sheets, say (Nov1, Nov2...Nov30). Each sheet contains columns such as "Scripts executed","number of hours worked","productivity" "Defects raised" assigned to all the employees
I need to create a final productivity sheet containing columns namely,
1.Total number of scripts executed
2.Total hours spent
3.Average productivity
4.Total defects raised
against each employee.
Also the problem requires, cumulative updation of final productivity sheet. Is this can be solved?? Awaiting for ur solution
karthik,
Yes, upload an example workbook.
You are genius Oscar. I was trying vlookup but didn't get it to do what i wanted. You just wowed me. God bless you. Thanks you so so much for being there.
Sheet 1
Date Ref No From/TO Item Received Qty
12Oct13 121 DXB Pen 25
13Oct13 122 SHJ Pencil 18
14Oct13 123 Pen 19
Sheet 2
Date Ref No From/TO Item Issued Qty
13Oct13 256 DXB Pen 12
14Oct13 365 SHJ Pencil 17
15Oct13 656 ADH Pen 11
Sheet 3
Date Ref No From/TO Item Received Qty Issued Qty
12Oct13 121 DXB Pen 25
13Oct13 122 SHJ Pencil 18
13Oct13 256 DXB Pen 12
14Oct13 123 Pen 19
14Oct13 365 SHJ Pencil 17
15Oct13 656 ADH Pen 11
please solve the problem like sheet 3
abdulali,
The Merge Ranges addin can do that for you, check it out:
https://www.getdigitalhelp.com/2010/02/28/combinedatafrommultiplesheetsinexcel/#mergeranges
This method does NOT work... Unless I am completely missing something. I am referring to the first method listed with all basic excel formulas. You find the max IDs as 28 in a formula and then you just manually type that in to your unique list step? What?
How is that automated at all. I am referring to:
=SMALL(IF(FREQUENCY(Jan:Mar!$A$2:$A$1000, ROW($1:$28))0, ROW($1:$28), ""), ROW(A1)) + CTRL + SHIFT + ENTER
This is only useful for finding a unique list of a never changing table as even tested in your example if I simply add an extra value to one of your tabs your entire sheet dies.
Can you address this?
Thanks!
Dear Honorable Team
I am working in Manpower Company.
My Problem is that i have Multipal Sheets in excel
For Example I have a Timesheet For January Like this
Name ID Hours Rate Amount
X 2 100 10 1000
i want a unique sheet that collect automatically and plus all the data in separate sheet so i can easily maintain all my data.Please Help me if its possible.
Hi Oscar,
Can not tell you how much your site has helped me. I know this is an older post, but I am stuck and would imagine some others could have this question.
I am wondering if there is a way to use the formula you created to "sort unique distinct list of numbers originating from multiple sheets" while also filtering for multiple conditions?
So imagine if in the example you gave not only is there an ID number but also a column with product code, a column with territory ID, and a column with the date of the transaction. I want this formula to return the ID number that matches 3 different criteria pertaining to those 3 columns, across multiple sheets, and only allowing unique values.
Thank you for all your help in excel!
Chris,
I don´t think it is possible, you need a user defined function (vba).