I am new to excel, doing the tutorial thing, but i am having a problem
with a worksheet i want to massage.
It goes something like this:

ColumnA Column B Column C Column D
abba A 0 0.15
abba B 1 2
abba C 0 0.14
abba D 0 3
abba E 1 0.2
acca A 0 1
acca B 0 3
acca C 0 .2
acca D 1 2
acca E 1 66

What i need to do is if Column C is 1 then I need excel to ouput the
value in Column D for ALL occurence of Column A. That is, the first B
has a value of 1 (the second b does not) and I need excel to output all
of the column D values for B in different cells, i.e For the B in
column B: abba:2, acca:3. For A, however, with two 0's I don't want it
output.

Here's one interp / suggestion ..

Suppose the table below is
in Sheet1, in A1:D10

abba A 0 0.15
abba B 1 2
abba C 0 0.14
abba D 0 3
abba E 1 0.2
acca A 0 1
acca B 0 3
acca C 0 .2
acca D 1 2
acca E 1 66

Put in E1: =IF(C1=1,ROW(),"")
Copy down to E10

In Sheet2
------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$E:$E,ROWS($A$1:A1) ),Sheet1!$E:$E,0)),"",INDE
X(Sheet1!A:A,MATCH(SMALL(Sheet1!$E:$E,ROWS($A$1:A1 )),Sheet1!$E:$E,0)))

Copy A1 across to D1, fill down to D10
(i.e. by as many rows as there is data in Sheet1)

You'll get in cols A to D:

abba B 1 2
abba E 1 0.2
acca D 1 2
acca E 1 66
[ rest are blanks: "" ]

which is an extract of all rows of the original table in Sheet1
where col C in Sheet1 contains "1"
(this is the interp of what you're after ..)

(Just hide away col C in Sheet2 if desired)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
oups.com...
I am new to excel, doing the tutorial thing, but i am having a problem
with a worksheet i want to massage.
It goes something like this:

ColumnA Column B Column C Column D
abba A 0 0.15
abba B 1 2
abba C 0 0.14
abba D 0 3
abba E 1 0.2
acca A 0 1
acca B 0 3
acca C 0 .2
acca D 1 2
acca E 1 66

What i need to do is if Column C is 1 then I need excel to ouput the
value in Column D for ALL occurence of Column A. That is, the first B
has a value of 1 (the second b does not) and I need excel to output all
of the column D values for B in different cells, i.e For the B in
column B: abba:2, acca:3. For A, however, with two 0's I don't want it
output.

Thanks,

If you have the time, do you mind explaining very briefly what exactly
that does?

It is almost what i am looking for, thanks for the help.
However, when i copy this out i only get clm D values that are 1. I
somehow need to be able to use 1 as a trigger to make excel display ALL
of the column D values for the column B constituants. (there would also
be adda, aeea, etc. with A-E and 0 or 1 and then a value.)

If there is one positive (1 is positive 0 is negative) hit for A in
abba and the rest are negative i need excel to display the positve AND
the non positive hits for A in abba, abbc and adda and aeea.
Thanks again for your help.

To be honest, I'm quite confused with your description <g

... to make excel display ALL of the column D values
for the column B constituants. (there would also
be adda, aeea, etc. with A-E and 0 or 1 and then a value.)

If there is one positive (1 is positive 0 is negative) hit for A in
abba and the rest are negative i need excel to display the positve AND
the non positive hits for A in abba, abbc and adda and aeea.

Doesn't the above sound like what you already have/started out with,
i.e. your source table itself ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
oups.com...
It is almost what i am looking for, thanks for the help.
However, when i copy this out i only get clm D values that are 1. I
somehow need to be able to use 1 as a trigger to make excel display ALL
of the column D values for the column B constituants. (there would also
be adda, aeea, etc. with A-E and 0 or 1 and then a value.)

If there is one positive (1 is positive 0 is negative) hit for A in
abba and the rest are negative i need excel to display the positve AND
the non positive hits for A in abba, abbc and adda and aeea.
Thanks again for your help.

Put in E1: =3DIF(C1=3D1,ROW(),"")
Copy down to E10

Change that to:

=3DIF(SUMIF(B$1:B$10,B1,C$1:C$10)0,ROW(),"")

Now, extract the data: (array entered)

=3DINDEX(A$1:A$10&":"&D$1:D$10,SMALL(IF(E$1:E$10< "",ROW
(A$1:A$10)),ROW(1:1)))

Copy down until #NUM! errors.

Output based on the OP's table and desired format(I think):

abba:2
abba:3
abba:0.2
acca:3
acca:2
acca:66

Biff

-----Original Message-----
To be honest, I'm quite confused with your description <g

... to make excel display ALL of the column D values
for the column B constituants. (there would also
be adda, aeea, etc. with A-E and 0 or 1 and then a=20
value.)

If there is one positive (1 is positive 0 is negative)=20
hit for A in
abba and the rest are negative i need excel to display=20
the positve AND
the non positive hits for A in abba, abbc and adda and=20
aeea.

Doesn't the above sound like what you already=20
have/started out with,
i.e. your source table itself ?
--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
roups.com
....
It is almost what i am looking for, thanks for the help.
However, when i copy this out i only get clm D values=20
that are 1. I
somehow need to be able to use 1 as a trigger to make=20
excel display ALL
of the column D values for the column B constituants.=20
(there would also
be adda, aeea, etc. with A-E and 0 or 1 and then a=20
value.)

If there is one positive (1 is positive 0 is negative)=20
hit for A in
abba and the rest are negative i need excel to display=20
the positve AND
the non positive hits for A in abba, abbc and adda and=20
aeea.
Thanks again for your help.


.

Have a look at:

http://tinyurl.com/63r58

which you could adapt to your task.

wrote:
I am new to excel, doing the tutorial thing, but i am having a problem
with a worksheet i want to massage.
It goes something like this:

ColumnA Column B Column C Column D
abba A 0 0.15
abba B 1 2
abba C 0 0.14
abba D 0 3
abba E 1 0.2
acca A 0 1
acca B 0 3
acca C 0 .2
acca D 1 2
acca E 1 66

What i need to do is if Column C is 1 then I need excel to ouput the
value in Column D for ALL occurence of Column A. That is, the first B
has a value of 1 (the second b does not) and I need excel to output all
of the column D values for B in different cells, i.e For the B in
column B: abba:2, acca:3. For A, however, with two 0's I don't want it
output.