Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a formula for conditional concatenating?
I would like to concatenate cells from a column based on the match in the
preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#2
|
|||
|
|||
Hi Erny
no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#3
|
|||
|
|||
Thanks Julie, very much appreciate, actually I wished to avoid any
programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#4
|
|||
|
|||
Hi Erny
i don't think MS people come in here to read "hints" :) Harald Staff and i were playing around with this idea - as another poster raised it a few days ago - and i did a limited concat_if funciton check out http://tinyurl.com/5wzuz for the discussion and the'function' ... you can put it in your personal.xls file and then access it via the paste function wizard under user defined functions. Cheers JulieD "Erny Meyer" wrote in message ... Thanks Julie, very much appreciate, actually I wished to avoid any programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#5
|
|||
|
|||
Hi Erny
if you're going to give my UDF a go, the ConcRange in your case would be A1:B5 the ConcCrit would be either T1 or the cell reference holding T1 and the DelimitWith would be a comma and/or space to separate Jim and Joe - when i figure out how to put help into a UDF it will make life easier Cheers JulieD "JulieD" wrote in message ... Hi Erny i don't think MS people come in here to read "hints" :) Harald Staff and i were playing around with this idea - as another poster raised it a few days ago - and i did a limited concat_if funciton check out http://tinyurl.com/5wzuz for the discussion and the'function' ... you can put it in your personal.xls file and then access it via the paste function wizard under user defined functions. Cheers JulieD "Erny Meyer" wrote in message ... Thanks Julie, very much appreciate, actually I wished to avoid any programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#6
|
|||
|
|||
Thanks Julie & Carey,
I looked at this and it is very useful; I may slightly modify the function to include the criteria checking range (which is in my case a different one from the concatenation range), so maybe 4 parameters instead of 3, but it will solve my problem. Cheers, Erny "JulieD" wrote: Hi Erny i don't think MS people come in here to read "hints" :) Harald Staff and i were playing around with this idea - as another poster raised it a few days ago - and i did a limited concat_if funciton check out http://tinyurl.com/5wzuz for the discussion and the'function' ... you can put it in your personal.xls file and then access it via the paste function wizard under user defined functions. Cheers JulieD "Erny Meyer" wrote in message ... Thanks Julie, very much appreciate, actually I wished to avoid any programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#7
|
|||
|
|||
Hi again,
Correction to my last one (I had not seen your 2nd reply yet): this makes it even easier. I tried it and it worked. Thanks again and ciaooo, Erny "JulieD" wrote: Hi Erny if you're going to give my UDF a go, the ConcRange in your case would be A1:B5 the ConcCrit would be either T1 or the cell reference holding T1 and the DelimitWith would be a comma and/or space to separate Jim and Joe - when i figure out how to put help into a UDF it will make life easier Cheers JulieD "JulieD" wrote in message ... Hi Erny i don't think MS people come in here to read "hints" :) Harald Staff and i were playing around with this idea - as another poster raised it a few days ago - and i did a limited concat_if funciton check out http://tinyurl.com/5wzuz for the discussion and the'function' ... you can put it in your personal.xls file and then access it via the paste function wizard under user defined functions. Cheers JulieD "Erny Meyer" wrote in message ... Thanks Julie, very much appreciate, actually I wished to avoid any programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#8
|
|||
|
|||
glad it worked.
"Erny Meyer" wrote in message ... Hi again, Correction to my last one (I had not seen your 2nd reply yet): this makes it even easier. I tried it and it worked. Thanks again and ciaooo, Erny "JulieD" wrote: Hi Erny if you're going to give my UDF a go, the ConcRange in your case would be A1:B5 the ConcCrit would be either T1 or the cell reference holding T1 and the DelimitWith would be a comma and/or space to separate Jim and Joe - when i figure out how to put help into a UDF it will make life easier Cheers JulieD "JulieD" wrote in message ... Hi Erny i don't think MS people come in here to read "hints" :) Harald Staff and i were playing around with this idea - as another poster raised it a few days ago - and i did a limited concat_if funciton check out http://tinyurl.com/5wzuz for the discussion and the'function' ... you can put it in your personal.xls file and then access it via the paste function wizard under user defined functions. Cheers JulieD "Erny Meyer" wrote in message ... Thanks Julie, very much appreciate, actually I wished to avoid any programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#9
|
|||
|
|||
btw i have another version which allows both text and numbers as the
criteria ... if you'ld like it email me direct at julied_ng at hcts dot net dot au Cheers "JulieD" wrote in message ... glad it worked. "Erny Meyer" wrote in message ... Hi again, Correction to my last one (I had not seen your 2nd reply yet): this makes it even easier. I tried it and it worked. Thanks again and ciaooo, Erny "JulieD" wrote: Hi Erny if you're going to give my UDF a go, the ConcRange in your case would be A1:B5 the ConcCrit would be either T1 or the cell reference holding T1 and the DelimitWith would be a comma and/or space to separate Jim and Joe - when i figure out how to put help into a UDF it will make life easier Cheers JulieD "JulieD" wrote in message ... Hi Erny i don't think MS people come in here to read "hints" :) Harald Staff and i were playing around with this idea - as another poster raised it a few days ago - and i did a limited concat_if funciton check out http://tinyurl.com/5wzuz for the discussion and the'function' ... you can put it in your personal.xls file and then access it via the paste function wizard under user defined functions. Cheers JulieD "Erny Meyer" wrote in message ... Thanks Julie, very much appreciate, actually I wished to avoid any programming, as I will need to paste whatever formula I could find to a larger range (lots of reference values and lots of names). I was wondering whether something similar to the SUMIF formula for values could existe such as a CONCATENATEIF for strings (maybe a hint to microsoft to add a functionality). I will use your code as an option. Kind regards, Erny "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula for conditional concatenating?
I think is the answer I am searching for. Not sure how to implement it. Can
someone help me? Thanks, Wanda "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula for conditional concatenating?
I also pine for the day when the concatenateif function was available. "Erny Meyer" wrote: I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula for conditional concatenating?
Julie, Harold Staff,
Thanks for your efforts! The following version looks like your best version yet. (The best solution was found he http://www.officekb.com/Uwe/Forum.as...-ConcatenateIF) --------- Public Function CONCAT_IF(ConcCheck As Range, _ ConcCrit As Variant, _ Optional ConcRange As Range, _ Optional DelimitWith As String) As String ' 'Version Posted: 01 Apr 2005 01:19 GMT 'Written by JulieD and Harald Staff ' 'ConcCheck - range to check for the criteria 'ConcCrit - the criteria 'ConcRange - range to concatenation 'DelimitWith - the delimination character(s) Dim Cel As Range Dim i As Long, j As Long Dim checkarray() As String Dim rangearray() As String If ConcRange Is Nothing Then Set ConcRange = ConcCheck i = ConcCheck.Count j = ConcRange.Count If i < j Then Exit Function End If ReDim checkarray(i - 1) ReDim rangearray(i - 1) i = 0 For Each Cel In ConcCheck checkarray(i) = Cel.Text i = i + 1 Next i = 0 For Each Cel In ConcRange rangearray(i) = Cel.Text i = i + 1 Next For i = 0 To j - 1 If checkarray(i) = ConcCrit Then CONCAT_IF = _ CONCAT_IF & rangearray(i) & DelimitWith Next If CONCAT_IF < "" Then _ CONCAT_IF = Left$(CONCAT_IF, _ Len(CONCAT_IF) - Len(DelimitWith)) End Function ------- "JulieD" wrote: Hi Erny no formula that i know of (someone else might know) but this code will do it for you -- Sub concatvals() Dim strvalue As String Dim strsearch As String For Each cell In Range("D1:D100") If Not IsNull(cell.Value) Then strsearch = cell.Value strvalue = "" For Each c In Range("A1:A100") If c.Value = strsearch Then If Len(strvalue) < 1 Then strvalue = c.Offset(0, 1).Value Else strvalue = strvalue & ", " & c.Offset(0, 1).Value End If End If Next End If cell.Offset(0, 1).Value = strvalue Next End Sub --- where D1:D100 contains the unique references from column A where E1:E100 is where the results can be put where A1:A100 is your lists of codes where B1:B100 is the list of people you want to concatenation in to column E next to the appropriate code id D --- if you would like assistance implementing the code, please post back Cheers JulieD "Erny Meyer" wrote in message ... I would like to concatenate cells from a column based on the match in the preceding column with a defined value. Example: Col. A B Row1: T1 Jim Row2: T3 Paul Row3: T1 Joe Row4: T2 Mike Row5: T3 Caroline The formula should return for T1 for instance: Jim;Joe; (for T2: Mike; for T3: Paul;Caroline;) Cells in column A can repeat any number of times. Thanks in advance, Erny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Concatenating two conditions in a "IF" Formula. | New Users to Excel |