R
Roger Govier
At the risk of this becoming the longest thread in history<g>
Yes that is exactly what I said would be the result.
You can have one solution, or the other which is why I suggested you
have both formulae in adjacent columns and use whichever you want.
Firstly, you can get rid of column C and the concatenation - we have now
discarded that.
If you want 1's to appear against "all" matching entries then the
formula is
=IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)),
SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"")
Place this in C1
With your 4 lines of data, all will remain blank because they do not
satisfy the criteria.
Because we are looking at ALL lines in the range abcd crops up 3 times,
so there cannot be a match and efgh crops up only once, so there cannot
be a match.
The moment you enter another line with abcd in column A and 100 in
column B, all 4 lines with abcd will show a 1 in column C because there
are 2 matching pairs and the sum of their values in column B is 0.
If you also put in column D the following formula
=IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)),
SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"")
then because we are only looking at lines up to and including the
current line that we are on, it will put a 1 in column D when the 2
criteria are met, but it will only put it against the second of the
matching pair of lines found within the range down to and including that
line.
Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the
criteria
I think we need to start again from the beginning with real examples and
description of exactly what you are wanting to achieve.
In your first posting you said you wanted to eliminate matching records.
If you do eliminate the matching records, or copy them to another sheet
called Completed or Reconciled, then the first method as above will work
fine.
If you do not remove them, then as you add further entries of the same
values in column A, obviously all of the previously marked lines will
become unmarked, as there will be an odd number of entries, and the
amounts in column B will not sum to 0.
--
Regards
Roger Govier
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
Yes that is exactly what I said would be the result.
You can have one solution, or the other which is why I suggested you
have both formulae in adjacent columns and use whichever you want.
Firstly, you can get rid of column C and the concatenation - we have now
discarded that.
If you want 1's to appear against "all" matching entries then the
formula is
=IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)),
SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"")
Place this in C1
With your 4 lines of data, all will remain blank because they do not
satisfy the criteria.
Because we are looking at ALL lines in the range abcd crops up 3 times,
so there cannot be a match and efgh crops up only once, so there cannot
be a match.
The moment you enter another line with abcd in column A and 100 in
column B, all 4 lines with abcd will show a 1 in column C because there
are 2 matching pairs and the sum of their values in column B is 0.
If you also put in column D the following formula
=IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)),
SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"")
then because we are only looking at lines up to and including the
current line that we are on, it will put a 1 in column D when the 2
criteria are met, but it will only put it against the second of the
matching pair of lines found within the range down to and including that
line.
Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the
criteria
I think we need to start again from the beginning with real examples and
description of exactly what you are wanting to achieve.
In your first posting you said you wanted to eliminate matching records.
If you do eliminate the matching records, or copy them to another sheet
called Completed or Reconciled, then the first method as above will work
fine.
If you do not remove them, then as you add further entries of the same
values in column A, obviously all of the previously marked lines will
become unmarked, as there will be an odd number of entries, and the
amounts in column B will not sum to 0.
--
Regards
Roger Govier
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