Forum Discussion
Formula to find and combine text
Does anyone know how to get excel to pull and combine text from different cells that meet specific requirements? I am needing to combine the text that has a specific value from column G and has a specific number is column E. There are multiple cells that meet the requirement it’s possible to have 20 or more meet it. Each row has it’s own description and while some are the same I need to combine the different text that meet the requirements together.
This is a much smaller and simplified version of the data file I get
here us a simplified and shorter version of what I am moving it to
7 Replies
=TEXTJOIN(", ",,FILTER(A2:A16,(E2:E16=M2)*(G2:G16=M3)))
With a recent version of Excel you can apply this formula. The criteria for cols E and G can be entered in cells M2 and M3 and the formula dynamically updates the result.
- =TEXTJOIN(",",,FILTER('DATA EXPORT'!Y:Y,('DATA EXPORT'!I:I=K5)*('DATA EXPORT'!F:F=B5)))
Here is what the formula I created based on your advice is. I am getting the #CALC! error? Any suggestions on how to fix that?The most likely reason is that there is an empty array which means that there aren't any filter results.
How to correct a #CALC! error - Microsoft Support
=TEXTJOIN(",",,FILTER(Y:Y,(I:I=K5)*(F:F=B5)))
In my example the CALC! error is returned in cell AA13 because there isn't a match for GL code 102 and for description canteen in columns F and I.
Please add an example of what you have and what you're expecting to see. Preferably as an image and/or text table.
