Can this be done
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
You can specify CHAR(10) as the very last argument of ConcatenateIfs. In one of the posts above, I used the example
=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,", ")
To separate the entries by new lines, change it to
=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,CHAR(10))
You should turn on Wrap Text for the cell with the formula.
You can specify CHAR(10) as the very last argument of ConcatenateIfs. In one of the posts above, I used the example
=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,", ")
To separate the entries by new lines, change it to
=ConcatenateIfs(B2:B100,B2:B100,"=","*"&L1&"*",A2:A100,"=",L1,CHAR(10))
You should turn on Wrap Text for the cell with the formula.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 20 May 2015, 19:52
Re: Can this be done
Wow, works like a charm Hans ! I've been strugguling for days trying to insert the vbnewline.
Thank you very much !
Voff
Thank you very much !
Voff
-
- NewLounger
- Posts: 5
- Joined: 29 Sep 2015, 22:03
Re: Can this be done
HansV - I hope your still monitoring this posting. I was wondering if ConcatenateIfs works with named ranges?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Can this be done
Hi Virgo,
Welcome to Eileen's Lounge.
Hans is an Admin on this site and frequents it regularly still.
The ConcatenateIfs() function can take range names. Excel (AFAIK) does not differentiate between range names and rage references; it is the same thing to Excel.
Attached is an example of ConcatenateIfs() with a range name in the first argument (Name range called DataA).
Welcome to Eileen's Lounge.
Hans is an Admin on this site and frequents it regularly still.
The ConcatenateIfs() function can take range names. Excel (AFAIK) does not differentiate between range names and rage references; it is the same thing to Excel.
Attached is an example of ConcatenateIfs() with a range name in the first argument (Name range called DataA).
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
I'm travelling at the moment and I can't check this until next week. But I see that Rudi has already tested it for you (thanks, Rudi).
I'm travelling at the moment and I can't check this until next week. But I see that Rudi has already tested it for you (thanks, Rudi).
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 29 Sep 2015, 22:03
Re: Can this be done
Thanks Rudy. I was also wondering if the sorting capability could be added to the ConcatenateIf() function as well.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
The version that I posted in this thread and that Rudi used in his sample workbook already sorts the result. For example, in cell D2 on Query Sheet, you see
1,9,18,27,35
The concatenated values are sorted in ascending order, although the DataA range on Data Sheet is not sorted.
If you want to sort by other criteria, the code would have to be customized. If you indicate what you want, it may well be possible.
1,9,18,27,35
The concatenated values are sorted in ascending order, although the DataA range on Data Sheet is not sorted.
If you want to sort by other criteria, the code would have to be customized. If you indicate what you want, it may well be possible.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 08 Oct 2015, 16:05
Re: Can this be done
I used the code provided to create the concatenateif formula, however, it gives me the #Name# result.
would you please check it for me in the Linked template tab Cell G3 and let me know what is wrong in the VBA or in the formula
your help will be highly appreciated :)
would you please check it for me in the Linked template tab Cell G3 and let me know what is wrong in the VBA or in the formula
your help will be highly appreciated :)
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
Did you intend to attach a sample workbook?
Did you intend to attach a sample workbook?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 08 Oct 2015, 16:05
Re: Can this be done
my Appologies as i thought that i did attach the file
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
The cause of the problem is that you have given the function and the module that contains it the same name. This confuses Excel. If you give the module another name, for example basConcatenateIf, the function will work. You will have to recalculate the formula once, for example by selecting the cell with the formula, pressing F2 then enter.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 08 Oct 2015, 16:05
Re: Can this be done
Thanks a lot for the help as it works now, only thing i had to change is to make sure that the criteria cell needs to be in c=values if numbers due to the way it was recorded in the look up range.
one more question though is that when I do any change in the formula in the file that was converted to macro after this VBA, the file get stock and keeps working forever, any suggestion for how should i fix this issue?
one more question though is that when I do any change in the formula in the file that was converted to macro after this VBA, the file get stock and keeps working forever, any suggestion for how should i fix this issue?
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
The VBA function has to perform a large amount of calculations, so if you have many formulas using this function, recalculating will cost a lot of time; that is unavoidable.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 29 Sep 2015, 22:03
Re: Can this be done
Hans/Rudy - I can't figure out what I'm doing wrong when trying to use the ConcatenateIfs function. I can use the ConcatenateIf function with no problem, but I need to use the ConcatenateIfs in order to sort my results. Please see the attached images. Any of your help is greatly appreciated.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Can you tell us which version of ConcatenateIfs you are using? I posted multiple versions in this thread, with different syntax.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 29 Sep 2015, 22:03
Re: Can this be done
Sorry, that would've help huh? Please see below:
Code: Select all
Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
' Source: EileensLounge.com, August 2014
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim strResult As String
Dim col As Collection
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 3 Then
' Too few arguments
GoTo ErrHandler
End If
If n Mod 3 = 0 Then
' Separator specified explicitly
Separator = Criteria(n)
Else
' Use default separator
Separator = ","
End If
' Initialize collection of unique items
Set col = New Collection
' Loop through the cells of the concatenate range
For i = 1 To ConcatenateRange.Count
' Start by assuming that we have a match
f = True
' Loop through the conditions
For c = 0 To n - 1 Step 3
' Does cell in criteria range match the condition?
Select Case Criteria(c + 1)
Case "<="
If Criteria(c).Cells(i).Value > Criteria(c + 2) Then
f = False
Exit For
End If
Case "<"
If Criteria(c).Cells(i).Value >= Criteria(c + 2) Then
f = False
Exit For
End If
Case ">="
If Criteria(c).Cells(i).Value < Criteria(c + 2) Then
f = False
Exit For
End If
Case ">"
If Criteria(c).Cells(i).Value <= Criteria(c + 2) Then
f = False
Exit For
End If
Case "<>"
If Criteria(c).Cells(i).Value = Criteria(c + 2) Then
f = False
Exit For
End If
Case Else
If Criteria(c).Cells(i).Value <> Criteria(c + 2) Then
f = False
Exit For
End If
End Select
Next c
' Were all criteria satisfied?
If f Then
' If so, add value to collection, if it has not been added yet
On Error Resume Next
col.Add Item:=ConcatenateRange.Cells(i).Value, _
Key:=CStr(ConcatenateRange.Cells(i).Value)
On Error GoTo ErrHandler
End If
Next i
If col.Count > 0 Then
' Sort the results
SortCollection col
' Concatenate them
For i = 1 To col.Count
strResult = strResult & Separator & col(i)
Next i
' Remove first separator
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfs = strResult
Exit Function
ErrHandler:
ConcatenateIfs = CVErr(xlErrValue)
End Function
Sub SortCollection(col As Collection)
Dim i As Long
Dim j As Long
Dim tmp As Variant
For i = 1 To col.Count - 1
For j = i + 1 To col.Count
If col(j) < col(i) Then
tmp = col(j)
col.Remove Index:=j
col.Add Item:=tmp, Key:=CStr(tmp), Before:=i
End If
Next j
Next i
End Sub
Last edited by HansV on 21 Oct 2015, 14:55, edited 1 time in total.
Reason: to add [code] ... [/code] tags around code
Reason: to add [code] ... [/code] tags around code
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Thanks. The syntax for this version is as follows:
=ConcatenateIfs(RangeToConcatenate, CriteriaRange1, Operator1, Condition1 [, CriteriaRange2, Operator2, Condition2, ...] [, Separator])
Parts in square brackets [ ] are optional.
RangeToConcatenate is the range with text values to be concatenated.
CriteriaRange1 is a range containing cells to test for a condition; this range must be the same size as RangeToConcatenate.
Operator1 can be one of the following: "<=", "<", ">=", ">", "<>", or "=". If left empty, "=" is assumed.
Condition1 must evaluate to a single value; each cell in CriteriaRange1 will be compared to this value according to Operator1.
You can optionally specify similar triples CriteriaRange2, Operator2, Condition2 etc.
Values from RangeToConcatenate that meet the conditions will be concatenated using the Separator string; if Separator is omitted, ", " will be used.
Example: you want to concatenate values from A1:A20 if the corresponding value in B1:B20 is greater than or equal to the value in D1 and less than or equal to the value in D2, and use "/" as separator:
=ConcatenateIfs(A1:A20, B1:B20, ">=", D1, B1:B20, "<=", D2, "/")
Instead of A1:A20, B1:B20, D1 and D2, you can use named ranges.
=ConcatenateIfs(RangeToConcatenate, CriteriaRange1, Operator1, Condition1 [, CriteriaRange2, Operator2, Condition2, ...] [, Separator])
Parts in square brackets [ ] are optional.
RangeToConcatenate is the range with text values to be concatenated.
CriteriaRange1 is a range containing cells to test for a condition; this range must be the same size as RangeToConcatenate.
Operator1 can be one of the following: "<=", "<", ">=", ">", "<>", or "=". If left empty, "=" is assumed.
Condition1 must evaluate to a single value; each cell in CriteriaRange1 will be compared to this value according to Operator1.
You can optionally specify similar triples CriteriaRange2, Operator2, Condition2 etc.
Values from RangeToConcatenate that meet the conditions will be concatenated using the Separator string; if Separator is omitted, ", " will be used.
Example: you want to concatenate values from A1:A20 if the corresponding value in B1:B20 is greater than or equal to the value in D1 and less than or equal to the value in D2, and use "/" as separator:
=ConcatenateIfs(A1:A20, B1:B20, ">=", D1, B1:B20, "<=", D2, "/")
Instead of A1:A20, B1:B20, D1 and D2, you can use named ranges.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 5
- Joined: 29 Sep 2015, 22:03
Re: Can this be done
Hans - Thank you. I finally was able to get the ConcatenateIfs to work, but my results aren't sorting. Any suggestions?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
The screenshot shows that the values in the ConcatenateRange are text values (because they are enclosed in quotes), so they are sorted in alphanumeric order ("10" comes before "2" since the first character in "10" comes before "2").
If you convert the values in ConcatenateRange to numbers, they should be sorted in numeric order.
If you convert the values in ConcatenateRange to numbers, they should be sorted in numeric order.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 16 May 2016, 18:31
Re: Can this be done
Hello,
Relative to all on here, I am a novice in excel. I have attempted to use the concatentateifs function in excel, but when I do so, I get the #NAME error.
I have done the following:
1) gone into visual basic and inserted the following module:
2) Following this, I clicked "save" and exited visual basic
When I try to use the concatentateifs function, I get this error. Can someone please tell me what I am doing wrong?
FYI - I am using a work computer and there may be restrictions. I am able to record macro's without issues though.
Thanks for the help everyone!
Sincerely,
VBA novice
Relative to all on here, I am a novice in excel. I have attempted to use the concatentateifs function in excel, but when I do so, I get the #NAME error.
I have done the following:
1) gone into visual basic and inserted the following module:
Code: Select all
Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
' Source: EileensLounge.com, August 2014
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim strResult As String
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 2 Then
' Too few arguments
GoTo ErrHandler
End If
If n Mod 2 = 0 Then
' Separator specified explicitly
Separator = Criteria(n)
Else
' Use default separator
Separator = ","
End If
' Loop through the cells of the concatenate range
For i = 1 To ConcatenateRange.Count
' Start by assuming that we have a match
f = True
' Loop through the conditions
For c = 0 To n - 1 Step 2
' Does cell in criteria range match the condition?
If Criteria(c).Cells(i).Value <> Criteria(c + 1) Then
' If not, we don't have a match
f = False
Exit For
End If
Next c
' Were all criteria satisfied?
If f Then
' If so, add separator and value to result
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
' Remove first separator
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfs = strResult
Exit Function
ErrHandler:
ConcatenateIfs = CVErr(xlErrValue)
End Function
When I try to use the concatentateifs function, I get this error. Can someone please tell me what I am doing wrong?
FYI - I am using a work computer and there may be restrictions. I am able to record macro's without issues though.
Thanks for the help everyone!
Sincerely,
VBA novice
Last edited by Rudi on 16 May 2016, 19:10, edited 1 time in total.
Reason: Edited to add [code]...[/code] tags and indenting...
Reason: Edited to add [code]...[/code] tags and indenting...