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:
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.