How To Concatenate Conditionally?

[xlmaniac]

Dear All,
I do have the sample data set across A1:C6.
I would like to concatenate as follows:-
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Store[/TD]
[TD="width: 64"]Result[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]c[/TD]
[TD]a-b-c[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]e[/TD]
[TD]d-e[/TD]
[/TR]
</tbody>[/TABLE]

Could someone help me with the formula which can populate the desired result across C Columns?
Regards

 

[Aryatect]

Hi, Please try below:

Book1
	A	B	C
1	Region	Store	Result
2	East	a
3	East	b
4	East	c	a-b-c
5	West	d
6	West	e	d-e
Sheet2

Cell Formulas
Range	Formula
C2	{=IF(COUNTIF($A$2:$A$6,A2)=LARGE(COUNTIF(A$2:A2,A2),1),TEXTJOIN("-",TRUE,IF($A$2:$A$6=A2,$B$2:$B$6,"")),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in C2 to C3 thru C6

 

[xlmaniac]

Dear Aryatect,
Thanks for the solution.
But since I am using 2010 version of excel & hence unable to use the formula that you have suggested.
Could you pls suggest some other alternative?
Regards

 

[Aryatect]

Hi, Let me try and find something else beside TEXTJOIN.

I think you might have to go for UDF (user defined function) for this, unfortunately that is not my strong area

 

[trunten]

If you do decide to go with a UDF you could this a go:

Public Function CONCATIF(ByRef CONCATRANGE As Range, ByRef COMPARERANGE, ByRef CONDITION As Variant, Optional ByVal DELIMITER As String = "", Optional ByVal IGNORECASE = False) As String
    Dim s As String
    Dim c As Range
    If TypeName(CONDITION) = "Range" Then CONDITION = CONDITION(1, 1).Value
    If TypeName(CONCATRANGE) = "Range" And TypeName(COMPARERANGE) = "Range" _
            And COMPARERANGE.Rows.Count = CONCATRANGE.Rows.Count _
            And COMPARERANGE.Columns.Count = CONCATRANGE.Columns.Count Then
        For Each c In COMPARERANGE
            If StrComp(c.Value, CONDITION, IIf(IGNORECASE, vbTextCompare, vbBinaryCompare)) = 0 Then
                s = s & IIf(s = "", "", DELIMITER) & CONCATRANGE(c.Row - COMPARERANGE(1, 1).Row + 1, c.Column - COMPARERANGE(1, 1).Column + 1).Value
            End If
        Next c
        CONCATIF = s
    Else
        CONCATIF = ""
    End If
End Function

the formula has the basic signature of:
=CONCATIF(CONCATRANGE, COMPARERANGE, CONDITION, DELIMITER, IGNORECASE)
wherein CONCATRANGE is the range containing the values that you are going to concatenate, COMPARERANGE is the range containing the values that you are going to apply the condition to (could be the same as CONCATRANGE), CONDITION is either a cell reference containing the condition or a hard coded string/number, DELIMITER is optional as must be a hard coded string which will separate the concatenated values and IGNORECASE defaults to FALSE but can be set to TRUE so that the comparison will compare values ignoring their case.

which you could use in your sheet like so:
=concatif($B$2:$B$6,$A$2:$A$6,A4,"-",TRUE) <-- Ignores case, condition is a cell reference and delimiter is a "-"
=concatif($B$2:$B$6,$A$2:$A$6,"EaSt",",",TRUE) <-- Ignores case, condition is a string and delimiter is a ","
=concatif($B$2:$B$6,$A$2:$A$6,"West","-") <-- Respects case, condition is a string and delimiter is a "-"
=concatif($B$2:$B$6,$A$2:$A$6,A2) <-- Respects case, condition is a cell reference and no delimiter

hth

 

[xlmaniac]

Dear Trunten,
Thanks a lot for your solution and your effort & time towards my problem.
I have tried the UDF but it is not dynamically populating the results that is desired.(1st 3 solutions).
4th one is not yielding anything.
Could you pls check and make it dynamic so that it populates result automatically without any manual intervention? My data ranges goes into thousands of rows.
Regards

 

[trunten]

hmm. its working that way for me.

perhaps try:

Public Function CONCATIF(ByRef CONCATRANGE As Range, ByRef COMPARERANGE, ByRef CONDITION As Variant, Optional ByVal DELIMITER As String = "", Optional ByVal IGNORECASE = False) As String
    Dim s As String
    Dim c As Range

    Application.Volatile
    If TypeName(CONDITION) = "Range" Then CONDITION = CONDITION(1, 1).Value
    If TypeName(CONCATRANGE) = "Range" And TypeName(COMPARERANGE) = "Range" _
            And COMPARERANGE.Rows.Count = CONCATRANGE.Rows.Count _
            And COMPARERANGE.Columns.Count = CONCATRANGE.Columns.Count Then
        For Each c In COMPARERANGE
            If StrComp(c.Value, CONDITION, IIf(IGNORECASE, vbTextCompare, vbBinaryCompare)) = 0 Then
                s = s & IIf(s = "", "", DELIMITER) & CONCATRANGE(c.Row - COMPARERANGE(1, 1).Row + 1, c.Column - COMPARERANGE(1, 1).Column + 1).Value
            End If
        Next c
        CONCATIF = s
    Else
        CONCATIF = ""
    End If
End Function

Just checking, the example formulas were just that, examples. If you have a larger data range then you can adjust the ranges as you see fit eg:

=concatif($B$2:$B$10000,$A$2:$A$10000,A2,"-",TRUE)
=concatif($B$2:$B$10000,$A$2:$A$10000,"East","-",TRUE)
etc.

 

[Eric W]

You can do this with a simple formula and Conditional Formatting. In C2 put this formula and drag down:

=IF(A2="","",IF(A1<>A2,B2,C1&"-"&B2))

Now select column C, click Conditional Formatting > New Rule > Use a formula > and enter:

=OR(A1="",A1=A2)

Then click Format... > Number > Custom > and put ;;; in the Type: box.


If you use C as a helper column, you can put this formula in D2

=IF(OR(A2<>A3,A3=""),C2,"")

and drag down, and you can do without the Conditional Formatting.

 

[xlmaniac]

hmm. its working that way for me.

perhaps try:

Public Function CONCATIF(ByRef CONCATRANGE As Range, ByRef COMPARERANGE, ByRef CONDITION As Variant, Optional ByVal DELIMITER As String = "", Optional ByVal IGNORECASE = False) As String
    Dim s As String
    Dim c As Range

    Application.Volatile
    If TypeName(CONDITION) = "Range" Then CONDITION = CONDITION(1, 1).Value
    If TypeName(CONCATRANGE) = "Range" And TypeName(COMPARERANGE) = "Range" _
            And COMPARERANGE.Rows.Count = CONCATRANGE.Rows.Count _
            And COMPARERANGE.Columns.Count = CONCATRANGE.Columns.Count Then
        For Each c In COMPARERANGE
            If StrComp(c.Value, CONDITION, IIf(IGNORECASE, vbTextCompare, vbBinaryCompare)) = 0 Then
                s = s & IIf(s = "", "", DELIMITER) & CONCATRANGE(c.Row - COMPARERANGE(1, 1).Row + 1, c.Column - COMPARERANGE(1, 1).Column + 1).Value
            End If
        Next c
        CONCATIF = s
    Else
        CONCATIF = ""
    End If
End Function

Just checking, the example formulas were just that, examples. If you have a larger data range then you can adjust the ranges as you see fit eg:

=concatif($B$2:$B$10000,$A$2:$A$10000,A2,"-",TRUE)
=concatif($B$2:$B$10000,$A$2:$A$10000,"East","-",TRUE)
etc.

Dear Trunten,
Sorry for the belated reply.
Now the UDF is populating desired result.
Thank you so much for such amazing solution.
Appreciate your support & time in resolving my issue.
Pls accept my sincere gratitude.

 

[xlmaniac]

You can do this with a simple formula and Conditional Formatting. In C2 put this formula and drag down:

=IF(A2="","",IF(A1<>A2,B2,C1&"-"&B2))

Now select column C, click Conditional Formatting > New Rule > Use a formula > and enter:

=OR(A1="",A1=A2)

Then click Format... > Number > Custom > and put ;;; in the Type: box.


If you use C as a helper column, you can put this formula in D2

=IF(OR(A2<>A3,A3=""),C2,"")

and drag down, and you can do without the Conditional Formatting.

Dear Eric,
Thank you for your outstanding solution.
Really appreciate your effort & time.
Sorry for the belated reply.
Thanks again.