hi!
sorry..the data in my earlier post
was wrong and the correct one is
as follows:

i'm having some id in colA
& numbers (may be "+" or "-") in colB
as under!
how can i eliminate the matched pairs
of "+" & "-" with ref to the id in colA?

-sample data-
colA colB
111 50
112 100
113 -50
114 -100
111 -50
115 150
112 -50
114 50
114 -50
113 100

what i want is using some formula in colC
can i flag off the record nos

1,5,8 & 9 the subtotal of the flagged records
should be always zero!


and able to retain the remaining records!

-via135

--
Message posted via http://www.officekb.com

hi!
The simplest solution is pivot table.


"via135 via OfficeKB.com дµÀ£º
"
hi!
sorry..the data in my earlier post
was wrong and the correct one is
as follows:

i'm having some id in colA
& numbers (may be "+" or "-") in colB
as under!
how can i eliminate the matched pairs
of "+" & "-" with ref to the id in colA?

-sample data-
colA colB
111 50
112 100
113 -50
114 -100
111 -50
115 150
112 -50
114 50
114 -50
113 100

what i want is using some formula in colC
can i flag off the record nos

1,5,8 & 9 the subtotal of the flagged records
should be always zero!


and able to retain the remaining records!

-via135

--
Message posted via http://www.officekb.com

hi!

i don't think so!

any other help pl?

-via135
wrote:
hi!
The simplest solution is pivot table.

"via135 via OfficeKB.com 写�：
"
hi!
sorry..the data in my earlier post
[quoted text clipped - 29 lines]

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1

Hi

One way
In cell C2 enter the following and copy down
=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))
There will be 1's against the matching items and 0's against the rest.
DataFilterAutofilteruse dropdown on column C to select rows with 1

--
Regards

Roger Govier


"via135 via OfficeKB.com" <u23552@uwe wrote in message
news:693b3ffc4e3e6@uwe...
hi!

i don't think so!

any other help pl?

-via135
wrote:
hi!
The simplest solution is pivot table.

"via135 via OfficeKB.com ??:
"
hi!
sorry..the data in my earlier post
[quoted text clipped - 29 lines]

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1

hi Roger!

i am getting the following results:

111 50 1
112 100 1
113 -50 1
114 -100 1
111 -50 1
115 150 1
112 -50 1
114 50 2
114 -50 2
113 100 1

-via135

Roger Govier wrote:
Hi

One way
In cell C2 enter the following and copy down
=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))
There will be 1's against the matching items and 0's against the rest.
DataFilterAutofilteruse dropdown on column C to select rows with 1

hi!

[quoted text clipped - 13 lines]

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1

Hi

You cannot have entered the formula as I gave you.
I get the following results
111 50 1
112 100 0
113 -50 0
114 -100 0
111 -50 1
115 150 0
112 -50 0
114 50 1
114 -50 1
113 100 0


--
Regards

Roger Govier


"via135 via OfficeKB.com" <u23552@uwe wrote in message
news:693c38a347d8e@uwe...
hi Roger!

i am getting the following results:

111 50 1
112 100 1
113 -50 1
114 -100 1
111 -50 1
115 150 1
112 -50 1
114 50 2
114 -50 2
113 100 1

-via135

Roger Govier wrote:
Hi

One way
In cell C2 enter the following and copy down
=SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))
There will be 1's against the matching items and 0's against the rest.
DataFilterAutofilteruse dropdown on column C to select rows with 1

hi!

[quoted text clipped - 13 lines]

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1