| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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. |
|
#7
|
|||
|
|||
|
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. |
|
#8
|
|||
|
|||
|
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. |
|
#9
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replacing MCONCAT with CONCAT_RANGE (in a complicated formula) | Excel Worksheet Functions | |||
| MCONCAT only Visible Cells | Excel Worksheet Functions | |||
| replacing TRUE/FALSE | New Users to Excel | |||
| MCONCAT | Excel Worksheet Functions | |||
| How do I stop excel replacing numerical values with the date? | Excel Discussion (Misc queries) | |||
Linear Mode
