Formula extracting unique column values

**labogola** · 09-09-2013, 08:25 AM

I know there is an easier way to extract unique items in a column but I'm trying to get better with formulas.
test.png
For some reason the formula I'm using here doesn't work. It's the same as the one found here http://cdn.get-digital-help.com/wp-c...-in-excel3.xls although with a slightly different list.

I'm curious, what's going wrong?

**zbor** · 09-09-2013, 08:29 AM

Please upload example workbook. Nobody wants to retype your data from a picture.

**Debraj Roy** · 09-09-2013, 08:31 AM

Check cell's format...

If Text.. change to general..
and dont forget to confirm the formula by pressing Ctrl + Shift + Enter.. not just Enter..

You may found a {} curly brackets in the formula after CSE confirmation..

**MarvinP** · 09-09-2013, 08:32 AM

Hi labogola,

Did you hold down the Ctrl and the Shift keys before you pressed the Enter key? These are Array formulas and need to be entered using this three key combination. See: http://www.cpearson.com/excel/ArrayFormulas.aspx for a good introduction.

**Fotis1991** · 09-09-2013, 08:32 AM

Try in this way.

IN G1 put this.

=IF(F1="","",F1)

In G2 and copy down use this.

=INDEX(F$1:F$10,MATCH(1,INDEX((COUNTIF(G$1:G1,F$1:F$10&"")=0)*(F$1:F$10<>""),0),0))

Comments?

your formula is an ARRAY formula.

**labogola** · 09-09-2013, 09:27 PM

Fotis1991 your way works but I'm still curious why my way isn't.

I've attached a spreadsheet with an example this time...example.xlsx

**benishiryo** · 09-09-2013, 09:44 PM

hi there again labogola. you usually cant do a formula to look at the cell the formula is in (ROWS is one you can use). for eg. you are now using COUNTIF in the cell itself. in B1, you are using
COUNTIF($B$1:B1,$A$1:$A$9)
that turns into a circular reference. so insert a row in row 1 & then in B2:
=INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10),0))

you seem to be very interested in learning formulas. perhaps this post i did may interest you. do look out for an update tomorrow though. i missed out something in 1 of the sheet
http://www.excelforum.com/tips-and-t...e-learned.html

**Fotis1991** · 09-10-2013, 02:34 AM

..Fotis1991 your way works but I'm still curious why my way isn't.

I've attached a spreadsheet with an example this time...example.xlsx

Hi there

First i'd like to say that-as a rule-if you have a solution using regular formula is better to avoid the ARRAY formulas.

Secondly, as benishiryo mentioned, sometimes our formulas create circular reference.

It's better to avoid to use these but in some cases(like if we want to get a Time-Date Stamp, using formulas) we can not avoid these and we have to use these. So in this case we do this.

Choose Tools/Options/Calculation (Preferences/Calculation for Macs) and check the Iteration checkbox.

So in my example sheet both ways(yours & mine) gives the same-correct answer.

In the worksheet example i have also a pic of what i see using our ways.