» Retrieving Unique Values From A List

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

Hi macroll,

[QUOTE=macroll]man, i can't get this to work???[/QUOTE]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,

[QUOTE=siju]it is not work out[/QUOTE]In what way?

Alan.

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

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

Hi gjcase,

[QUOTE=gjcase]This is different than using the advanced filter in that the filter list does not update if the list changes, whereas this does.[/QUOTE]If you want an autofilter / advanced filter to automatically update you will need to use VBA.

Alan.

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.