MCONCAT only Visible Cells
 

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;

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" wrote in message
...
I use this formula to concat after I use the auto filter:



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;

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" wrote:

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" wrote in message
...
I use this formula to concat after I use the auto filter:



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;

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.

Did you see

http://tinyurl.com/9xfau

which also offers an answer?

carl wrote:
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;

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" wrote:


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.

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/groups?hl=f...rldnet.att.net

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

Regards,

Daniel M.