formula help

Copper Contributor

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. 

 

 

 

4 Replies

@Ravi_Shah 

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).

@Ravi_Shah 

 

The easiest solution is using Power Query 

please see the attached file

I used : https://www.youtube.com/watch?v=ZBDrtjWkQM4

 

 

 

Hans that isn't working for me, i've tried. The return i'm getting is everything see attached. @Hans Vogelaar 

@Ravi_Shah 

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.