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.
.
|