Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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; |
#2
|
|||
|
|||
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; |
#3
|
|||
|
|||
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; |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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; |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MCONCAT only Visible Cells | Excel Worksheet Functions | |||
AutoFill Visible Cells with Months | New Users to Excel | |||
Select Visible Cells Only | Excel Discussion (Misc queries) | |||
Copy & Paste Visible Cells with Formulas | Excel Worksheet Functions | |||
SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY | Excel Worksheet Functions |