Can this be done
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
Please note that the function is named ConcatenateIfs, not ConcatentateIfs.
Also: make sure that you haven't named the module that contains the function ConcatenateIfs - having a function and a module with the same name confuses Excel.
Please note that the function is named ConcatenateIfs, not ConcatentateIfs.
Also: make sure that you haven't named the module that contains the function ConcatenateIfs - having a function and a module with the same name confuses Excel.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 16 May 2016, 18:31
Re: Can this be done
Hi Hans,
Thanks for the quick response. I have gone in and renamed the module to "Concatenate_ifs" and I am using ConcatenateIfs. In doing so I am still getting the "#NAME" error. I have included a screen shot below of the my workbook and VBA code. Can you have a look at let me know if anything stands out to you?
Again, your help is greatly appreciated.
Thanks for the quick response. I have gone in and renamed the module to "Concatenate_ifs" and I am using ConcatenateIfs. In doing so I am still getting the "#NAME" error. I have included a screen shot below of the my workbook and VBA code. Can you have a look at let me know if anything stands out to you?
Again, your help is greatly appreciated.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Thanks - that helps!
You have stored the ConcatenateIfs function in a module in your personal macro workbook Personal.xlsb. That is great - it means that the function will be available in all your workbooks. BUT... since the function is not stored in the workbook in which you use it, you must use it like this:
=PERSONAL.XLSB!ConcatenateIFs(A1:A6,B1:B6,2,C1:C6,"b")
You have stored the ConcatenateIfs function in a module in your personal macro workbook Personal.xlsb. That is great - it means that the function will be available in all your workbooks. BUT... since the function is not stored in the workbook in which you use it, you must use it like this:
=PERSONAL.XLSB!ConcatenateIFs(A1:A6,B1:B6,2,C1:C6,"b")
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 16 May 2016, 18:31
Re: Can this be done
Amazing!
Thanks for the help!
Thanks for the help!
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- NewLounger
- Posts: 20
- Joined: 16 May 2016, 18:31
Re: Can this be done
Hi Hans,
Thanks again for all your help on this function. One last question for you (hopefully!): I would like my final separator in the concatenateifs function to be "and". Right now the separator is set as "," and I was hoping I could retain this but have the separator between the second last and last items separated by "and". For example: with the current function, if A, B and C would have a result of "A, B, C". I would like it to be "A, B and C". Is this possible?
Thanks!
Thanks again for all your help on this function. One last question for you (hopefully!): I would like my final separator in the concatenateifs function to be "and". Right now the separator is set as "," and I was hoping I could retain this but have the separator between the second last and last items separated by "and". For example: with the current function, if A, B and C would have a result of "A, B, C". I would like it to be "A, B and C". Is this possible?
Thanks!
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
That requires the function to be modified substantially. Here is a new version:
The syntax is now
=ConcatenateIfs(RangeToConcatenate, CriteriaRange1, Operator1, Condition1 [, CriteriaRange2, Operator2, Condition2, ...] [, Separator] [, FinalSeparator])
In addition to the string to be used as separator, you can specify a string to be used as final separator. For example:
=ConcatenateIfs(A1:A6, B1:B6, "=", 2, C1:C6, "=", "b", ", " , " and ")
or if the function is in your Personal Macro Workbook:
=PERSONAL.XLSB!ConcatenateIfs(A1:A6, B1:B6, "=", 2, C1:C6, "=", "b", ", " , " and ")
This concatenates the cells of A1:A6 where the corresponding cell in B1:B6 equals 2 and the corresponding cell in C1:C6 equals b. The separator is ", ", i.e. a comma followed by a space. The final separator is " and ".
Code: Select all
Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
' Source: EileensLounge.com, August 2016
Dim i As Long
Dim c As Long
Dim n As Long
Dim f As Boolean
Dim Separator As String
Dim FinalSep As String
Dim TempSep As String
Dim strResult As String
Dim lngCount As Long
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 2 Then
' Too few arguments
GoTo ErrHandler
End If
Select Case n Mod 3
Case 0
' Separator specified
Separator = Criteria(n)
FinalSep = Separator
Case 1
' Two separators specified
Separator = Criteria(n - 1)
FinalSep = Criteria(n)
Case 2
' Default separators
Separator = ","
FinalSep = ","
End Select
TempSep = Chr(182)
' 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 - 2 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 Like Criteria(c + 2) Then
f = False
Exit For
End If
Case Else
If Not Criteria(c).Cells(i).Value Like Criteria(c + 2) Then
f = False
Exit For
End If
End Select
Next c
' Were all criteria satisfied?
If f Then
' If so, add separator and value to result
strResult = strResult & TempSep & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
' Remove first separator
strResult = Mid(strResult, Len(TempSep) + 1)
lngCount = (Len(strResult) - Len(Replace(strResult, TempSep, ""))) / Len(TempSep)
' Replace TempSep with Separator
strResult = Replace(strResult, TempSep, Separator, 1, lngCount - 1)
' Replace last separator with FinalSep
strResult = Replace(strResult, TempSep, FinalSep)
End If
ConcatenateIfs = strResult
Exit Function
ErrHandler:
ConcatenateIfs = CVErr(xlErrValue)
End Function
=ConcatenateIfs(RangeToConcatenate, CriteriaRange1, Operator1, Condition1 [, CriteriaRange2, Operator2, Condition2, ...] [, Separator] [, FinalSeparator])
In addition to the string to be used as separator, you can specify a string to be used as final separator. For example:
=ConcatenateIfs(A1:A6, B1:B6, "=", 2, C1:C6, "=", "b", ", " , " and ")
or if the function is in your Personal Macro Workbook:
=PERSONAL.XLSB!ConcatenateIfs(A1:A6, B1:B6, "=", 2, C1:C6, "=", "b", ", " , " and ")
This concatenates the cells of A1:A6 where the corresponding cell in B1:B6 equals 2 and the corresponding cell in C1:C6 equals b. The separator is ", ", i.e. a comma followed by a space. The final separator is " and ".
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 20
- Joined: 16 May 2016, 18:31
Re: Can this be done
Works great,
Thanks again VBA master!
Thanks again VBA master!
-
- NewLounger
- Posts: 3
- Joined: 08 Sep 2016, 08:49
Re: Can this be done
This very useful for my needs too - thanks.
One more thing, can it be edited further to ignore blank cells as the latest version appears to treat all empty cells as a criteria and therefore concatenated all the values together.
Many thanks.
Edit: In the cells I already have some text i.e. 'Mary, Lamb, Sheep' and this VB will see this as a string rather than the words invdividually and would do the following:
'Mary, Lamb, Sheep'
'Lamb, Mary, Sheep'
would result in 'Lamb, Mary, Sheep, Mary, Lamb, Sheep'
Is it possible for the VB to treat all words in a string as seperate words?
One more thing, can it be edited further to ignore blank cells as the latest version appears to treat all empty cells as a criteria and therefore concatenated all the values together.
Many thanks.
Edit: In the cells I already have some text i.e. 'Mary, Lamb, Sheep' and this VB will see this as a string rather than the words invdividually and would do the following:
'Mary, Lamb, Sheep'
'Lamb, Mary, Sheep'
would result in 'Lamb, Mary, Sheep, Mary, Lamb, Sheep'
Is it possible for the VB to treat all words in a string as seperate words?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
Here is a new version that ignores empty values and processes individual words. It assumes that the words in the cells are delimited by the same separator as the one used to concatenate the values.
Example of usage:
=ConcatenateIfsList(B1:B6,A1:A6,"=","London",", ")
This will concatenate the words found in B1:B6 with ", " if the corresponding cell in A1:A6 equals London:
Here is a new version that ignores empty values and processes individual words. It assumes that the words in the cells are delimited by the same separator as the one used to concatenate the values.
Code: Select all
Function ConcatenateIfsList(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 arrWords() As String
Dim j As Long
Dim col As Collection
On Error GoTo ErrHandler
n = UBound(Criteria)
If n < 2 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
' Split the cell value
arrWords = Split(ConcatenateRange.Cells(i).Value, Separator)
For j = 0 To UBound(arrWords)
' Add word to collection, if it has not been added yet
On Error Resume Next
col.Add Item:=arrWords(j), _
Key:=CStr(arrWords(j))
On Error GoTo ErrHandler
Next j
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
ConcatenateIfsList = strResult
Exit Function
ErrHandler:
ConcatenateIfsList = 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
=ConcatenateIfsList(B1:B6,A1:A6,"=","London",", ")
This will concatenate the words found in B1:B6 with ", " if the corresponding cell in A1:A6 equals London:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 08 Sep 2016, 08:49
Re: Can this be done
Excellent! Thanks :)
Can it have multiple separators? (i.e. '/', '-' etc)
I note it will not process 'mary,lamb,sheep' as it do for 'mary, lamb, sheep'
Can it have multiple separators? (i.e. '/', '-' etc)
I note it will not process 'mary,lamb,sheep' as it do for 'mary, lamb, sheep'
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Multiple separators? What would you want to do with them?
And if you have "mary,lamb,sheep", specify "," (without a space after the comma) instead of ", " as separator.
And if you have "mary,lamb,sheep", specify "," (without a space after the comma) instead of ", " as separator.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 08 Sep 2016, 08:49
Re: Can this be done
Just to make it future proof - I may come across with Mary/Lamb/Sheep etc
It will not process 'mary,lamb,sheep' as it do for 'mary, lamb, sheep' as it will process:
mary,lamb,sheep
mary, lamb, sheep
as
lamb, mary, mary,lamb,sheep, sheep
It will not process 'mary,lamb,sheep' as it do for 'mary, lamb, sheep' as it will process:
mary,lamb,sheep
mary, lamb, sheep
as
lamb, mary, mary,lamb,sheep, sheep
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
If you change the lines
to
You can then specify "," as separator. The code will handle both "mary, lamb" and "mary,lamb" correctly.
To handle several separators, it's probably easier to replace "/", "-" etc. with "," in the concatenate range itself.
Code: Select all
col.Add Item:=arrWords(j), _
Key:=CStr(arrWords(j))
Code: Select all
col.Add Item:=Trim(arrWords(j)), _
Key:=Trim(arrWords(j))
To handle several separators, it's probably easier to replace "/", "-" etc. with "," in the concatenate range itself.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 23 Jan 2017, 03:53
Re: Can this be done
Hey HansV - looks like you're a bit of a guru, and are incredibly generous with your time!
My company insists on using Google Sheets...not excel. Do you know how to transcribe your code (the one I've pasted below, which is amazing) into Google Sheets speak ("Google Apps Script", I think). Or do you know where I can find someone that can do this? Thank you so much mate, you're amazing.
My company insists on using Google Sheets...not excel. Do you know how to transcribe your code (the one I've pasted below, which is amazing) into Google Sheets speak ("Google Apps Script", I think). Or do you know where I can find someone that can do this? Thank you so much mate, you're amazing.
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 < 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
' 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 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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
I have no experience with Google Apps Script. You might try the Google Apps Script Communities.
I have no experience with Google Apps Script. You might try the Google Apps Script Communities.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 23 Jan 2017, 03:53
Re: Can this be done
Thanks Hans, really appreciate the speedy reply. All the best!
-
- NewLounger
- Posts: 2
- Joined: 28 Jan 2017, 05:56
Re: Can this be done
Hi Hans,
I envy your excel skills!
I was using the version of this function posted below and it was working fine until I ran another macro. Now I get a #Value error every time i try to use the concatenateifs function (in new cells and in existing cells with the function already applied). Any suggestions on what I could be doing wrong?
Thanks so much for your time!
I envy your excel skills!
I was using the version of this function posted below and it was working fine until I ran another macro. Now I get a #Value error every time i try to use the concatenateifs function (in new cells and in existing cells with the function already applied). Any suggestions on what I could be doing wrong?
Thanks so much for your time!
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 < 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
' 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 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
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Can this be done
Hi blindzero678,
Welcome to Eileen's Lounge.
Hans will need to verify my suggestion, but it seems the origninal function had the condition:
n = UBound(Criteria)
If n < 2 Then...
...yet in your posted syntax you have:
If n < 3 Then...
Changing it back to n < 2 makes the function work as expected (at least in my tests).
Welcome to Eileen's Lounge.
Hans will need to verify my suggestion, but it seems the origninal function had the condition:
n = UBound(Criteria)
If n < 2 Then...
...yet in your posted syntax you have:
If n < 3 Then...
Changing it back to n < 2 makes the function work as expected (at least in my tests).
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: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Rudi is correct that it should be
If n < 2 Then
(I think I made this mistake myself in one of the versions). But that shouldn't cause formulas that worked before to suddenly return #Value. The problem would only occur if you specify only one condition and omit the separator, otherwise it shouldn't make a difference.
If you could attach a sample workbook that demonstrates the problem, without sensitive information, we could investigate the problem directly.
If n < 2 Then
(I think I made this mistake myself in one of the versions). But that shouldn't cause formulas that worked before to suddenly return #Value. The problem would only occur if you specify only one condition and omit the separator, otherwise it shouldn't make a difference.
If you could attach a sample workbook that demonstrates the problem, without sensitive information, we could investigate the problem directly.
Best wishes,
Hans
Hans