Concatenate cell values based on a condition
This article is for Excel users that don't have the latest Excel version or can't or don't want to use VBA code.
The image above shows you data in column B and C. I want to concatenate values on the same row as the condition specified in cell F2 which is in this case "M".
The corresponding values are "CD", "IJ" and "OP", in the picture above.
Table of Contents
I highly recommend using the TEXTJOIN function if you use at least Excel 2019 or a later version.
For earlier versions than Excel 2019 use the user-defined function demonstrated here: Lookup and return multiple values concatenated into one cell
It is what the CONCATENATE function should have been from the beginning.
1. Create an array of values manually
- Copy (CTRL + c) this formula:
=TRANSPOSE(IF(B3:B11=F3,C3:C11,"")) - Double press with left mouse button on cell F4
- Paste (Ctrl + v) formula to cell F4
- Select the entire formula in the formula bar
- Press Function key F9 and the formula is converted to an array of constants:
={"","CD","","","IJ","","","OP",""} - Delete the equal sign = in the formula bar and then press Enter
{"","CD","","","IJ","","","OP",""} - Select cell F4
1.1 Explaining formula
Step 1 - Logical expression
The equal sign is a logical operator that allows you to compare value to value. In this case, we are going to compare a single value to multiple values.
B3:B11=F3
becomes
{"S";"M";"L";"S";"M";"L";"S";"M";"L"}="M"
The result is TRUE or FALSE based on if the value is equal or not. TRUE and FALSE are boolean values that can be used in a IF function.
{"S";"M";"L";"S";"M";"L";"S";"M";"L"}="M"
returns
{FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(B3:B11=F3,C3:C11,"")
becomes
IF({FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {"AB";"CD";"EF";"GH";"IJ";"KL";"MN";"OP";"QR"},"")
and returns {"";"CD";"";"";"IJ";"";"";"OP";""}.
Step 3 - TRANSPOSE function
The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa. The CONCATENATE function needs the comma as a delimiting character to function properly.
TRANSPOSE(IF(B3:B11=F3,C3:C11,""))
becomes
TRANSPOSE({"";"CD";"";"";"IJ";"";"";"OP";""})
and returns
{"", "CD", "", "", "IJ", "", "", "OP", ""}.
2. Delete blanks in the array
- I am now going to delete all empty characters in the array, press CTRL + H to open "Search and Replace" Dialog box
- Type in Find what: "", and nothing in Replace with:
- Press with mouse on "Replace" button
- Type in Find what: ,"" and nothing in Replace with:
- Press with left mouse button on "Replace" button once again.
- Press with left mouse button on Close button.
3. Concatenate all values in the array
The final thing to do is to use the concatenate function to add all values into one text string.
- Double press with left mouse button on cell F4
- Delete the curly brackets from the formula: {}
- Add this: =CONCATENATE(
- and then an ending parentheses )
- Press Enter
4. Add delimiting characters
Perhaps you want a delimiting character between values, right after you have converted the formula to an array of constants (step 5, above), do this:
- The array looks like this: ={"","CD","","","IJ","","","OP",""}
- Add an ampersand and then a delimiting character with quotation marks.
={"","CD","","","IJ","","","OP",""}&"|" - Select the formula and press function key F9, the formula now looks like this:
={"|","CD|","|","|","IJ|","|","|","OP|","|"}
The ampersand has added the delimiting character to all values in the array. - Continue with step 6 above. Tip! To delete empty values in array, Search and Replace with these values "|", and ,"|"
Concatenate category
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]
Excel categories
2 Responses to “Concatenate cell values based on a condition”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
you have taught this for M, if i replace M with S in F3 then it's not changing dynamically..please suggest something
This article shows the steps you need to take in order to manually concatenate the values if your Excel version does not support the TEXTJOIN function (Office 365).
The following article demonstrates how to do this dynamically using the TEXTJOIN function:
https://www.get-digital-help.com/2010/12/20/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/#concatenate