formula help

Question

I need help on writing a formula to consolidate text horizontally and vertically from one tab to another. almost like a vlookup and sumif for text. I tried to use =textjoin( function but was unsuccessful. Below is the example on what output i would need returned, in green.&nbsp;"Detail tab"Col 1&nbsp; &nbsp;Col 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Col 3 (drop down)Dog&nbsp; &nbsp; &nbsp;He went for a walk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ActivityCat&nbsp; &nbsp; &nbsp; She didn't eat her food&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FoodDog&nbsp; &nbsp; He is sleeping.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ActivityBear&nbsp; &nbsp; She is very happy right now.&nbsp; &nbsp; &nbsp; &nbsp; MoodCat&nbsp; &nbsp; &nbsp; She isn't feeling well.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Mood&nbsp;"Summary Tab"Animal&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Activity&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Food&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MoodDog&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;He went for a walk.&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; He is sleeping.Cat&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; She didn't eat her food.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;She isn't feeling well.&nbsp;Bear&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;She is very happy right now.&nbsp;&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

Ravi_Shah&nbsp;
In B2 on the Summary sheet, as an array formula confirmed with Ctrl+Shift+Enter:
&nbsp;
=TEXTJOIN(CHAR(10),TRUE,IF((Detail!$A$1:$A$27=$A2)*(Detail!$C$1:$C$27=B$1),Detail!$B$1:$B$27,""))
&nbsp;
Adjust the ranges as needed.
Fill to the right, then down (or vice versa).

ramiz_assaf · Answer

Ravi_Shah&nbsp;&nbsp;The easiest solution is using Power Query&nbsp;please see the attached fileI used :&nbsp;https://www.youtube.com/watch?v=ZBDrtjWkQM4&nbsp;&nbsp;&nbsp;

ravi_shah · Answer

Hans that isn't working for me, i've tried. The return i'm getting is everything see attached.&nbsp;HansVogelaar&nbsp;

hansvogelaar · Answer

Ravi_Shah&nbsp;
1) You have to confirm the formula with Ctrl+Shift+Enter to make it an array formula. It won't work as an ordinary formula.
2) There are spaces after some of the animal names on the Detail sheet in your sample workbook. If you remove those the (array) formula should return the correct results.

Forum Discussion

formula help

4 Replies

Resources