|
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. |
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. |
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. |
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. |
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. |
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. |
hi bernie. i sent you the workbook. please note my global settings are ";"
versus ",". "Bernie Deitrick" wrote: 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. |
Carl,
Below is a UDF that takes one range and three optional arguments: a separator string, true/false for return only filtered, and true/false to return only unique values. It is used like this, in your workbook: =ConcatUF(D5:D1074,";",TRUE,TRUE) It could be used like =ConcatUF(D5:D1074,,,TRUE) =ConcatUF(D5:D1074,";",TRUE) =ConcatUF(D5:D1074,";",,TRUE) =ConcatUF(D5:D1074,";") HTH, Bernie MS Excel MVP Function ConcatUF(rngInput As Range, _ Optional strSep As String, _ Optional boolFiltered As Boolean, _ Optional boolUnique As Boolean) As String Dim myCell As Range For Each myCell In rngInput If (Not boolFiltered Or _ (boolFiltered And (Application.Subtotal(3, myCell) = 1))) And _ ((Not boolUnique) Or _ (boolUnique And (InStr(1, ConcatUF, myCell.Value) = 0))) Then If ConcatUF = "" Then ConcatUF = myCell.Value Else ConcatUF = ConcatUF & strSep & myCell.Value End If End If Next myCell End Function "carl" wrote in message ... hi bernie. i sent you the workbook. please note my global settings are ";" versus ",". "Bernie Deitrick" wrote: 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. |
For consistency with the name, I should have defined the function with the
parameters ordered as Function ConcatUF(rngInput As Range, _ Optional strSep As String, _ Optional boolUnique As Boolean, _ Optional boolFiltered As Boolean) As String I had initially use the name ConcatFU, but then figured that wasn't nice.... Bernie "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Carl, Below is a UDF that takes one range and three optional arguments: a separator string, true/false for return only filtered, and true/false to return only unique values. It is used like this, in your workbook: =ConcatUF(D5:D1074,";",TRUE,TRUE) It could be used like =ConcatUF(D5:D1074,,,TRUE) =ConcatUF(D5:D1074,";",TRUE) =ConcatUF(D5:D1074,";",,TRUE) =ConcatUF(D5:D1074,";") HTH, Bernie MS Excel MVP Function ConcatUF(rngInput As Range, _ Optional strSep As String, _ Optional boolFiltered As Boolean, _ Optional boolUnique As Boolean) As String Dim myCell As Range For Each myCell In rngInput If (Not boolFiltered Or _ (boolFiltered And (Application.Subtotal(3, myCell) = 1))) And _ ((Not boolUnique) Or _ (boolUnique And (InStr(1, ConcatUF, myCell.Value) = 0))) Then If ConcatUF = "" Then ConcatUF = myCell.Value Else ConcatUF = ConcatUF & strSep & myCell.Value End If End If Next myCell End Function "carl" wrote in message ... hi bernie. i sent you the workbook. please note my global settings are ";" versus ",". "Bernie Deitrick" wrote: 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. |
| All times are GMT +1. The time now is 02:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com