## Extract multiple values into one unique cell if matching criteria with a formula in an excel report

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,""))