Can this be done

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Can this be done

Post by cecil »

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.

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

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:

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
You can use it like this:

=ConcatenateIf($B$2:$B$796,A14,$M$2:$M$796,",")
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

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?

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Welcome to Eileen's Lounge!

Yes, that should be doable, but I'll have to work on it. I'll get back to you.
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

Thanks Hans... will await your reply

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Here is the code for a ConcatenateIfs function. Make sure that you read the instructions below the code carefully.

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
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:
S0017.png
=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.
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

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...
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

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...
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

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?

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

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.
Regards,
Hans

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Here is a new version, with a new syntax.

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
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)
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

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...

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Do you want the remaining values to be sorted in ascending order too? :grin:
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

:grin: :laugh:
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!!! :rofl:
:thankyou:

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Oops! :blush:

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
See the attached version.
concatenateifsquery for lounge.xlsm
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

:thankyou: That's marvellous... and an elegant piece of work!!!... i was pulling my hair out over this for a long time!!! :hairout:

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

I hope you have some hair left!
Regards,
Hans

User avatar
Rudi
gamma jay
Posts: 25409
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Can this be done

Post by Rudi »

Hans,
So when will you be publishing the help files for this elegant query?

:whisper: 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.

drknow1982
NewLounger
Posts: 11
Joined: 15 Aug 2014, 10:51

Re: Can this be done

Post by drknow1982 »

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?

User avatar
HansV
Administrator
Posts: 74792
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can this be done

Post by HansV »

Can you explain what you want to accomplish?
Regards,
Hans