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:
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,""))
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...