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.

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.

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

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

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

Thank you. This is a very useful UDF. Is it possbile to add a second (third)
criteria. For example:

ColA ColB ColC
AB 123 1
CD 456 2
EF 123 4
GF 123 1


concatenate only if colb=123 and colc=1

thank you again for your help.


"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.

mmm will have to think about it .. check my website in a month or two -you
never know
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
Thank you. This is a very useful UDF. Is it possbile to add a second
(third)
criteria. For example:

ColA ColB ColC
AB 123 1
CD 456 2
EF 123 4
GF 123 1


concatenate only if colb=123 and colc=1

thank you again for your help.


"JulieD" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.