Reference? Question

[Remco]

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

 

[Tom Ogilvy]

In A1 put in
=IF(COUNTIF(Invoer!$A$1:$A$10,1)>=COLUMN(),INDEX(Invoer!$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(Invoer!$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.

 

[Alan Beban]

I think there's atypo; the second formula should go in A2 rather than B1.

Alan Beban

In A1 put in
=IF(COUNTIF(Invoer!$A$1:$A$10,1)>=COLUMN(),INDEX(Invoer!$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(Invoer!$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

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

 

[Tom Ogilvy]

absolutely - my mistake - thanks for the heads up.

Regards,
Tom Ogilvy

I think there's atypo; the second formula should go in A2 rather than B1.

Alan Beban

In A1 put in

=IF(COUNTIF(Invoer!$A$1:$A$10,1)>=COLUMN(),INDEX(Invoer!$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(Invoer!$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

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

 

[Remco]

Tried to do it, but unfortunately Excel gives me an error:

"De formula you typed contains an error"
And than it highlited the first [$A$10,1] in the formula.
Can it be my version of excell or did I do something wrong?

Remco
--

In A1 put in
=IF(COUNTIF(Invoer!$A$1:$A$10,1)>=COLUMN(),INDEX(Invoer!$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(Invoer!$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

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

 

[Tom Ogilvy]

My formula (copied out of a worksheet where it is working fine) is set up
for US English. If you are not using US English, you may have to do some
translating. In US English, the comma (,) is a list separator, but in some
other countries, the semicolon ( is used as a list separator .

If you would like to post your email address I can email you the worksheet
and when you open it, excel will do the translations.

--
Regards,
Tom Ogilvy

Tried to do it, but unfortunately Excel gives me an error:

"De formula you typed contains an error"
And than it highlited the first [$A$10,1] in the formula.
Can it be my version of excell or did I do something wrong?

Remco
--

In A1 put in

=IF(COUNTIF(Invoer!$A$1:$A$10,1)>=COLUMN(),INDEX(Invoer!$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(Invoer!$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

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