Excel tutorials

Concatenate Multiple Cells Into One Cell If Match Condition

 

Merge Multiple Cells To One If Matched Criteria

In the work with Excel formulas we sometimes have the situation that it is necessary to merge texts or data from multiple cells for a repeating condition. If you have a similar problem try using the formulas in this Excel tutorial. The first step is to show the Unique Data only (condition) that will be the criterion for returning other data from adjacent columns.

In this Excel tutorial I will show how we can for a particular condition to merge text from multiple cells into one cell. In the picture below, notice the situation with the original data and results.

 Merge texts from multiple cells into one under a certain condition

 

In columns 'A', 'B' and 'C' you can see the source data. Based on these data, we want to show in the columns E, F and G for a specific condition all model and quantity for a certain repeating Date. So we need a specific date to display all models and add value to quantity.

In cell "E2" there is a formula that returns unique data from column "A".

In "F2", there is an ARRAY or CSE formula that returns as a condition all models in column "B" and displays them in one cell

=CHOOSE(SUMPRODUCT(--(E2=$A$2:$A$7));VLOOKUP(E2;$A$2:$B$7;2;0);VLOOKUP(E2;$A$2:$B$7;2;0) & ", " & INDEX($B$2:$B$7;MATCH(E2;$A$2:$A$7;0)+1);VLOOKUP(E2;$A$2:$B$7;2;0) & ", " & INDEX($B$2:$B$7;MATCH(E2;$A$2:$A$7;0)+1) & ", " & INDEX($B$2:$B$7;MATCH(E2;$A$2:$A$7;0)+2))

If you want the models to appear one below the other then activate "Wrap Text" on the "F" column

In cell "G2" there is a formula that returns the sum of all quantities for the date condition.

=SUMIF($A$2:$C$7;E2;$C$2:$C$7)

Concatenate All Data for Matched Criteria to the One Cell by VBA-UDF

If you want, you can also cocatenate multiple cells with VBA-UDF (User Definition Function). Open the VBE in Excel and copy this VBA code below to the new Standard Module.

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
If r = lookupval Then
result = result & " " & r.Offset(0, indexcol - 1)
End If
Next r
MYVLOOKUP = result
End Function

In the "J2" cell, place the formula: =MYVLOOKUP(E2;A2:A7;2) and copy it down.

If you are using Excel 2016 then try using this formula below

=TEXTJOIN(", ";1;IF($A$2:$A$7=E2;$B$2:$B$7;""))

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.