Article updated on January 24, 2018

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

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:

=MAX(Jan:Mar!A2:A1000) + Enter

Recommended article

Merge two columns

The picture above shows how to merge two columns into one list using a formula. If you are looking […]

### 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):

=SMALL(IF(FREQUENCY(Jan:Mar!\$A\$2:\$A\$1000, ROW(\$1:\$28))<>0, ROW(\$1:\$28), ""), ROW(A1)) + CTRL + SHIFT + ENTER

copied down as far as needed.

How to create a unique distinct list sorted alphabetically:

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

### Combine data from multiple sheets

Formula in B4:

=IF(ISERROR(MATCH(A4, Jan!\$A\$2:\$A\$10, 0)), "", INDEX(Jan!\$B\$2:\$B\$10, MATCH(A4, Jan!\$A\$2:\$A\$10, 0))) + CTRL + SHIFT + ENTER

copied down as far as needed.

Recommended article:

Merge two columns with possible blank cells

Formula in C4:

=IF(ISERROR(MATCH(A4, Feb!\$A\$2:\$A\$10, 0)), "", INDEX(Feb!\$B\$2:\$B\$10, MATCH(A4, Feb!\$A\$2:\$A\$10, 0))) + CTRL + SHIFT + ENTER

copied down as far as needed.

Recommended article:

Consolidate sheets [vba]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Formula in D4:

=IF(ISERROR(MATCH(A4, Mar!\$A\$2:\$A\$10, 0)), "", INDEX(Mar!\$B\$2:\$B\$10, MATCH(A4, Mar!\$A\$2:\$A\$10, 0))) + CTRL + SHIFT + ENTER

copied down as far as needed.

Recommended post:

Merge tables based on a condition

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]

Get-data-from-each-sheet.xls
(Excel 97-2003  Workbook *.xls)

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 k-th 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 is an add-in 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:

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:

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 Add-in found one duplicate record from all three ranges combined.

### What you get

• Merge Ranges Add-in 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 Add-In for Excel 2007/2010/2013 - Price \$19 US

Purchase Merge Ranges Add-In 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.

1. Type user defined function in formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Is there a money back guarantee?

Sure, you have un-conditional 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 Add-In for Excel 2007/2010/2013 - Price \$19 US

Purchase Merge Ranges Add-In 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.