adding unique values together

[ben123456]

Hey,



i want to be able to filter out all the different values and put them onto another table as unique values. But i dont want to use filter as it hides rows i dont want hiding or a macro because its not feasable for what im wanting it for.

Excel Workbook
	A	B
1	apples
2	pears
3	apples
4	pears
5	banana
6	banana
7	apples
8	pears
9	banana
Sheet1

to

Excel Workbook
	A	B
1	apples
2	pears
3	banana
Sheet2

 

[ben123456]

anything?

 

[jim may]

Is there to be a Column Header, which you omitted above?

 

[barry houdini]

Assuming you have data down to row 100 in sheet1 try this:

In sheet2 A1

=Sheet1!A1

and then in A2 copied down as far as needed and further

=IFERROR(INDEX(Sheet1!A$1:A$100,MATCH(1,INDEX((COUNTIF(A$1:A1,Sheet1!A$1:A$100)=0)*(Sheet1!A$1:A$100<>""),0),0)),"")

assumes Excel 2007 or later

 

[ben123456]

need it for excel 2003 bud

 

[T. Valko]

need it for excel 2003 bud

Try this...

With your data in the range Sheet1 A1:A9...

On some other sheet...

Enter this formula in A1:

=Sheet1!A1

Enter this array formula** in A2 and copy down until you get blanks:

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(Sheet1!A$1:A$9,MATCH(0,COUNTIF(A$1:A1,Sheet1!A$1:A$9),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.