Formula: return unique values

**Bramjonker** · 06-19-2012, 11:15 AM

Hi, Hope someone can help me with this headbreaking problem

I have a huge list of items:

Bread
butter
milk
cheese
milk
milk
Cheese
Gum
Water
Milk
Cheese
Bread
Bread
butter
milk
cheese
milk
milk
Cheese

I need a formula to return the unique items, so:
Bread
Butter
Milk
Cheese
Gum
Water

The items are in column A and would like to have the unique list (automatically) in column B

Thank you so much in advance

**icestationzbra** · 06-19-2012, 11:22 AM

if the above list is in cells A2 through A20, put the following CSE formula in B2 (ensure that B1 IS blank or has a value that does not exist between A2 through A20):

Please Login or Register  to view this content.

**Bramjonker** · 06-19-2012, 11:27 AM

Thanks for your help,

But it doesn't work. It only displays -

**Fotis1991** · 06-19-2012, 11:28 AM

Hi

With your list starting in A2, in B2(i need an empty, or title list name in B1), use this.

=LOOKUP(REPT("Z",255),CHOOSE({1\2},"",INDEX($A$2:$A$100,MATCH(TRUE,INDEX(ISNA(MATCH($A$2:$A$100,$B$2:$B2,0)),0),0))))

Is this, works for you?

**Bramjonker** · 06-19-2012, 11:39 AM

Wow!!! This is really great!!!!!

**Fotis1991** · 06-19-2012, 11:45 AM

..So, I think I can guess that formula works well for you....

Thanks for the feed back and also for the reb*.

Pls, mark your thread, as SOlved.

**icestationzbra** · 06-19-2012, 12:22 PM

Originally Posted by Bramjonker

Thanks for your help,

But it doesn't work. It only displays -

did you observe i said that it is a CSE formula? i have in my signature instructions on how to enter a CSE formula - you have to confirm such formulae with CTRL+SHIFT+ENTER, not just with ENTER.