Auto filter

[-emma-]

Hi,

I need to be able to review a range of data (example A2:A150) and in a different cell/column, be able to produce a list of unique entries in ascending order.

Example

Column A
45
16
16
82
45
45
79
82
82

Column B
16
45
79
82

This list may be constantly changing, so new unique entries may be added. Ideally I need this list to be updating when Column A updates.

Thanks in advance for your help on this…

Emma x

 

[Yahya]

This is idea by formula, try it

Excel Workbook
	A	B	C	D
1	45
2	16			45
3	16			16
4	82			82
5	45			79
6	45
7	79
8	82
9	82
Sheet10

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[-emma-]

Hi,

I did what you said but came out with #NAME? (This is the formual exactly taken from D2)...=IFERROR(INDEX($A$1:$A$9,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$9),0)),"") the curly brackets were there...

Any other ideas please?

 

[Yahya]

Hi,

I did what you said but came out with #NAME? (This is the formual exactly taken from D2)...=IFERROR(INDEX($A$1:$A$9,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$9),0)),"") the curly brackets were there...

Any other ideas please?

this is array formula
and read this note
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[-emma-]

I did that,

Am i correct in thinking this should be placed in D2?

 

[Yahya]

I did that,

Am i correct in thinking this should be placed in D2?

yes the formula should be placed in D2

Iferror just work with Excel 2007 and 2010

may be this make problem for you

 

[-emma-]

Forgetting I was at work, here I have Excel 2003...

Thank you for your help anyway

 

[moonfish]

I tested the formula and it's working for me, but it's not sorting the data from low to high.

Regardless, there's the formula rewritten for older versions of excel:
=IF(ISERROR(INDEX($A$1:$A$9,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$9),0))),"",INDEX($A$1:$A$9,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$9),0)))

 

[Yahya]

Forgetting I was at work, here I have Excel 2003...

Thank you for your help anyway

my formula doesn't work with Excel 2010
you need to modify it with IF and iserror
to be like this

Excel Workbook
	D
2	45
3	16
4	82
5	79
6
7
8
9
Sheet10

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[-emma-]

That works a treat - thank you Yahya