Compile a Column into a List

[nputt]

Hello, not sure if Excel even has this capability. (Using 365)
I have a meal planner that I use on Excel that separates columns by food type (protein, veggie, car, etc), I'd like to make a grocery list by combining the data listed in a single column into a single cell. EX: IF B3 = Lettuce, B4 = Tomatoes, and B5 = Apples; how can I make B7= Lettuce, Tomatoes, Apples by formula rather than manual entry.
Thank you.

 

[nputt]

Do you have the LET function in your version

I do not.

 

[Fluff]

Ok, how about

=TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,G4:G9,""),",",REPT(" ",100)),SEQUENCE(LEN(TEXTJOIN(",",TRUE,G4:G9,""))-LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,G4:G9,""),",",""))+1,,0)*100+1,100))))

I would advise you to get rid of those merged cells, they can cause a lot of problems.

 

[aghaffar82]

If you got the UNIQUE function, this thing works perfectly, put it in cell L2

=TEXTJOIN(", ",TRUE,(UNIQUE(G4:G9)),"")

 

[nputt]

.

 

[Fluff]

@aghaffar82
Whilst that does work for col G it does not for col I.

@nputt
You've already said that.

 

[nputt]

If you got the UNIQUE function, this thing works perfectly, put it in cell L2

=TEXTJOIN(", ",TRUE,(UNIQUE(G4:G9)),"")

Thank you, everyone!

 

[nputt]

@aghaffar82
Whilst that does work for col G it does not for col I.

@nputt
You've already said that.

Computer is lagging and giving me problems, sorry for double posting answers :P

 

[Fluff]

Not a problem.

It may not be a problem for you, but if you use @aghaffar82 formula in col H you will get
Tomato Sauce, Beans, Tomato Sauce, Pickles
rather than
Tomato Sauce, Beans, Pickles

which my formula will give you.

 

[nputt]

Not a problem.

It may not be a problem for you, but if you use @aghaffar82 formula in col H you will get
Tomato Sauce, Beans, Tomato Sauce, Pickles
rather than
Tomato Sauce, Beans, Pickles

which my formula will give you.

The LET function might be hidden in my Insert Functions. I'll look back through them when I get the chance again (at work.) If I do have the LET function what would that look like?

 

[Fluff]

Like

=LET(Joined,TEXTJOIN(",",TRUE,G4:G9,""), TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(Joined,",",REPT(" ",100)),SEQUENCE(LEN(Joined)-LEN(SUBSTITUTE(Joined,",",""))+1,,0)*100+1,100)))))