Hi, I am trying to create a formula that will merge text from multiple cells, depending on certain criteria being met, and for new carriage returns to be entered between each merged text (using CONCATENATE). Here is what I mean:
Here is the original table of data:
Excel1.png
The table shows three rows of data entries (Blurb, Text, and Copy) and each row of data has a series of checks against numbered criteria listed in row 5. So, for example, Blurb has checks for criteria 1, 3, and 5, Text has checks for criteria 2, 4 and 7, and Copy has checks for 2, 6, and 7.
This means that both Text and Copy have checks under criteria 2 and 7. That means that the 'Text' and 'Copy' values, along with their corresponding values under the Type column must be merged and appear in an output/destination cell (similar to what is seen in cell K19). The text from each row then need to appear on separate lines in the same cell (as if carriage returns have been entered - like using the CONCATENATE function.
In other words, the formula needs to check the rows for an 'X' that has been added into the relevant criteria columns horizontally...
Excel2.png
It then needs to look down the column and find any other matches, before merging/showing the result in a destination cell.
Excel3.png
I hope I have explained that correctly :-)
Thanks for your help.
Bookmarks