CODEWITHSUNDEEP
excelexcel-formulaconcatenation
Ben Aspill
Ben Aspill

Reputation: 35

How do I concatenate multiple cells which fulfil a criteria in Sheets/Excel?

I wish to Concatenate multiple cells together which fulfil a certain criteria of my choosing.

For example, if I want all of the cats or all of the dogs in one cell, even if they duplicate: 

A1 Cat B2 Leopard 
A2 Dog B3 Hyeenah
A3 Cat C3 Tortoise Shell
A4 Cat C4 Tortoise Shell

So I want to Concatenate all of the cats and return in one cell so that the output is: Leopard, Tortoise Shell, Tortoise Shell

or for Dogs:

Hyeenah

I have tried various combinations using Index, Match, Ifs and Concatenate but do not feel like I am getting any closer.

I hope to return the output I specify in a reference cell.

Upvotes: 2

Views: 130

Answers (2)

JvdV
JvdV

Reputation: 75840

Assuming you actually meant B1:B4 in the return values, you could use the TEXTJOIN function depending on your Excel license. Follow the link to see if it would be available to you.

enter image description here

Unfortunately there is no such thing as a TEXTJOINIF function, so we have to create our own.

Formula in E1:

=TEXTJOIN(", ",TRUE,IF($A$1:$A$4=D1,$B$1:$B$4,""))

Note: These are array formulas entered as CSE through Ctrl+Shift+Enter

Might you not have TEXTJOIN your best bet is to go with a UDF, and we can create our own TEXTJOINIF function:

Function TEXTJOINIF(Delimiter As String, CriteriaRange1 As Range, Criteria As String, ConcatRange As Range) As String

Dim CL As Range
For Each CL In CriteriaRange1
    If CL.Value = Criteria Then
        If TEXTJOINIF = "" Then
            TEXTJOINIF = ConcatRange.Cells(CL.Row, 1).Value
        Else
            TEXTJOINIF = TEXTJOINIF & Delimiter & ConcatRange.Cells(CL.Row, 1).Value
        End If
    End If
Next CL

End Function

You can call it in E1:

=TEXTJOINIF(", ",$A$1:$A$4,D1;$B$1:$B$4)

And drag it down.

Upvotes: 1

Matthew E. Miller
Matthew E. Miller

Reputation: 557

I'm not an Excel Wizard, just a programmer, but I believe you need some sort of automation/repetition loop. I found this can be done with VBA in Excel.

Link: Excel VBA Loop

Here is the pseudo-code:

// SUMMATION CELL CATS: D1
// SUMMATION CELL DOGS: D2

   Dim i As Integer

   For i = 1 To 6
     IF(Cells(i, 1).Value == "Cat"){CONCATENATE(D1, Cells(i,1).Value, ',')}
     ELSE(Cells(i, 1).Value == "Dog"){CONCATENATE(D2, Cells(i,1).Value, ',')}
   Next i

I hope that helps you get towards the solution.

Upvotes: 0

Related Questions