# Off Topic > Tips and Tutorials >  >  Creating a list of unique values.

## littleIdiot

How is this different to doing the following?
Data > Filter > Advanced Filter > Unique records only

----------


## Donna

Alan formula worked good, but when I change the $A$13 to the $74 for the number in MY list, I get error. Don't know what I did wrong..

----------


## min5h

I have been looking for the simple fix to this excel problem for ages, wondering about it for years. Up to now I have been going into MS access and running a count query on the table with all the extra entries. This suits me perfectly: 

Advanced Filter - unique?
littleIdiot wrote on December 31, 1969 18:00 EST  
How is this different to doing the following? 
Data > Filter > Advanced Filter > Unique records only 

Can't get the other formula's to work but thanks very much for the advanced filter tip.

----------


## ExcelTip

Problem:							
\"Column A contains a list of values. Each value may appear more than once.
How could we create a distinct list in which each value from List will appear only once?\"							

Solution:							
\"Using the INDEX, MATCH and COUNTIF functions, in the following array formula:
{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B2:B2,$A$2:$A$8),0))}
The first cell in the distinct list (column B) must remain empty.
The formula should be entered in the second cell and copied down.\"							

List_________Distinct List						
Red_________							
Blue_________	Red						
Green_________	Blue						
Yellow_________	Green						
Green_________	Yellow						
Blue_________	#N/A						
Blue_________

----------


## macroll

man, i can't get this to work???

----------


## Alan

Hi macroll,





> man, i can't get this to work???



I haven't tried to decipher the tip above, but this should do what you want:

If you have a list in A1:A13 as follows:

Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In B1 enter either "Alan" or link to A1

In B2 enter this as an array formula:

{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}

To enter the array formula, type it in without the curly brackets, and then enter using Shift-Ctrl-Enter and excel will put the braces on the formula.

Copy that formula down to B2:B13.

It returns 'Not Applicable' in B6:B13 since there are no more unique entries.  If you want to show something else (or a blank cell), just wrap it in a check using the ISNA function.  That would be as follows (in B2):

{=IF(ISNA(OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)),"",OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1))}

I prefer to return #NA myself and trap for that whereever I use the results - just a preference though.

HTH,

Alan.

----------


## Sid

Good Stuff

----------


## someone

In the initial formula there is something the author forgot to mention:
B3 Formula: {=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B2:B2,$A$2:$A$8),0))}
B4 Formula: {=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B2:B3,$A$2:$A$8),0))}
B5 Formula: {=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B2:B4,$A$2:$A$8),0))}

And so on... (First array in COUNTIF should be all the cells above the current cell).

Also, to enter these formulas, you need to press CTRL+SHIFT+ENTER when you\'re done entering the formula. This will convert the formula into an array formula.

Hope it helps.

----------


## siju

it is not work out

----------


## Alan

Hi siju,





> it is not work out



In what way?

Alan.

----------


## gjcase

This is different than using the advanced filter in that the filter list does not update if the list changes, whereas this does.

----------


## Alan

Hi gjcase,





> This is different than using the advanced filter in that the filter list does not update if the list changes, whereas this does.



If you want an autofilter / advanced filter to automatically update you will need to use VBA.

Alan.

----------

