eliminating matched records!

[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

 

[joytickle]

hi!
The simplest solution is pivot table.


"via135 via OfficeKB.com дµÀ£º
"

 

[via135 via OfficeKB.com]

hi!

i don't think so!

any other help pl?

-via135

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

 

[Roger Govier]

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.
Data>Filter>Autofilter>use dropdown on column C to select rows with 1

--
Regards

Roger Govier

hi!

i don't think so!

any other help pl?

-via135

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

 

[via135 via OfficeKB.com]

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

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.
Data>Filter>Autofilter>use dropdown on column C to select rows with 1

hi!

[quoted text clipped - 13 lines]

 

[Roger Govier]

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

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

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.
Data>Filter>Autofilter>use dropdown on column C to select rows with 1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

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

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

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

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.
Data>Filter>Autofilter>use dropdown on column C to select rows with 1
hi!
[quoted text clipped - 13 lines]
-via135

 

[Roger Govier]

Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

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

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

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

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.
Data>Filter>Autofilter>use dropdown on column C to select rows with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

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

Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

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

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
message
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.
Data>Filter>Autofilter>use dropdown on column C to select rows with
1
hi!
[quoted text clipped - 13 lines]
-via135

 

[Roger Govier]

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

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

Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

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?

On Nov 13, 10:53 pm, "Roger Govier" <[email protected]>
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
Roger Govier

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

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.
Data>Filter>Autofilter>use dropdown on column C to select rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

that's it!
this is what i exactly want!
thks roger.. thks a lot..!

-via135

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

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

Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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" <[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to select rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[Roger Govier]

Glad we got there in the end!!!
Thanks for the feedback.

--
Regards

Roger Govier

that's it!
this is what i exactly want!
thks roger.. thks a lot..!

-via135

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

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" <[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

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

Glad we got there in the end!!!
Thanks for the feedback.

--
Regards

Roger Govier

that's it!
this is what i exactly want!
thks roger.. thks a lot..!

-via135

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


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" <[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[Roger Govier]

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

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

Glad we got there in the end!!!
Thanks for the feedback.

--
Regards

Roger Govier

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


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"
<[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

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

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

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

Glad we got there in the end!!!
Thanks for the feedback.

--
Regards

Roger Govier


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


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"
<[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[Roger Govier]

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

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

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

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


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


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"
<[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the
Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to
select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

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

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

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

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


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


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


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"
<[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the
Autofilter,
use the dropdown on column C choose >Custom>Greater than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to
select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[Roger Govier]

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

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

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

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


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


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


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"
<[email protected]>
wrote:
Hi

The formula is working correctly.When you apply the
Autofilter,
use the dropdown on column C choose >Custom>Greater
than>0
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

message
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"
<[email protected]>
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

message
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.
Data>Filter>Autofilter>use dropdown on column C to
select
rows
with
1

hi!

[quoted text clipped - 13 lines]

-via135

 

[via135]

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

 

[via135]

hi!

help me out!!??

-via135

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






read more »