Use TEXTJOIN() instead: =TEXTJOIN(“;”,TRUE,A1:A2000) For those who do not have OFFICE 365 Excel then use this UDF that mimics the TEXTJOIN Function. Put this in a module attached to the workbook and use the formula above to call. Function TEXTJOIN(delim As String, skipblank As Boolean, arr) Dim d As Long Dim c As Long Dim … Read more
Use¹ the following as an array formula. =TEXTJOIN(“–>”, TRUE, IF(LEN(C3:I3), C$2:I$2, “”)) Pre-Excel 2016 versions While you could just string together a series of IF statements, a cleaner alternate might be to write a user defined function (aka UDF). In a standard VBA module code sheet: Function udf_Stitch_Together(r As Range, _ h As Range, _ … Read more
Many ways: If one has the new Dynamic array formulas: =FILTER(C:C,(A:A=J1)*(B:B=J2)) If not then: Dealing with Number returns: If your return values are numbers and the match is unique(there is only one John Doe in the data) or you want to sum the returns if there are multiples, then Using SUMIFS is the quickest method. … Read more
This TextJoinIfs user-defined-function (aka UDF) provides basic TEXTJOIN functionality to Excel 2003 – 2013 versions as well as expanded functionality for all versions by adding optional error control, uniqueness, sorting and a paramarray of conditions for easy criteria. This TextJoinIfs UDF code belongs in a public module code sheet; e.g. Book1 – Module1 (code). Option … Read more