|
Modifying a Formula To display only Unique Values
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: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. |
| All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com