MCONCAT only Visible Cells

[carl]

I use MCONCAT (Laurent Longre's MOREFUNC.XLL). I often need to filter my
spreadsheet and work with only a portion of the data. I was hoping there was
a way to use the MCONCAT to concatenate only the "visible cells".

My spreadsheet looks like this:

ColA ColB
AB 123
CD 456
EF 123

If I filter ColB down to 123, I would like the MCONCAT to concatenate ColA
Values AB and EF.

Thank you for thinking about this.

Best Regards.

 

[JulieD]

Hi Carl

not sure that this is the total solution to your problem, but on my website
(www.hcts.net.au/tipsandtricks.htm) i have a concat_if function, which
concatenates a range based on values in another range ... so you could use
this UDF to do
=CONCAT_IF(B1:B3,123,A1:A3,",")
to end up with
AB,EF

 

[Aladin Akyurek]

=SUBSTITUTE(MCONCAT(IF(SUBTOTAL(3,OFFSET(A3:A10,ROW(A3:A10)-MIN(ROW(A3:A10)),,1)),","&A3:A10,"")),",","",1)

which you need to confirm with control+shift+enter instead of just with
enter.

 

[JulieD]

mmm will have to think about it .. check my website in a month or two -you
never know