Automatic & Unique Data Consolidation Across Worksheets

[B1313]

I have 6 worksheets:

1. Aug (2015)
2. Sept (2015)
3. Oct (2015)
4. Nov (2015)
5. Dec (2015)
6. Jan (2016)

Each is formatted EXACTLY the same. The data contains individuals and their specific performance information for the respective period. There may be someone who appears on 1 or more sheets and their maybe a person who appears on less than all of them.

I need to make another sheet with all the individuals (unique, they need to be represented at least once but never more than once) in a single column and then their specific month data points across columns. A summary of the previous 6 sheets.

My solution: Combine all names into 1 massive list then create another list with only unique names (ie removing duplicates). The problem is the formula to do so is GIGANTIC and EXTREMELY resource intensive, to the point I can't run it on anyone else's PC other than my own custom PC.

Question: Is there a way to achieve my goal in a more efficient manner? VBA is not a problem, though I am not profusely versed in Excel's native version of VB. Is there a more efficient formula?

My current formula:

=IFERROR(INDEX(INDIRECT($B$6, TRUE), ROWS(B$13:$B14)), IFERROR(INDEX(INDIRECT($B$7, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE))), IFERROR(INDEX(INDIRECT($B$8, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7))), IFERROR(INDEX(INDIRECT($B$9, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8))), IFERROR(INDEX(INDIRECT($B$10, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8)) - ROWS(INDIRECT($B$9, TRUE))), IFERROR(INDEX(INDIRECT($B$11, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8)) - ROWS(INDIRECT($B$9, TRUE)) - ROWS(INDIRECT($B$10, TRUE))),IFERROR(INDEX(INDIRECT($B$12, TRUE), ROWS(B$13:$B14) - ROWS(INDIRECT($B$6, TRUE)) - ROWS(INDIRECT($B$7)) - ROWS(INDIRECT($B$8)) - ROWS(INDIRECT($B$9, TRUE)) - ROWS(INDIRECT($B$10, TRUE)) - ROWS(INDIRECT($B$11, TRUE))),"")))))))

-- Array Formula to make combined list.

=INDEX(TotalNameListRange, MATCH(0, COUNTIF($D$16:D16, TotalNameListRange), 0))

-- Array formula to disseminate unique data from the total combined name list.

$B$6:$B$12 contain the worksheets in the following format ("'Aug (2015)'$C$17:$C$203") These helper cells calculate the range of the names in each sheet so I do not have to use $C$17:$C$9999 to account for the fluctuation in the data. They are ADDRESS formulas.

All Names start in the $C$17 cell and proceed down. There are NO blanks in between names (ie C17 = "John Doe", C18 = "Sally Sample", etc).

NOTE: The data will change weekly (hourly in real time, but I will only consolidate weekly). So doing this manually is NOT an option. Also, the tabs will change monthly (ie Add preceding month and drop the preceding 6th month).

Any and all advice is welcome. Thank you!

 

[B1313]

...

NOTE 2: Once I have the unique names in a single column, I planned on just doing INDEX-MATCH on each individual to pull the data for that individual for each respective month. This, in theory, should work perfectly. Let me know if this should be scrapped in favor of a better solution, VBA maybe?

 

[B1313]

Bump. Any ideas?