I am trying to create a master sheet that shows unique items from cells C7:C16 across 40 worksheets, and list them in each category they are attached to according to cells G7:I16 (they can be attached to up to 3 different categories) on the master sheet.
The idea is to be able to enter all the criteria for each unit separately into the appropriate worksheet (column c), apply it to an outcome/outcomes (columns G:I), and then have the macro (or formula, if that is easier?) compile the data into the outcome categories on the master showing each unique criterion in each category (which means a criterion can be found in more than one category, but only once in each). This way we can see at a glance if the required outcomes are being met by the criteria or not. At the moment, with 9 outcome categories and 9 criteria per category across 40 units, it is a big task to see if there is any deficiency. Being able to compile the list, we can both quantify and qualify the criteria to ensure it meets the required outcomes.
I have attached a zipped version of a cut down example showing 8 sample worksheets, the master worksheet (called 'Learning outcomes'), an attempt at writing a macro to compile the data into a single list (worksheet entitled 'ULO list') and another worksheet. The full workbook has 10 worksheets that compile data (including 'Learning outcomes') and 40 individual worksheets with the actual data for each component which is manually entered.
Thank you for your help.