hi!

mmhhmmm!

now i am getting

-A-.......-B-...-C-............-D-
abcd... 100..abcd100....blank
abcd...-100..abcd100....1
abcd...-100..abcd100....blank
efgh....100..efgh100......blank

instead

-A-......-B-..........-C-..........-D-
abcd -100 abcd100....blank
efgh 100 efgh100.....blank

-via135



On Nov 17, 1:35 pm, "Roger Govier"
wrote:
Yes you will get all blanks, because none of the collection is
satisfying the criteria.
Whilst you can "see" that the first 2 entries of abcd do produce a
matching pair, there are 3 entries with abcd in column A so it fails
firstly because Countif for abcd is not even (3).
It also fails because the Sum of values in B against lines having abcd
in column A does not come to zero.

We can get around this by making the formula carry out its tests as it
goes down the column, as opposed to being on the whole column, but then
you will only see the 1's appearing against the second of the 2 entries,
i.e. the entry which creates the match, hence if you were to try to use
Autofilter to view only the matching entries, it would only pick up one
half of each matched pair.

Perhaps you should use 2 columns, one with the formula as provided
already, and one with the modified formula as below
=IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)),
SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"")

You will note that the range is now $A$1:$A1 so it will expand as it is
copied down because of the relative second reference whereas the
original formula uses absolutes of $A$1:$A$15 and use the whole range
unaltered in each of the cells as you copy down.
I hope this makes sense to you.

--
Regards

Roger Govier

"via135" wrote in oglegroups.com...

hi!

i am still getting blanks in the flag Col-C..!
instead getting 1s only for first two records
the matched pair in the data!

the last two

abcd...-100
efgh....100

are not matched pairs!

-via135

Roger Govier wrote:
Hi

No, the result is correct.
It will only put a value of 1 against entries where there are
matching
pairs of values.
The abcd entries have 3 and the efgh has 1. All results are ODD
therefore no 1 is inserted.

If another entry of abcd and 100 were made, then the count would be
4,
and Iseven would be satisfied so all 4 lines would have 1 against
them.

Having just said that, I realise that entering another line of abcd
and -100 would also give that result, which would be incorrect as you
need the sum of column B to be zero for matching values in column A.

We don't need the concatenation column at all.
Just use
=IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)),
SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"")

This works for alpha or numeric in column A
--
Regards

Roger Govier

"via135" wrote in message
roups.com...
hi!

thks for responding again promptly!
as you rightly predict i am not that much of goose!

btw i am afraid that A2&ABS(B2) in col-B doesn't seems to
work for the text in col-A as like A2*ABS(B2) does the trick
for the number..!
ex:

"A" "B" "C" "D"
abcd... 100.... abcd100...blank
abcd... -100... abcd100...blank
abcd... -100... abcd100...blank
efgh....100.... efgh100....blank

actually i should get

abcd... -100...blank
efgh....100....blank

-via135

Roger Govier wrote:
Hi

Just change the formula in C2 to
=A2&ABS(B2)
then, you had already worked out my error for column D, it should
have
read
=IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"")

--
Regards

Roger Govier

"via135" wrote in message
roups.com...
hi!

sorry for disturbing again..!
one more follow up question..pl?

this formula seems to be ok for number values.
if iam having text as ref in the place of number ref
in col-A, is there any method to arrive at the result?

-via135

Roger Govier wrote:
Glad we got there in the end!!!
Thanks for the feedback.

--
Regards

Roger Govier

"via135" wrote in message
roups.com...
that's it!
this is what i exactly want!
thks roger.. thks a lot..!

-via135

Roger Govier wrote:
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
groups.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!...

read more »