E1:

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(E5:E1075,ROW(E5:E1 075)-MIN(ROW(E5:E1075)),,1)),E5:E1075),,FALSE)

E2:

=SUBSTITUTE(SUBSTITUTE(MCONCAT(IF(SUBTOTAL(3,OFFSE T(E5:E1075,ROW(E5:E1075)-MIN(ROW(E5:E1075)),,1)),";"&UNIQUEVALUES(E5:E1075, 1),"")),";","",1),";","",E1)

I assume that you have the latest version version of the morefunc.xll
add-in.

Note that both formulas must be confirmed with control+shift+enter.

Replace comma's with semi-colons on your version of Excel.

carl wrote:
I am using this formula:

=SUBSTITUTE(MCONCAT(IF(SUBTOTAL(3;OFFSET(E5:E1075; ROW(E5:E1075)-MIN(ROW(E5:E1075));;1));";"&E5:E1075;""));":";"";1 )

Because of the data, it can produce a result like this:

;L0;L0;AB;AB;L0;AB

Is there a way to modify the formula so that it produces:

;L0;AB

Thank you in advance.