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 function CONCATENATE , but you will need to select the required cells manually. To simplify the work, we need to automate the process connection of text by condition from different cells . So if you need it fast "hook" text from different cells using a condition IF , then you should write your own function.
Table of secured loans:
So, let's create our own function and name it CONCATIF . To do this, open the menu Service - Macros - Visual Basic Editor , insert module VBA (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 ( column with searched values ; search value ; column with data to join )
We will have the following result:
And finally, you need to fix the ranges A2:A10 and C2:C10 dollar sign ($) , so that the ranges do not move downwards when copying the function.