In A1 put in
=IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$B$1:$B$10,SMALL(IF(
Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "")

enter this with Ctrl+shift+Enter rather than just enter since it is an array
formula.

in B1 put in

=IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$C$1:$C$10,SMALL(IF(
Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "")

enter this with Ctrl+shift+Enter rather than just enter since it is an array
formula.

Select A1:B1 and drag fill to the right.

Gave me the values you showed.

--
Regards,
Tom Ogilvy


Remco wrote in message
...
I've Excel 2000 and I've a (little?) problem.
I have a sheet like below (example):

Sheet name: Invoer
A B C
1 1 bal bol
2 2 bol kat
3 3 kat bal
4 1 kat bol
5 2 bol jan
6 2 bal jan
7 3 jan bol
8 3 bal kat
9 3 jan kat
10 1 bol jol


Now, what I'm looking for is a formula in a new sheet
what gives me on [newsheet row 1] the words in [invoer column B] where the
value in [invoer column A]=1,
and on [newsheet row 2] the words in [invoer column C] where the value in
[invoer column A]=1.

The result in the newsheet must be like this (based on example):

Sheet name: NewSheet
A B C
1 bal kat bol
2 bol bol jol

I hope someone can help me with this.

Thanks,
Remco