| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can Excel represent formula in textural format with values substi. | Excel Worksheet Functions | |||
| FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS | Excel Discussion (Misc queries) | |||
| Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
| Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
| How do I get unique values from 2 columns? | Excel Discussion (Misc queries) | |||
Linear Mode
