Collate and Index Info

Hi I am new to this site, and not an expert when it comes to using Excel... which is why I'm here and hoping you guys may be able to help me :-)


Is there an easy way in which Excel can collect and collate information/data? This data can sometimes be in form of text, as well as numerical value.

For example, my worksheet comprises of modules (Units, if you will) which each features its own list of criteria. As seen in my attached example spreadsheet. When an entry covers criteria from a given unit it is represented by "Y". If not, it is left empty. The first thing I need is a formula that can collate all these "Y"'s and list them in one cell, but converting the Y to the value of the criteria it is covering. (Refer to example sheet for better understanding of this!)

On the example, there are three sheets: Main, Unit 1 and Unit 2.

Main is used to contain all the collected data for each and every entry and all the units it has covered.

Unit 1 and Unit 2 sheets are examples of how the entries should be collated, listed and indexed as per unit coverage. (Again refer to example).

The example spreadsheet merely shows how it SHOULD work (a static if you like - there are no formulas as that's the help I need). :-)

If there is a SIMPLE and EFFECTIVE way of doing this that you guys can help me with, GREAT! Many thanks for your help :-)