0

I have a list in cells A1:A16 of foods and want to create a unique list with each item only appearing once in the list:

Eggs
Bacon
Hashbrowns
Baked Beans
Avocado
Coffee
Bread
Bread
Sandwhich meet
Lettuce
Tomato
mustard
mayo
Crisps
Cheese
Crackers

I have tried the formula =unique(transpose(split(ArrayFormula(concatenate(A1:A16&" "))," ")))

but this creates a unique list and splits words such as "baked beans" into new items like so:

Eggs
Bacon
Hashbrowns
Baked
Beans
Avocado
Coffee
Bread
Sandwhich
meet
Lettuce
Tomato
mustard
mayo
Crisps
Cheese
Crackers

As you can see, "baked" and "beans" appears as two separate items. How can I make it one item in the new unique list?

0

I managed to get it working by changing what I am concatenating with.

Using a + to concatenate then split by the + I am able to avoid splitting sentences.

=unique(transpose(split(ArrayFormula(concatenate(A1:A16"+")),"+")))

0
  • to get a unique list of items you need only this:

    =UNIQUE(A1:A16)

  • in column format:

    =TRANSPOSE(UNIQUE(A1:A16))

    0

  • I should have been more clear, I still want each item to be in a column format – Stretch0 Apr 14 at 12:27
  • @Stretch0 answer updated – user0 Apr 14 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.