Concatenation of text in cells by condition (CONCATIF)

Description

Let's say we have a table of loans with corresponding security. We need, for example, to display a list of collateral for each loan in one cell.

Excel can only provide us with a simple CONCATENATE function, but you will need to select the necessary cells manually. In order to simplify the work, we need to automate the process of combining text from different cells according to a condition. So, if you need to quickly "concatenate" text from different cells using the IF condition, then you should write your own function.

Table of secured loans:

VBA code for the CONCATIF function

So, let's create our own function and call it CONCATIF. To do this, open the menu Service - Macros - Visual Basic Editor, insert the VBA module (menu Insert - Module) and copy the text of this function there:

Function CONCATIF(Table As Range, SearchValue As Variant, Table2 As Range)
	'moonexcel.com.ua
	Dim i As Integer
	
	For i = 1 To Table.Rows.Count
		If Table.Cells(i, 1) = SearchValue Then
			If Not IsEmpty(Table2.Cells(i, 1).Value) Then
			    CONCATIF = CONCATIF & Table2.Cells(i, 1).Value & "; "
			End If
		End If
	Next i
	
	CONCATIF = Left(CONCATIF, Len(CONCATIF) - 2)
End Function

Close Visual Basic Editor and return to Excel.

Now, in Function wizard in category User defined you can find our CONCATIF function and use it. The syntax of this function is as follows:

=CONCATIF (searched values column; searched values; data column to combine)

We will have the following result:

And finally, you need to lock the ranges A2:A10 and C2:C10 with a dollar sign ($) so that the ranges don't move to the bottom when you copy the function.

Related Articles: