Modifying a Formula To display only Unique Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
E1:

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

E2:

=SUBSTITUTE(SUBSTITUTE(MCONCAT(IF(SUBTOTAL(3,OFFSET(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.
 
Back
Top