Sep 28 2020 10:45 AM
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.
"Detail tab"
Col 1 Col 2 Col 3 (drop down)
Dog He went for a walk Activity
Cat She didn't eat her food Food
Dog He is sleeping. Activity
Bear She is very happy right now. Mood
Cat She isn't feeling well. Mood
"Summary Tab"
Animal Activity Food Mood
Dog He went for a walk.
He is sleeping.
Cat She didn't eat her food. She isn't feeling well.
Bear She is very happy right now.
Sep 28 2020 11:29 AM
In B2 on the Summary sheet, as an array formula confirmed with Ctrl+Shift+Enter:
=TEXTJOIN(CHAR(10),TRUE,IF((Detail!$A$1:$A$27=$A2)*(Detail!$C$1:$C$27=B$1),Detail!$B$1:$B$27,""))
Adjust the ranges as needed.
Fill to the right, then down (or vice versa).
Sep 28 2020 11:43 AM - edited Sep 28 2020 11:43 AM
The easiest solution is using Power Query
please see the attached file
I used : https://www.youtube.com/watch?v=ZBDrtjWkQM4
Sep 28 2020 01:01 PM
Hans that isn't working for me, i've tried. The return i'm getting is everything see attached. @Hans Vogelaar
Sep 28 2020 01:08 PM
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.