Creating a list of unique values.

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

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..

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.

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_________

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

Hi macroll,

Originally Posted by 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.

Good Stuff

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.

it is not work out

Hi siju,

Originally Posted by siju

it is not work out

In what way?

Alan.

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