Modifying a Formula To display only Unique Values

C

carl

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.
 
A

Aladin Akyurek

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF Statement on multiple levels 6
MCONCAT only Visible Cells 6
Key Card Software (Auric) 7
If Function 1
Replacing MCONCAT with CONCAT_RANGE (UDF) 8
Subtotal question 4
Excel Macro or Formula Question 1
Replacing MCONCAT with CONCAT_RANGE (in a complicated formula) 0

Top