Create grocery list without any blanks

[coccio]

I am trying to create a list in column C without any blanks

I select a food in column B mark it with a x and it highlights the food which it does and then I want it listed in Column C without any blanks cells between the foods as in this case I would like to see it listed in column C as
C2 = Avocados
C3 = Cucumbers
C3 = Zucchini

 

[JoeMo]

If your version of Excel includes the FILTER function try:

Book1
	A	B	C
1	Food	Grocery	List
2	Avocados	x	Avocados
3	Lemons		Cucumbers
4	Apples		Zucchini
5	Cucumbers	x
6	Carrot
7	Celery
8	Zucchini	x
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A8,B2:B8="x","")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=$B2="x"	text	NO

 

[coccio]

Filter function doesn't work in my version of excel

 

[JoeMo]

Filter function doesn't work in my version of excel

OK. If you have Excel 2010 or later maybe:

Book1
	A	B	C
1	Food	Grocery	List
2	Avocados	x	Avocados
3	Lemons		Cucumbers
4	Apples		Zucchini
5	Cucumbers	x
6	Carrot
7	Celery
8	Zucchini	x
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($B$2:$B$8="x"),ROWS(A$2:A2))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A8	Expression	=$B2="x"	text	NO

 

[coccio]

I have 2016 and I was trying to get the iferror working for me and you got it solved thank you

 

[JoeMo]

I have 2016 and I was trying to get the iferror working for me and you got it solved thank you

You are welcome - thanks for the reply.

 

[Peter_SSs]

I have 2016

Please add that information to your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)