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.

 

[Fluff]

Hi & welcome to MrExcel.
Have a look at TextJoin How to use the Excel TEXTJOIN function | Exceljet

 

[nputt]

TEXTJOIN worked perfectly as described, but I ran into another problem. I have duplicate entries within the same cell now. I've attached a picture of the sheet I'm currently working with to help explain. I have a dinner list and a grocery list. I use the information from my dinner list to fill out the grocery list section. Is there a way to remove duplicate data or to alphabetize the order it's in?

 

[Peter_SSs]

Could we have the sample data and expected results with XL2BB instead of a picture that we cannot copy from (& can hardly read)?

 

[nputt]

Could we have the sample data and expected results with XL2BB instead of a picture that we cannot copy from (& can hardly read)?

I don't have that as an option in my Add-Ins.

 

[Fluff]

You need to download it from here XL2BB - Excel Range to BBCode

 

[nputt]

You need to download it from here XL2BB - Excel Range to BBCode

It wasn't allowing me to "find" it in the dialogue box but I figured it out eventually. Thank you.

 

[nputt]

Meal Planning.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1											Compiled Shopping List
2											Veggies Fruit	Lettuce, Tomatoes, Mushrooms, Lettuce, Tomatoes
3
4	1	Chicken Parmesan	N/A	Chicken Patties	Spaghetti	Parmesan Shredded		Tomato Sauce	Italian Seasoning, Butter
5	2	Sausage Beans & Rice	N/A	Polska Sausage	Rice			Beans, Tomato Sauce	Soy Sauce
6	3	Turkey Burgers	Fries?	Turkey Burgers	Buns		Lettuce, Tomatoes	Pickles	Mayo, Mustard, Ketchup		Raw Meat Frozen Meat	Chicken Patties, Polska Sausage, Turkey Burgers, Porkchops, Tilapia, Chicken Patties
7	4	Garlic Mushroom Pork	Baked potatoes	Porkchops	Potatoes		Mushrooms		Garlic & Onion Powder, Minced Garlic, Butter
8	5	Cajun Tilapia Gumbo	N/A	Tilapia	Rice			Beans, Tomato Sauce	Garlic & Onion Powder, Cajun, Butter
9	6	Chicken Patty Sandwiches	Fries?	Chicken Patties	Buns	Cheddar/Swiss Sliced	Lettuce, Tomatoes	Pickles	Ketchup, Mustard, Mayo, BBQ, Buffalo, Ranch
10											Dairy	Parmesan Shredded, Cheddar/Swiss Sliced
11
12
13
14											Lunch Snacks
15
16
17
18											Packaged & Carbs	Tomato Sauce, Beans, Tomato Sauce, Pickles, Beans, Tomato Sauce, Pickles
19
20												Spaghetti, Rice, Buns, Potatoes, Rice, Buns
21
22											Condiments, Etc	Italian Seasoning, Butter, Soy Sauce, Mayo, Mustard, Ketchup, Garlic & Onion Powder, Minced Garlic, Butter, Garlic & Onion Powder, Cajun, Butter, Ketchup, Mustard, Mayo, BBQ, Buffalo, Ranch
23
24
25
26											TP, PT, Tissues, Bags, Cleaning, Hygiene, Health, Other
27
28
29
30
31
Dinner Planning

Cell Formulas
Range	Formula
L2	L2	=TEXTJOIN(", ",TRUE,G4:G9,"")
L6	L6	=TEXTJOIN(", ",TRUE,D4:D9)
L10	L10	=TEXTJOIN(", ",TRUE,F4:F9)
L18	L18	=TEXTJOIN(", ",TRUE,H4:H9)
L20	L20	=TEXTJOIN(", ",TRUE,E4:E9)
L22	L22	=TEXTJOIN(", ",TRUE,I4:I9)

 

[nputt]

Meal Planning.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1											Compiled Shopping List
2											Veggies Fruit	Lettuce, Tomatoes, Mushrooms, Lettuce, Tomatoes
3
4	1	Chicken Parmesan	N/A	Chicken Patties	Spaghetti	Parmesan Shredded		Tomato Sauce	Italian Seasoning, Butter
5	2	Sausage Beans & Rice	N/A	Polska Sausage	Rice			Beans, Tomato Sauce	Soy Sauce
6	3	Turkey Burgers	Fries?	Turkey Burgers	Buns		Lettuce, Tomatoes	Pickles	Mayo, Mustard, Ketchup		Raw Meat Frozen Meat	Chicken Patties, Polska Sausage, Turkey Burgers, Porkchops, Tilapia, Chicken Patties
7	4	Garlic Mushroom Pork	Baked potatoes	Porkchops	Potatoes		Mushrooms		Garlic & Onion Powder, Minced Garlic, Butter
8	5	Cajun Tilapia Gumbo	N/A	Tilapia	Rice			Beans, Tomato Sauce	Garlic & Onion Powder, Cajun, Butter
9	6	Chicken Patty Sandwiches	Fries?	Chicken Patties	Buns	Cheddar/Swiss Sliced	Lettuce, Tomatoes	Pickles	Ketchup, Mustard, Mayo, BBQ, Buffalo, Ranch
10											Dairy	Parmesan Shredded, Cheddar/Swiss Sliced
11
12
13
14											Lunch Snacks
15
16
17
18											Packaged & Carbs	Tomato Sauce, Beans, Tomato Sauce, Pickles, Beans, Tomato Sauce, Pickles
19
20												Spaghetti, Rice, Buns, Potatoes, Rice, Buns
21
22											Condiments, Etc	Italian Seasoning, Butter, Soy Sauce, Mayo, Mustard, Ketchup, Garlic & Onion Powder, Minced Garlic, Butter, Garlic & Onion Powder, Cajun, Butter, Ketchup, Mustard, Mayo, BBQ, Buffalo, Ranch
23
24
25
26											TP, PT, Tissues, Bags, Cleaning, Hygiene, Health, Other
27
28
29
30
31
Dinner Planning

Cell Formulas
Range	Formula
L2	L2	=TEXTJOIN(", ",TRUE,G4:G9,"")
L6	L6	=TEXTJOIN(", ",TRUE,D4:D9)
L10	L10	=TEXTJOIN(", ",TRUE,F4:F9)
L18	L18	=TEXTJOIN(", ",TRUE,H4:H9)
L20	L20	=TEXTJOIN(", ",TRUE,E4:E9)
L22	L22	=TEXTJOIN(", ",TRUE,I4:I9)

I was trying to avoid having to have each ingredient in a different cell to save space on the spreadsheet, but I'd like to remove any duplicates from my textjoin cells.

 

[Fluff]

Do you have the LET function in your version