Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Concatenate multiple cells to one for matched criteria

Concatenate 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 tutorial. The first step is to show only the unique data (conditions) that will be the criterion for returning other data from adjacent columns.

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


Concatenate all data for matched criteria to one cell by VBA-UDF

If you want, you can also concatenate 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;""))

Attention! In the formulas as a separator I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,), you may need to use point (.) instead of.