Contents
I already wrote about how you can quickly glue text from several cells into one and, conversely, parse a long text string into components. Now let’s look at a close, but slightly more complex task – how to glue text from several cells when a certain specified condition is met.
Let’s say that we have a database of customers, where one company name can correspond to several different emails of its employees. Our task is to collect all the addresses by company names and concatenate them (separated by commas or semicolons) in order to make, for example, a mailing list for customers, i.e. get output something like:
In other words, we need a tool that will glue (link) the text according to the condition – an analogue of the function SUMMESLI (SUMIF), but for text.
Method 0. Formula
Not very elegant, but the easiest way. You can write a simple formula that will check whether the company in the next row differs from the previous one. If it does not differ, then glue the next address separated by a comma. If it differs, then we “reset” the accumulated, starting again:
The disadvantages of this approach are obvious: from all the cells of the additional column obtained, we need only the last ones for each company (yellow). If the list is large, then in order to quickly select them, you will have to add another column using the function DLSTR (LEN), checking the length of the accumulated strings:
Now you can filter out the ones and copy the necessary address gluing for further use.
Method 1. Macrofunction of gluing by one condition
If the original list is not sorted by company, then the above simple formula does not work, but you can easily get around with a small custom function in VBA. Open the Visual Basic Editor by pressing a keyboard shortcut Alt + F11 or using the button Visual Basic tab developer (Developer). In the window that opens, insert a new empty module through the menu Insert – Module and copy the text of our function there:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings are not equal to each other - we exit with an error If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Function End If 'go through all the cells, check the condition and collect the text in the variable OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'display results without last delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End function
If you now return to Microsoft Excel, then in the list of functions (button fx in the formula bar or tab Formulas – Insert Function) it will be possible to find our function MergeIf in category User Defined (User Defined). The arguments to the function are as follows:
Method 2. Concatenate text by inexact condition
If we replace the first character in the 13th line of our macro = to the approximate match operator Like, then it will be possible to carry out gluing by an inexact match of the initial data with the selection criterion. For example, if the company name can be written in different variants, then we can check and collect them all with one function:
Standard wildcards are supported:
- asterisk (*) – denotes any number of any characters (including their absence)
- question mark (?) – stands for any single character
- pound sign (#) – stands for any one digit (0-9)
By default, the Like operator is case sensitive, i.e. understands, for example, “Orion” and “orion” as different companies. To ignore case, you can add the line at the very beginning of the module in the Visual Basic editor Option Compare Text, which will switch Like to be case insensitive.
In this way, you can compose very complex masks for checking conditions, for example:
- ?1##??777RUS – selection of all license plates of the 777 region, starting with 1
- LLC* – all companies whose name begins with LLC
- ##7## – all products with a five-digit digital code, where the third digit is 7
- ????? – all names of five letters, etc.
Method 3. Macro function for gluing text under two conditions
In the work there may be a problem when you need to link the text more than one condition. For example, let’s imagine that in our previous table, one more column with the city was added, and gluing should be carried out not only for a given company, but also for a given city. In this case, our function will have to be slightly modernized by adding another range check to it:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'delimiter characters (can be replaced with space or ; etc.) e.) 'if the validation and gluing ranges are not equal to each other, exit with an error If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'go through all cells, check all conditions and collect the text into the variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'display results without last delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
It will be applied in exactly the same way – only arguments now need to be specified more:
Method 4. Grouping and gluing in Power Query
You can solve the problem without programming in VBA, if you use the free Power Query add-in. For Excel 2010-2013 it can be downloaded here, and in Excel 2016 it is already built in by default. The sequence of actions will be as follows:
Power Query does not know how to work with regular tables, so the first step is to turn our table into a “smart” one. To do this, select it and press the combination Ctrl+T or select from the tab Home – Format as a table (Home — Format as Table). On the tab that then appears Constructor (Design) you can set the table name (I left the standard Table 1):
Now let’s load our table into the Power Query add-in. To do this, on the tab Data (if you have Excel 2016) or on the Power Query tab (if you have Excel 2010-2013) click From the table (Data — From Table):
In the query editor window that opens, select the column by clicking on the header Company and press the button above Group (Group By). Enter the name of the new column and the type of operation in the grouping – All lines (All Rows):
Click OK and we get a mini-table of grouped values for each company. The contents of the tables are clearly visible if you left-click on the white background of the cells (not on the text!) in the resulting column:
Now let’s add one more column, where, using the function, we glue the contents of the Address columns in each of the mini-tables, separated by commas. To do this, on the tab Add column we press Custom column (Add column — Custom column) and in the window that appears, enter the name of the new column and the coupling formula in the M language built into Power Query:
Note that all M-functions are case sensitive (unlike Excel). After clicking on OK we get a new column with glued addresses:
It remains to remove the already unnecessary column TableAddresses (right click on title) Delete column) and upload the results to the sheet by clicking on the tab Home — Close and download (Home — Close and load):
Important nuance: Unlike the previous methods (functions), tables from Power Query are not updated automatically. If in the future there will be any changes in the source data, then you will need to right-click anywhere in the results table and select the command Update & Save (Refresh).
- How to split a long text string into parts
- Several ways to glue text from different cells into one
- Using the Like operator to test text against a mask