extracting values out of a list using formulars

[Max Power]

hi there,
i've got the following problem: i've got a list of values (text). this
list might contain a value several times. i want to extract the values
of this list using a formular. multiple occourances of one value must
only be extracted once. the extracted list should be created
automatically only using formulars and no macros.
here is an example of what a want to do: the user enters values in the
cells A1 to A5 and the speedsheet should print out A8 to A10.

------| A | B |
1 | val1 | |
2 | val2 | |
3 | val2 | |
4 | val3 | |
5 | val1 | |
6 | | |
7 | List | |
8 | val1 | |
9 | val2 | |
10 | val3 | |

maybe someone has got an idea on how to do this.
greets,
max power

 

[Frank Kabel]

Hi
1. you may have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique
though this may create blank rows in your target area

2. If you need to do this automatically with a formula and without
blanks it's more complicated. Try the
following (Assumptions: column A: stores your values, values start in
row 1)
- Enter a new column A (used as helper column). In A1 (first data row)
enter a '1'. In A2 enter
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")
copy this down for all rows (A2:A5)

- So now lets assume the put your list of unique entries in column C
(also starting in row 1). Put the following in C1:
=IF(ISERROR(INDEX($B$1:$B$5,MATCH(ROW(),$A$1:$A$5,0))),"",INDEX($B$1:$B
$5,MATCH(ROW(),$A$1:$A$5,0)))
copy this down