MCONCAT only Visible Cells

[carl]

I use this formula to concat after I use the auto filter:

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

My data looks like so:

AM
AM
AM
AE
AE
AE
AB
AB
AB
AB
AB
AB

For example, if I filter down to "AB", the formula gives me the following
result:

;AB;AB;AB;AB;AB;AB

Is there a way to modify the formula to only show me the unique value, in
this example

AB;

 

[Daniel.M]

Carl,

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

Use UNIQUEVALUES() fonction available in the same XLL you're using.

So, this array formula:
=MID(MCONCAT(UNIQUEVALUES(
IF(SUBTOTAL(3;OFFSET(E5:E1074;ROW(E5:E1074)-
MIN(ROW(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Regards,

Daniel M.

 

[carl]

Thank you Dan. The formula you suggested:

Use UNIQUEVALUES() fonction available in the same XLL you're using.

So, this array formula:
=MID(MCONCAT(UNIQUEVALUES(
IF(SUBTOTAL(3;OFFSET(E5:E1074;ROW(E5:E1074)-
MIN(ROW(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

In the last argument, 8^8. Do I enter the formula exactly as shown ?

 

[Daniel.M]

So, this array formula:
=MID(MCONCAT(UNIQUEVALUES(
IF(SUBTOTAL(3;OFFSET(E5:E1074;ROW(E5:E1074)-
MIN(ROW(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

In the last argument, 8^8. Do I enter the formula exactly as shown ?

Yes.
Or 999 if you want. Or any number high enough.

Regards,

Daniel M.

 

[Aladin Akyurek]

Did you see

http://tinyurl.com/9xfau

which also offers an answer?

 

[carl]

Thank you again.

I recently posted this:

Thank you to Daniel M for helping me to put togther this formula:
=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5:E1074;ROW(E5:E1074)-MIN(ROW(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Which allows me to create a string based on how I have my auto-filtering. It
works fantastically.

I have now discovered that the MCONCAT formula appears to have a limitation
on the number of characters it can concatenate - I think it's around 50.

Thank you to JulieD http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF:

=CONCAT_RANGE(E5:E1074;";")

I would like to replace the MCONCAT function above with the CONCAT_RANGE
function. I've tried but can't seem to get it to work.

Thank you in advance.

Do you have a thought on making this modification ?

 

[Daniel.M]

Hi,

I have now discovered that the MCONCAT formula appears to have a limitation
on the number of characters it can concatenate - I think it's around 50.

More around 256 cars but anyhow...

I would like to replace the MCONCAT function above with the CONCAT_RANGE
function. I've tried but can't seem to get it to work.

Try with a function concatenate that works with arrays (not only RANGES).
Here's one from Harlan Grove :
http://groups.google.com/[email protected]

It's VBA so obviously slower than MCONCAT (XLL, C compiled) so see if it fits
you.

Regards,

Daniel M.