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:

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

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.

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.

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.

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.

Download excel tutorial file

Get data from each sheet.xls
(Excel 97-2003  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 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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
  2. Split data across multiple sheets in excel (vba)
  3. Count unique distinct numbers across multiple sheets (3D range) in excel
  4. Search for multiple text strings in multiple cells and use in data validation in excel
  5. Create a list with most recent data available in excel
  6. Return multiple values if in range in excel
  7. Categorize values into multiple columns (excel formulas)
  8. Filter duplicates within same date, week or month in excel
  9. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  10. Lookup with multiple criteria and display multiple search results using excel formula, part 4