This formula allows me to extract all data from a column into one single cell based on some specific criteria, meaning that if the criteria match, it will return all values from the column. I can put 1, 2 or 3 or more criteria to match, even looking for a specific character instead of the full word. I will also provide the option to get only unique result so no duplicate.

 

When I use the formula ?

When I need to extract all values of a column based on criteria.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formulas ?

=TEXTJOIN()

=IFERROR()

=IF()

=SEARCH()

=FREQUENCY()

=ISNUMBER()

=MATCH()

=ROW()

=MIN()

=UNIQUE()

=FILTER()

The formula for only 1 criteria:

=TEXTJOIN(" ",TRUE,IFERROR(IF(SEARCH("United-States",$A$2:$A$10),$B$2:$B$10,""),""))

Take note that the “textjoin” function is available if you are using excel from Office 365 and you may not have it if you are using a retail version. If it is the case, you will need to create it into the visual basic:

• On your keyboard, press “Fn + ALT + F11”
• Click on “insert -> module”
• Put this code then close the visual basic

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
Else
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function

As you can see, it didn’t show the result I want this is because for this formula, I need to press in the keyboard this combination:

CTRL + SHIFT + ENTER

This formula also works by putting a word as criteria, for instance, instead of “United-States”, I will put only “ted”

=TEXTJOIN(" ",TRUE,IFERROR(IF(SEARCH("ted",$A$2:$A$10),$B$2:$B$10,""),""))

If I prefer to use a cell reference, just replace “United-States” by the cell reference, for instance:

=TEXTJOIN(" ",TRUE,IFERROR(IF(SEARCH(E2,$A$2:$A$10),$B$2:$B$10,""),""))

Instead to put a space for the result, I can choose for instance to put a comma, for instance:

=TEXTJOIN(", ",TRUE,IFERROR(IF(SEARCH(E2,$A$2:$A$10),$B$2:$B$10,""),""))

If I want to add another criteria, I just need to put another “search”, for instance, I will add the “priority”:

=TEXTJOIN(" ",TRUE,IFERROR(IF(SEARCH("United-States",$A$2:$A$10)*SEARCH("P1",$C$2:$C$10),$B$2:$B$10,""),""))

And if I want to add more, I will apply the same principle.

If you take a look, you can see that for US and for France, there are duplicate incidents so this is the formula to get only unique values:

=TEXTJOIN(" ",1,UNIQUE(FILTER($B$2:$B$10,ISNUMBER(SEARCH("United-States",$A$2:$A$10)))))

UNIQUE and FILTER may not be available with your excel version, in this case, use this formula:

=TEXTJOIN(" ",1,IF(FREQUENCY(IF(ISNUMBER(SEARCH("United-States",$A$2:$A$10)),MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),$B$2:$B$10,""))

Again same thing as above, if you want to put more criteria, just put another “search”, for instance, adding the priority:

=TEXTJOIN(" ",1,UNIQUE(FILTER($B$2:$B$10,ISNUMBER(SEARCH("United-States",$A$2:$A$10)*SEARCH("P1",$C$2:$C$10)))))

The formula without UNIQUE and FILTER:

=TEXTJOIN(" ",1,IF(FREQUENCY(IF(ISNUMBER(SEARCH("United-States",$A$2:$A$10)*SEARCH("P1",$C$2:$C$10)),MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),$B$2:$B$10,""))