Hi
I think I understand. You just want to identify those lines where the
number in column A is the same, and, the sum of the numbers adjacent to
them in column B equals zero.
If so then in column C enter
=A2*ABS(B2)
In D2 enter
=IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"")
Filter on column D for values of 1
--
Regards
Roger Govier
"via135" wrote in message
oups.com...
hi!
i think i've not explained it well!
infact my intention is not to delete the duplicate records..! i just
want to eliminate the matched pairs of +ve and -ve wrt the other
column!
for example if i am having 4 records as under
1112........100
1112........-100
1112........-100
1113........100
i don't want to eliminate the duplicate records 2nd & 3rd
instead i want to offset 1st & 2nd (reconciled ones) and
identify the 3rd and 4th as unreconciled..!!
hope u understand..!!
regds!
-via135
On Nov 14, 2:47 pm, "Roger Govier"
wrote:
Hi
The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose CustomGreater than0
This will then show your rows which are duplicated.
Alternatively, you could wrap the formula in an IF statement
=IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","")
Filter column C for Duplicate
--
Regards
Roger Govier
"via135" wrote in
glegroups.com...
yes..you are absolutely right!
i have given "B2" i / o "-B2"..!
one more question..the formula gives the right answer only when
the each ref no in col A is having some unique value col B..!
when the same value is repeated in col B for the same ref no in col
A
there seems to be some glitch!
for example if i extend the data to A2:B15 as under..
A........B
111.... 50
112.... 100
113.... -50
114.... -100
111.... -50
115.... 150
112.... -50
114.... 50
114.... -50
113.... 100
111.... 50
113.... -100
111.... -50
114.... -50
what i am getting is
A.......B........C (getting) C (expecting)
111... 50... 2.......................1
112... 100... 0......................0
113... -50... 0.......................0
114... -100... 0......................0
111... -50... 2.......................1
115... 150... 0......................0
112... -50... 0.......................0
114... 50... 2.......................1
114... -50... 1.......................1
113... 100... 1......................1
111... 50... 2.......................1
113... -100.. 1......................1
111... -50... 2.......................1
114... -50... 1.......................0
any suggestion for alteration
in the function?
-via135
On Nov 13, 10:53 pm, "Roger Govier"
wrote:
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
messagenews: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