Retrieve Unique Values from Columns in Multiple Worksheets

Hello,

I’d like to capture unique strings from a column on multiple worksheets while also accounting for blanks. I’m currently using the following nested INDEX/MATCH/COUNTIF formula (array) to retrieve the values:

Please Login or Register  to view this content.

While this works somewhat, it has a number of problems:

1. I need to capture the unique values for 9 worksheets so nesting this many IFERROR blocks would be difficult to create/maintain
2. My understanding is that nested IFERROR blocks significantly impact performance
3. Because the unique values are captured per worksheet, adding a new value in any worksheet except the last one adds the new value inside of the existing list causing the associated columns to be wrong. In the attached spreadsheet, if you add “Trainer” into A2 of Sheet2, the 80 Hours which were associated with Developer are now incorrectly assigned to the newly added Trainer row.

Is there are better way to capture the unique values (accounting for blanks), as well as ensure that new values are added to the end of the list instead of in the middle?

Constraints:

• Cannot use Macros or VBA code
• Solution must work in SharePoint Online
• Must work in Office 365

Thanks for replying Tim201110,

I notice your example has 0s in the source field (mine would be NULL) so I should be able to switch <>0 to <>"" to resolve. The biggest thing is new values are still inserted in the middle of the list instead of at the end. So in your example, if I add "Trainer" into K3 it moves A6's content (Developer in this case) down into A7 - causing the resource hours not to match anymore.

Thoughts on how to resolve this piece?

without macros - no way