Can this be done
-
- StarLounger
- Posts: 98
- Joined: 09 Sep 2010, 16:01
Can this be done
I have values in column A, cells A1, A3, A3 A4
E
D
C
R
I want Cell B2 to equal EDCR.
In my real world application, I want to look up a part number in column B and return the text value in colum M. I then want to append the text values where there is a match. Using the following array formula I can find the values.
=IFERROR(INDEX($B$2:$M$796,SMALL(IF($B$2:$B$796=A14,ROW($B$2:$B$796)-1),1),12) & ", ","")
But I need to append these like so, while changing the parameter for the "small" function.
=IFERROR(INDEX($B$2:$M$796,SMALL(IF($B$2:$B$796=A14,ROW($B$2:$B$796)-1),1),12) & ", ","") & =IFERROR(INDEX($B$2:$M$796,SMALL(IF($B$2:$B$796=A14,ROW($B$2:$B$796)-1),2),12) & ", ","")
The problem is, I may have 1, I may have 20, the number of matches is unknown. After I append 20 (a likely max) of these and enter them as an array formula, and populate them down 1000+ rows, my calculation slows to a crawl.
I am looking for a way to transpose values from a column into a single cell. If there is no way to do this with a formula, I can easily do it with a macro.
Thanks.
E
D
C
R
I want Cell B2 to equal EDCR.
In my real world application, I want to look up a part number in column B and return the text value in colum M. I then want to append the text values where there is a match. Using the following array formula I can find the values.
=IFERROR(INDEX($B$2:$M$796,SMALL(IF($B$2:$B$796=A14,ROW($B$2:$B$796)-1),1),12) & ", ","")
But I need to append these like so, while changing the parameter for the "small" function.
=IFERROR(INDEX($B$2:$M$796,SMALL(IF($B$2:$B$796=A14,ROW($B$2:$B$796)-1),1),12) & ", ","") & =IFERROR(INDEX($B$2:$M$796,SMALL(IF($B$2:$B$796=A14,ROW($B$2:$B$796)-1),2),12) & ", ","")
The problem is, I may have 1, I may have 20, the number of matches is unknown. After I append 20 (a likely max) of these and enter them as an array formula, and populate them down 1000+ rows, my calculation slows to a crawl.
I am looking for a way to transpose values from a column into a single cell. If there is no way to do this with a formula, I can easily do it with a macro.
Thanks.
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
You cannot do this with built-in functions, since the CONCATENATE worksheet function requires you to enumerate each argument individually.
Here is a user-defined function you can use:
You can use it like this:
=ConcatenateIf($B$2:$B$796,A14,$M$2:$M$796,",")
Here is a user-defined function you can use:
Code: Select all
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function
=ConcatenateIf($B$2:$B$796,A14,$M$2:$M$796,",")
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Hi Hans,
I can see that this is an old post... but is still relevant. I found your udf extremely helpful. I was wondering if you could modify it to accept multiple criteria?... I am a biologist and am finding it difficult to do it myself... in short, I was looking to use this ConcatenateIf() function as a ConcatenateIfs() function.... Could you help with this?
I can see that this is an old post... but is still relevant. I found your udf extremely helpful. I was wondering if you could modify it to accept multiple criteria?... I am a biologist and am finding it difficult to do it myself... in short, I was looking to use this ConcatenateIf() function as a ConcatenateIfs() function.... Could you help with this?
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Welcome to Eileen's Lounge!
Yes, that should be doable, but I'll have to work on it. I'll get back to you.
Yes, that should be doable, but I'll have to work on it. I'll get back to you.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Thanks Hans... will await your reply
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Here is the code for a ConcatenateIfs function. Make sure that you read the instructions below the code carefully.
Just as with SUMIFS vs SUMIF, the arguments have a different order than in ConcatenateIf. The syntax is:
=ConcatenateIfs(ConcatenateRange, CriteriaRange1, Condition1, CriteriaRange2, Condition2, ..., Separator)
ConcatenateRange: required; it is the range containing the values you want to concatenate.
CriteriaRange1: required; the first range to check
Condition1: required, the value to match with the first criteria range
CriteriaRange2: optional; the second range to check
Condition2: optional, but required if CriteriaRange2 has been specified; the value to match with the second criteria range
...
Separator: optional; the string that separates the values in the result. If you omit it, "," is used
Example of usage:
=ConcatenateIfs(A1:A6, B1:B6, 2, C1:C6, "b")
This formula will concatenate those cells in A1:A6 for which the corresponding cell in B1:B6 equals 1 and the corresponding cell in C1:C6 equals b. The default separator "," will be used. Result:
Elina,Jim
If we use
=ConcatenateIfs(A1:A6,B1:B6,2,C1:C6,"b"," - ")
the result is
Elina - Jim
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
=ConcatenateIfs(ConcatenateRange, CriteriaRange1, Condition1, CriteriaRange2, Condition2, ..., Separator)
ConcatenateRange: required; it is the range containing the values you want to concatenate.
CriteriaRange1: required; the first range to check
Condition1: required, the value to match with the first criteria range
CriteriaRange2: optional; the second range to check
Condition2: optional, but required if CriteriaRange2 has been specified; the value to match with the second criteria range
...
Separator: optional; the string that separates the values in the result. If you omit it, "," is used
Example of usage:
=ConcatenateIfs(A1:A6, B1:B6, 2, C1:C6, "b")
This formula will concatenate those cells in A1:A6 for which the corresponding cell in B1:B6 equals 1 and the corresponding cell in C1:C6 equals b. The default separator "," will be used. Result:
Elina,Jim
If we use
=ConcatenateIfs(A1:A6,B1:B6,2,C1:C6,"b"," - ")
the result is
Elina - Jim
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Hi Hans,
Thanks a lot for this....
I tried out the query.... it returns a blank cell.... I have attached a file which has two sheets... the query sheet and the data sheet.
I have applied the concatenateifs formula in the query sheet in columnD.... Just for cross checking, I applied the countifs and sumifs formula in columns E and F, which seem to work.....
Could you see what I am doing wrong and how this can be resolved?...
Thanks for all the help...
Thanks a lot for this....
I tried out the query.... it returns a blank cell.... I have attached a file which has two sheets... the query sheet and the data sheet.
I have applied the concatenateifs formula in the query sheet in columnD.... Just for cross checking, I applied the countifs and sumifs formula in columns E and F, which seem to work.....
Could you see what I am doing wrong and how this can be resolved?...
Thanks for all the help...
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Both the ConcatenateIf and ConcatenateIfs functions can only handle simple values as Condition1, Condition2 etc., not expressions with >, >=, <.<= or <>. Trying to handle expressions would be very complicated and extremely slow. I performed a small test and it bogged down Excel completely.
I cannot help but wonder whether you really need so many very long concatenated strings...
I cannot help but wonder whether you really need so many very long concatenated strings...
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Well I definitely need the query to satisfy all these criteria.... whether it be in the form of simple queries or in the form of concatenated strings...
When I run the query... excel does not hang (i assume that is what you meant by "bogged down").... it just returns an empty cell....
I tried using just one criteria with a concatenated string, smaller dataset etc.... but it still returns an empty cell. I guess, thats a limitation with the concatenate function????
Anyway.... do you see a workaround this problem?
When I run the query... excel does not hang (i assume that is what you meant by "bogged down").... it just returns an empty cell....
I tried using just one criteria with a concatenated string, smaller dataset etc.... but it still returns an empty cell. I guess, thats a limitation with the concatenate function????
Anyway.... do you see a workaround this problem?
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
As it is now, the function cannot handle an expression such as "<="&'QUERY SHEET'!$B2, so none of the rows meet the criteria.
I'll see if I can come up with something, but as I wrote, it will be complicated.
I'll see if I can come up with something, but as I wrote, it will be complicated.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Here is a new version, with a new syntax.
Syntax:
=ConcatenateIfs(ConcatenateRange, CriteriaRange1, Operator1, Condition1, CriteriaRange2, Operator2, Condition2, ..., Separator)
ConcatenateRange: required; it is the range containing the values you want to concatenate.
CriteriaRange1: required; the first range to check
Operator1: required, the comparison operator "=", "<>", "<", "<=", ">" or ">="
Condition1: required, the value to match with the first criteria range
CriteriaRange2: optional; the second range to check
Operator2: optional, the second comparison operator
Condition2: optional, but required if CriteriaRange2 has been specified; the value to match with the second criteria range
...
Separator: optional; the string that separates the values in the result. If you omit it, "," is used
Example:
=ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b")
or
=ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b", " - ")
In your workbook:
=CONCATENATEIFS('Data Sheet'!$A$2:$A$1181,'Data Sheet'!$C$2:$C$1181,"=",'QUERY SHEET'!$A2,'Data Sheet'!$D$2:$D$1181,"<=",'QUERY SHEET'!$B2,'Data Sheet'!$E$2:$E$1181,">=",'QUERY SHEET'!$C2,'Data Sheet'!$B$2:$B$1181,">",0)
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
=ConcatenateIfs(ConcatenateRange, CriteriaRange1, Operator1, Condition1, CriteriaRange2, Operator2, Condition2, ..., Separator)
ConcatenateRange: required; it is the range containing the values you want to concatenate.
CriteriaRange1: required; the first range to check
Operator1: required, the comparison operator "=", "<>", "<", "<=", ">" or ">="
Condition1: required, the value to match with the first criteria range
CriteriaRange2: optional; the second range to check
Operator2: optional, the second comparison operator
Condition2: optional, but required if CriteriaRange2 has been specified; the value to match with the second criteria range
...
Separator: optional; the string that separates the values in the result. If you omit it, "," is used
Example:
=ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b")
or
=ConcatenateIfs(A1:A6, B1:B6, "<", 2, C1:C6, "=", "b", " - ")
In your workbook:
=CONCATENATEIFS('Data Sheet'!$A$2:$A$1181,'Data Sheet'!$C$2:$C$1181,"=",'QUERY SHEET'!$A2,'Data Sheet'!$D$2:$D$1181,"<=",'QUERY SHEET'!$B2,'Data Sheet'!$E$2:$E$1181,">=",'QUERY SHEET'!$C2,'Data Sheet'!$B$2:$B$1181,">",0)
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
That's fantastic!!!
works like a charm!!....
Do you think it would be possible to eliminate the duplicates from the output string?.....
Thanks a lot for this...
works like a charm!!....
Do you think it would be possible to eliminate the duplicates from the output string?.....
Thanks a lot for this...
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Do you want the remaining values to be sorted in ascending order too?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Well.... It doesn't have to be sorted!!...but if you do that... i'll be on the next flight to give you a big sloppy kiss!!!
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Can this be done
Oops!
This needs an extra piece of code to sort the results:
See the attached version.
This needs an extra piece of code to sort the results:
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
That's marvellous... and an elegant piece of work!!!... i was pulling my hair out over this for a long time!!!
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Can this be done
Hans,
So when will you be publishing the help files for this elegant query?
Please just use short sentences so John and I can benefit from it.
So when will you be publishing the help files for this elegant query?
Please just use short sentences so John and I can benefit from it.
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.
-
- NewLounger
- Posts: 11
- Joined: 15 Aug 2014, 10:51
Re: Can this be done
Is there a way I can use the individual values from the concatenated output as a criteria in a sumifs query, without using the text-to-columns option?
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands