Filtering fonction

[zara_toustra]

Hello everyone,

I have a serie of value in a column, most of these values are redundant. In another column I want each unique value.

car	car
car	truck
car	apple
truck	house
truck
apple
apple
apple
house
house

Users can add anything at the end of serie of values (in the first column), and I need right away to have the result on the second column, preferably without Macro.

Anyideas about a function that can do that?

Thanks !

[AlKey]

Copy and paste formula in B2 and drag it down

=IF(ISERROR(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$11),,),))),"",(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$11),,),))))

	A	B
2	car	car
3	car	truck
4	car	apple
5	truck	house
6	truck
7	apple
8	apple
9	apple
10	house
11	house

[Tony Valko]

Try this...


Data Range

	A	B
1	Header1	Header2
2	car	car
3	car	truck
4	car	apple
5	truck	house
6	truck
7	apple
8	apple
9	apple
10	house
11	house
12
13
14
15

This array formula** entered in B2 and copied down as needed:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A$2:A$100,MATCH(0,COUNTIF(B$1:B1,A$2:A$100),0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Use a reasonable end of range where I use down to row 100.

[newdoverman]

Another way to get unique records from a list:

[AlKey]

Try this...

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Use a reasonable end of range where I use down to row 100.

Hi Tony. Is there any other purpose for the portion of your formula LOOKUP("zzzzz",CHOOSE({1,2},"" simply to hide error? The other part of it INDEX(A$2:A$100,MATCH(0,COUNTIF(B$1:B1,A$2:A$100),0) does the job anyway when entered as an array?


Oh, I understand now. This is really cool. Makes a formula much smaller when writing it for Excel 2003 and earlier.

Thanks

[newdoverman]

It is an error trap. Leave a blank cell in the list and the filter stops at the blank. I have seen this before but I can't remember why this was important at the time. Written the way that you mention, the blank cell is replaced by a 0 and continues.

[Tony Valko]

Makes a formula much smaller when writing it for Excel 2003 and earlier.

Not just shorter, but more efficient, also!

Do you understand how the LOOKUP function is trapping any errors?

[AlKey]

I do now and thanks for sharing this little gem

[Tony Valko]

______

[zara_toustra]

Thanks Tony and the others.

Tony's solution works well. It took me a litle bit of time to implement because I work on a Excel 2002 french version so I had to translate function and change "," by ";" :
=RECHERCHE("zzzzz";CHOISIR({1;2};"";INDEX(A$2:A$100;EQUIV(0;NB.SI(B$1:B1;A$2:A$100);0))))

Many thanks

[zara_toustra]

………………………..

[martindwilson]

data starting in a2 then this will do it in b2 copied down

=IFERROR(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$11),0,0),0)),"")

[Tony Valko]

You're welcome. Thanks for the feedback!

[jahangeer100]

Using Auto Filter to filter data is a quick and easy way to find and work with a subset of data in a range of cells or table. Once you have filtered data in a range of cells or table, you can either reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.