Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
carl
 
Posts: n/a
Default Replacing MCONCAT with CONCAT_RANGE (UDF)

Currently using this formula:

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

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Carl,

If you are going to use a UDF, you might as well write one that does exactly
what you want. So instead of cobbling together non-custom UDF's, post an
example of what you have and what result you want.

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Currently using this formula:


=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5: E1074;ROW(E5:E1074)-MIN(RO
W(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.



  #3   Report Post  
carl
 
Posts: n/a
Default

Thank You Bernie.

Can I send you a copy of my sheet with a description of what I am trying to
accomplish ?


"Bernie Deitrick" wrote:

Carl,

If you are going to use a UDF, you might as well write one that does exactly
what you want. So instead of cobbling together non-custom UDF's, post an
example of what you have and what result you want.

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Currently using this formula:


=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5: E1074;ROW(E5:E1074)-MIN(RO
W(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.




  #4   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Sure. Take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Thank You Bernie.

Can I send you a copy of my sheet with a description of what I am trying

to
accomplish ?


"Bernie Deitrick" wrote:

Carl,

If you are going to use a UDF, you might as well write one that does

exactly
what you want. So instead of cobbling together non-custom UDF's, post an
example of what you have and what result you want.

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Currently using this formula:



=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5: E1074;ROW(E5:E1074)-MIN(RO
W(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.






  #5   Report Post  
JulieD
 
Posts: n/a
Default

Hi Bernie

would be interested in any feedback with regards to the CONCAT_RANGE
function that you might have.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sure. Take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Thank You Bernie.

Can I send you a copy of my sheet with a description of what I am trying

to
accomplish ?


"Bernie Deitrick" wrote:

Carl,

If you are going to use a UDF, you might as well write one that does

exactly
what you want. So instead of cobbling together non-custom UDF's, post
an
example of what you have and what result you want.

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Currently using this formula:



=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5: E1074;ROW(E5:E1074)-MIN(RO
W(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.










  #6   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Julie,

Will do, if I get the workbook from Carl.

HTH,
Bernie
MS Excel MVP


"JulieD" wrote in message
...
Hi Bernie

would be interested in any feedback with regards to the CONCAT_RANGE
function that you might have.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sure. Take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Thank You Bernie.

Can I send you a copy of my sheet with a description of what I am

trying
to
accomplish ?


"Bernie Deitrick" wrote:

Carl,

If you are going to use a UDF, you might as well write one that does

exactly
what you want. So instead of cobbling together non-custom UDF's, post
an
example of what you have and what result you want.

HTH,
Bernie
MS Excel MVP


"carl" wrote in message
...
Currently using this formula:




=MID(MCONCAT(UNIQUEVALUES(IF(SUBTOTAL(3;OFFSET(E5: E1074;ROW(E5:E1074)-MIN(RO
W(E5:E1074));;1));";"&E5:E1074;"")));2;8^8)

Would like to replace the MCONCAT function with the CONCAT_RANGE
--=CONCAT_RANGE(E5:E1074;";") -- UDF (Thank you to JulieD
http://www.hcts.net.au/tipsandtricks.htm for pointing
me in the direction of this UDF)

Is this possible. I tried but could not get it to work.

Thank you in advance.










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replacing MCONCAT with CONCAT_RANGE (in a complicated formula) carl Excel Worksheet Functions 0 April 21st 05 05:43 PM
MCONCAT only Visible Cells carl Excel Worksheet Functions 4 April 13th 05 12:09 PM
replacing TRUE/FALSE Rogueuk New Users to Excel 0 February 9th 05 11:45 AM
MCONCAT carl Excel Worksheet Functions 3 December 31st 04 10:58 PM
How do I stop excel replacing numerical values with the date? Becca C Excel Discussion (Misc queries) 2 December 21st 04 12:12 PM


All times are GMT +1. The time now is 07:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"