Suppose we have such table of loans with respective collaterals:
We need, for instance, to display list of collaterals for each loan in the one cell.
Excel can provide us only simple formula CONCATENATE, but you should manually choose required cells. So, If you need to concatenate text from the different cells by the condition, you should write own formula.
Let's write and name it like CONCATIF.
Open menu Service - Macros - Visual Basic Editor, insert new module (menu Insert - Module) and copy to there text of this function:
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 the Function wizard in category User definded you can find our function CONCATIF and use it. Syntaxes of the function follow:
=CONCATIF (column with condition values;condition value; column with value for concatenate)
So we will have such result:
At the end, you should fix the ranges A2:A10 and C2:C10 by dollar sign ($) in order to the ranges don't slide downstairs during you copy the formula.