Remove range limitation of formula & extend to whole column & possibly execute in VBA?

Windows 10
Excel 2019


I have the below formula which looks at the range E2:E100 and if it finds each cell in the range has the Number 1 it returns the text "M", if it finds each cell in the range has the Number 2 it returns the text "S". If it finds a mix of the numbers 1 and 2 it returns the text "M+S".

In the E Column there will always be only the Numbers 1 and 2.

Please Login or Register  to view this content.

I would like to remove the range limitation of E2:E100 and have the formula look down all of Column E and where it finds unique data in Column B, then look down the whole of Column E and if it finds each cell in the range has the Number 1 it returns the text "M", if it finds each cell in the range has the Number 2 it returns the text "S". If it finds a mix of the numbers 1 and 2 it returns the text "M+S".

An example would be:

Please Login or Register  to view this content.

I also have the VBA code below that looks at Column E and for each unique range in Column E, it alternately colours each range from Column A to Column J.

I wonder if, as an alternate or better way to run the above formula, it could be incorporated into the below
function. I am unsure if it is better to do it this way or just use the formula on its own.

Please Login or Register  to view this content.

Here are 2 different solutions with a formula suitable for Excel 2019:

Please try in F2 and copy down:

Formula:

Please Login or Register  to view this content.

or try in F2 and copy down:

Formula:

Please Login or Register  to view this content.

You are Welcome!

Thanks for the feedback and rep .

Glad to have helped.