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]

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

Ended up finding it (LET) and it worked like a charm. Thank you everyone for your assistance! I've learned quite a bit today!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Ended up finding it (LET) and it worked like a charm.

Are you sure?

In cell I14 below I have used the suggested formula. You will notice that the result includes two "non-ingredients" ("Garli" and "c & Onion Powder") and excludes one real ingredient ("Ranch")
The issue could possibly be overcome by increasing the 100 values in the formula to, say, 1000 but the issue could still arise if there is a reasonably long list of ingredients in a column.

An alternative that should do the job without having to figure out how many spaces to insert is provided in I15.

nputt.xlsm
	I
4	Italian Seasoning, Butter
5	Soy Sauce
6	Mayo, Mustard, Ketchup
7	Garlic & Onion Powder, Minced Garlic, Butter
8	Garlic & Onion Powder, Cajun, Butter
9	Ketchup, Mustard, Mayo, BBQ, Buffalo, Ranch
10
11
12
13
14	Italian Seasoning, Butter, Soy Sauce, Mayo, Mustard, Ketchup, Garlic & Onion Powder, Minced Garlic, Garli, c & Onion Powder, Cajun, BBQ, Buffalo
15	Italian Seasoning, Butter, Soy Sauce, Mayo, Mustard, Ketchup, Garlic & Onion Powder, Minced Garlic, Cajun, BBQ, Buffalo, Ranch
Sheet1

Cell Formulas
Range	Formula
I14	I14	=LET(Joined,TEXTJOIN(",",TRUE,I4:I9,""), TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(Joined,",",REPT(" ",100)),SEQUENCE(LEN(Joined)-LEN(SUBSTITUTE(Joined,",",""))+1,,0)*100+1,100)))))
I15	I15	=LET(Joined,","&TEXTJOIN(",",TRUE,I4:I9)&",",Parts,REPLACE(Joined,1,FIND("#",SUBSTITUTE(Joined,",","#",SEQUENCE(LEN(Joined)-LEN(SUBSTITUTE(Joined,",",""))-1))),""),TEXTJOIN(", ",,UNIQUE(TRIM(LEFT(Parts,FIND(",",Parts)-1)))))

 

[nputt]

Are you sure?

In cell I14 below I have used the suggested formula. You will notice that the result includes two "non-ingredients" ("Garli" and "c & Onion Powder") and excludes one real ingredient ("Ranch")
The issue could possibly be overcome by increasing the 100 values in the formula to, say, 1000 but the issue could still arise if there is a reasonably long list of ingredients in a column.

An alternative that should do the job without having to figure out how many spaces to insert is provided in I15.

nputt.xlsm
	I
4	Italian Seasoning, Butter
5	Soy Sauce
6	Mayo, Mustard, Ketchup
7	Garlic & Onion Powder, Minced Garlic, Butter
8	Garlic & Onion Powder, Cajun, Butter
9	Ketchup, Mustard, Mayo, BBQ, Buffalo, Ranch
10
11
12
13
14	Italian Seasoning, Butter, Soy Sauce, Mayo, Mustard, Ketchup, Garlic & Onion Powder, Minced Garlic, Garli, c & Onion Powder, Cajun, BBQ, Buffalo
15	Italian Seasoning, Butter, Soy Sauce, Mayo, Mustard, Ketchup, Garlic & Onion Powder, Minced Garlic, Cajun, BBQ, Buffalo, Ranch
Sheet1

Cell Formulas
Range	Formula
I14	I14	=LET(Joined,TEXTJOIN(",",TRUE,I4:I9,""), TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(Joined,",",REPT(" ",100)),SEQUENCE(LEN(Joined)-LEN(SUBSTITUTE(Joined,",",""))+1,,0)*100+1,100)))))
I15	I15	=LET(Joined,","&TEXTJOIN(",",TRUE,I4:I9)&",",Parts,REPLACE(Joined,1,FIND("#",SUBSTITUTE(Joined,",","#",SEQUENCE(LEN(Joined)-LEN(SUBSTITUTE(Joined,",",""))-1))),""),TEXTJOIN(", ",,UNIQUE(TRIM(LEFT(Parts,FIND(",",Parts)-1)))))

Thank you. I was actually just coming here today to say that for some reason one of my cells was not being included. This response resolved the issues fully.

 

[Peter_SSs]

You're welcome.