I have been trying to find a way in which i can have excel search
through two columns to find each unique match between the columns and
somehow change the formatting (like highlighting) to fish out the
matches. The problem is that i cannot think of a way to get each cell
in column a compared to each cell in column b, rinse and repeat for
the entire column a. And i am talking about thousands of rows per
column, and the columns are not necessarily the same size. Maddening!

If anyone has any ideas that do not involve installing shareware
plugins, please let me know!

Thanks!

Hi there! I can definitely help you with this problem. Here's a

1. Select the range of cells in column A that you want to compare with column B.
2. Go to the Home tab and click on Conditional Formatting, then select "New Rule".
3. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
4. In the formula bar, enter the following formula:
   Formula:

   =COUNTIF($B:$B,A1)0 

5. Click on the "Format" button and choose the formatting you want to apply to the cells that match.
6. Click "OK" to close the "Format Cells" dialog box.
7. Click "OK" again to close the "New Formatting Rule" dialog box.

This will highlight all the cells in column A that have a match in column B. You can then repeat the process for column B to highlight the cells that have a match in column A.

Note that this method will only work if the values in the two columns are exactly the same. If there are slight variations in the values (e.g. extra spaces), you may need to clean up the data first before comparing.

use countif as the formula is part of Conditional Formatting

=countif(C:C,A2)

information on conditional formattiong

Debra Dalgleish
http://www.contextures.com/tiptech.html


Chip Pearson's site on duplicates and uniques
http://www.cpearson.com/Excel/Duplicates.aspx

--
Regards,
Tom Ogilvy



" wrote:

I have been trying to find a way in which i can have excel search
through two columns to find each unique match between the columns and
somehow change the formatting (like highlighting) to fish out the
matches. The problem is that i cannot think of a way to get each cell
in column a compared to each cell in column b, rinse and repeat for
the entire column a. And i am talking about thousands of rows per
column, and the columns are not necessarily the same size. Maddening!

If anyone has any ideas that do not involve installing shareware
plugins, please let me know!

Thanks!

Tom,
A great start upon a solution, i am attempting a workaround using the
info you provided, many thanks!

Although my problem is more intricate than i let on. Realistically, i
would like to find duplicate values within the same column, but some
of the values are negative and some ar positive. I need to find out,
and highlight, the values which would cancel each other out. Hence i
need to find duplicates, except that they are not EXACT duplicates,
but one is the positive version of the number and one is the negative
version of the number. Here is an small example of such a list:

A1
10 <-matches such as this and the negative 10 below it both need to
be highlighted
-10
11
12
13 <-does not cancel and doesnt need to be highlighted
-12
-11
15
-15
-15 <-also does not cancel since one positve already canceled with one
negative and doesnt need to be highlighted

as you can see, i am attempting to highlight the corresponding
positive and negative values (the ones which would cancel each other
out) and leave the singular values unhighlighted. The problem is
even futher complicated by the fact that some values might have
multiple duplicates. There may be 10 positive number 11's and
nineteen negative number 11's, meaning i need the 10 positive versions
of the number 11 to cancel and highlight along with the 10 negative
versions of the number 11, but to leave the other 9 number 11's
unhighlighted.

I apologize for how confusing this sounds, but i would choose
confusing over hours of manual labor infused with human error anyday,
seeing as how i have thousands of rows within this one column with
many random and changing values to identify and compare.

If you or anyone can think of a solution more closely tailored to this
particular situation, i would be indebted to you forever.

In the meantime, i will work with what you provided to me Tom.

Thank you kindly.

Also, to clarify, the reason i was asking about two columns was
because i used absolute value on all of the negative numbers and put
them in a separate list next to the positive ones to find EXACT
matches. But if there is a way in which this wasnt necessary, that
would be far more ideal.

Thanks again guys!

Are you allowed to sort the data, as well as taking the absolute
value? If so, then you are looking for pairs of numbers in adjacent
cells. Assuming that you have your ABS formula in column B, starting
with B1, and that you have sorted the data by column B, then put this
formula in C1:

=IF(B1=B2,"yes1","no")

and this one in C2:

=IF(AND(B2=B1,C1="yes2"),IF(B2=B3,"yes1","no"),IF( B2=B1,"yes2",IF(B2=B3,"yes1","no")))

Copy this formula down column C by double-clicking the fill icon (the
small black square in the bottom right corner of the cursor. It will
give you pairs of "yes1"/"yes2" down the column indicating paired
duplicates, and the occasional "no" meaning a single unpaired (unique)
value.

If you wanted to use conditional formatting on the values in column A,
then you can use Formula Is and then =LEFT(C1,3)="yes" and set your
colour.

I've just realised that this doesn't necessarily pair +10 with -10, so
if you have, say, three +10s and one -10 this would indicate all four
10s would be paired - does this matter?

If you wanted the data in the original order, then you should first
enter a simple sequence in column D (say), i.e. 1, 2, 3, 4 etc before
sorting the data (including column D) on column B and using the
formulae. Then fix the values in column C and re-sort the data by
column D to get it back to the original sequence - column D can then
be deleted.

Anyway, hope this helps.

Pete

On Sep 28, 9:41 pm, wrote:
Also, to clarify, the reason i was asking about two columns was
because i used absolute value on all of the negative numbers and put
them in a separate list next to the positive ones to find EXACT
matches. But if there is a way in which this wasnt necessary, that
would be far more ideal.

Thanks again guys!

One play which can deliver both the CF desired and a way to extract the
cells in col A which do not cancel out ..

Assuming source numbers within A1:A100, as posted

Put in B1:
=IF(A1="","",COUNTIF($A$1:A1,A1))

Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),"",ROW() ))
Select B1:C1, copy down to C100. Col C will "flag" only cells in col A which
do not cancel out.

Then you could easily apply CF to highlight col A pointing to col C
Select col A (A1 active), then apply CF using Formula Is:
=$C1<""
Format the fill color to taste, OK out

And if you want to extract those cells in col A which do not cancel out in
another col (this might be useful), just put in say E1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Copy down to E100. This will return all cells in col A which do not cancel
out, neatly bunched at the top in col E
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
Tom,
A great start upon a solution, i am attempting a workaround using the
info you provided, many thanks!

Although my problem is more intricate than i let on. Realistically, i
would like to find duplicate values within the same column, but some
of the values are negative and some ar positive. I need to find out,
and highlight, the values which would cancel each other out. Hence i
need to find duplicates, except that they are not EXACT duplicates,
but one is the positive version of the number and one is the negative
version of the number. Here is an small example of such a list:

A1
10 <-matches such as this and the negative 10 below it both need to
be highlighted
-10
11
12
13 <-does not cancel and doesnt need to be highlighted
-12
-11
15
-15
-15 <-also does not cancel since one positve already canceled with one
negative and doesnt need to be highlighted

as you can see, i am attempting to highlight the corresponding
positive and negative values (the ones which would cancel each other
out) and leave the singular values unhighlighted. The problem is
even futher complicated by the fact that some values might have
multiple duplicates. There may be 10 positive number 11's and
nineteen negative number 11's, meaning i need the 10 positive versions
of the number 11 to cancel and highlight along with the 10 negative
versions of the number 11, but to leave the other 9 number 11's
unhighlighted.

I apologize for how confusing this sounds, but i would choose
confusing over hours of manual labor infused with human error anyday,
seeing as how i have thousands of rows within this one column with
many random and changing values to identify and compare.

If you or anyone can think of a solution more closely tailored to this
particular situation, i would be indebted to you forever.

In the meantime, i will work with what you provided to me Tom.

Thank you kindly.

M/s Pogster,Pete , Max and Tom,
Hi everybody. I am a learner in usage of Excel functions as well as VBA. I
hope the Excel function solutions offered by you experts will provide me more
insights. Meanwhile, I looked at Pogster's problem as a VBA problem and
tried to write a programme ( the first full fledged one I am writing
struggling with the codes & Help feature in VBA). I, understand his
requirement is to mark off one value with equivalent negative value whenever
it appears in the data range. My other assumptions we i) Even a
neagative value may precede a positive value. ii) If there is only a pair of
positive values or negative value both will remain unhighlighted. iii) The
first opposite value has to be the basis for markoff iv)The marked off
entries have to have colored so that they can be distinguished from the
outstanding ones v) The programme has to proceed to look into the entire
range until it encounters a blank cell in the same column.

I have called the programme markOff. It has to be run through VBA say
using F5. Before commencing the execution, the cursor has to be kept on the
first cell of the range in the excel sheet. I have not prepared any code for
a button or for a message box (say, for asking the user whether the cursor is
in the first cell and if not to keep it there to proceed further), as writing
this programme itself has been almost a day's job taking away my weekend and
I am also required to learning coding for buttons and msgbox. The code is
given below. I request the experts to look into it and suggest improvement,
if any needed to make it more efficient and economical. The data I have
taken as the basis for testing the programme is given first, followed by the
programme.

A
-12
12
15
14
-15
13
-16
15
16
13
16
17
(intentionally left blank to see whether execution stops here or not)
17

The code:
----------------------------------------------------------------
Sub markOff()
'
' markOff Macro
' Macro recorded 29/09/2007 by Balan
'

Dim Num As Range
Dim Val As Double
Dim addr As String
Dim rownum As Integer
Dim colnum As Integer

rownum = ActiveCell.Row + 1
colnum = ActiveCell.Column
addr = ActiveCell.Address
Val = ActiveCell.Value
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum)

Do While ActiveCell.Value < ""
If ActiveCell.Value = -Val Then
If ActiveCell.Interior.ColorIndex = 6 Then
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)
addr = ActiveCell.Address
Val = ActiveCell.Value
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)

Else
ActiveCell.Interior.ColorIndex = 6
Range(addr).Interior.ColorIndex = 6
Application.Goto Reference:=Worksheets("Sheet1").Range(addr)
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)
Do While ActiveCell.Interior.ColorIndex = 6

rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)
Loop


addr = ActiveCell.Address
Val = ActiveCell.Value
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)

End If
Else
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)
If ActiveCell.Value = "" Then
Application.Goto Reference:=Worksheets("Sheet1").Range(addr)
rownum = ActiveCell.Row + 1
If ActiveCell.Value < "" Then
Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum, colnum)
Do While ActiveCell.Interior.ColorIndex = 6
rownum = ActiveCell.Row + 1
Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum, colnum)
Loop
addr = ActiveCell.Address
Val = ActiveCell.Value
rownum = ActiveCell.Row + 1
Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum, colnum)
End If
End If
End If
Loop

End Sub


---------------------------------------------
I hope I am not troubling you.

Balan


"Pete_UK" wrote:

Are you allowed to sort the data, as well as taking the absolute
value? If so, then you are looking for pairs of numbers in adjacent
cells. Assuming that you have your ABS formula in column B, starting
with B1, and that you have sorted the data by column B, then put this
formula in C1:

=IF(B1=B2,"yes1","no")

and this one in C2:

=IF(AND(B2=B1,C1="yes2"),IF(B2=B3,"yes1","no"),IF( B2=B1,"yes2",IF(B2=B3,"yes1","no")))

Copy this formula down column C by double-clicking the fill icon (the
small black square in the bottom right corner of the cursor. It will
give you pairs of "yes1"/"yes2" down the column indicating paired
duplicates, and the occasional "no" meaning a single unpaired (unique)
value.

If you wanted to use conditional formatting on the values in column A,
then you can use Formula Is and then =LEFT(C1,3)="yes" and set your
colour.

I've just realised that this doesn't necessarily pair +10 with -10, so
if you have, say, three +10s and one -10 this would indicate all four
10s would be paired - does this matter?

If you wanted the data in the original order, then you should first
enter a simple sequence in column D (say), i.e. 1, 2, 3, 4 etc before
sorting the data (including column D) on column B and using the
formulae. Then fix the values in column C and re-sort the data by
column D to get it back to the original sequence - column D can then
be deleted.

Anyway, hope this helps.

Pete

On Sep 28, 9:41 pm, wrote:
Also, to clarify, the reason i was asking about two columns was
because i used absolute value on all of the negative numbers and put
them in a separate list next to the positive ones to find EXACT
matches. But if there is a way in which this wasnt necessary, that
would be far more ideal.

Thanks again guys!

Mr Tom
Pl see my response to this question. I shall be grateful for your comments
/ suggestions.

"Tom Ogilvy" wrote:

use countif as the formula is part of Conditional Formatting

=countif(C:C,A2)

information on conditional formattiong

Debra Dalgleish
http://www.contextures.com/tiptech.html


Chip Pearson's site on duplicates and uniques
http://www.cpearson.com/Excel/Duplicates.aspx

--
Regards,
Tom Ogilvy



" wrote:

I have been trying to find a way in which i can have excel search
through two columns to find each unique match between the columns and
somehow change the formatting (like highlighting) to fish out the
matches. The problem is that i cannot think of a way to get each cell
in column a compared to each cell in column b, rinse and repeat for
the entire column a. And i am talking about thousands of rows per
column, and the columns are not necessarily the same size. Maddening!

If anyone has any ideas that do not involve installing shareware
plugins, please let me know!

Thanks!

Mr.Max,
Pl see my response to this question. I shall be grateful for your comments
/ suggestions.


"Max" wrote:

One play which can deliver both the CF desired and a way to extract the
cells in col A which do not cancel out ..

Assuming source numbers within A1:A100, as posted

Put in B1:
=IF(A1="","",COUNTIF($A$1:A1,A1))

Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),"",ROW() ))
Select B1:C1, copy down to C100. Col C will "flag" only cells in col A which
do not cancel out.

Then you could easily apply CF to highlight col A pointing to col C
Select col A (A1 active), then apply CF using Formula Is:
=$C1<""
Format the fill color to taste, OK out

And if you want to extract those cells in col A which do not cancel out in
another col (this might be useful), just put in say E1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Copy down to E100. This will return all cells in col A which do not cancel
out, neatly bunched at the top in col E
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
Tom,
A great start upon a solution, i am attempting a workaround using the
info you provided, many thanks!

Although my problem is more intricate than i let on. Realistically, i
would like to find duplicate values within the same column, but some
of the values are negative and some ar positive. I need to find out,
and highlight, the values which would cancel each other out. Hence i
need to find duplicates, except that they are not EXACT duplicates,
but one is the positive version of the number and one is the negative
version of the number. Here is an small example of such a list:

A1
10 <-matches such as this and the negative 10 below it both need to
be highlighted
-10
11
12
13 <-does not cancel and doesnt need to be highlighted
-12
-11
15
-15
-15 <-also does not cancel since one positve already canceled with one
negative and doesnt need to be highlighted

as you can see, i am attempting to highlight the corresponding
positive and negative values (the ones which would cancel each other
out) and leave the singular values unhighlighted. The problem is
even futher complicated by the fact that some values might have
multiple duplicates. There may be 10 positive number 11's and
nineteen negative number 11's, meaning i need the 10 positive versions
of the number 11 to cancel and highlight along with the 10 negative
versions of the number 11, but to leave the other 9 number 11's
unhighlighted.

I apologize for how confusing this sounds, but i would choose
confusing over hours of manual labor infused with human error anyday,
seeing as how i have thousands of rows within this one column with
many random and changing values to identify and compare.

If you or anyone can think of a solution more closely tailored to this
particular situation, i would be indebted to you forever.

In the meantime, i will work with what you provided to me Tom.

Thank you kindly.

Balan,

Thanks so much for taking the time to write a script for this
dilemma. In my attempts to execute the VBA macro script in excel, i
recieved a compile erorr: syntax error which caused the macro not to
run. The error occured at this point:

Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)

Beneath the first DoWhile Loop.

I am not much a programmer and so am not sure as to why this
happened.

Max,
thank you for your valiant effort on this problem of mine.

To comment on the solution you provided, i used your formulas and used
conditional formatting but received results that were not accurate.
Maybe if i emailed you the actual data set i am working with and
showed to you how your formatting worked out, it would be easier.

In the meantime, what happened was that in column B was that numbers
ranging from 1 to 16 showed up in various places. What did you mean
for this column to do? It had 1's next to values which i knew
repeated in the negative, so i dont think that count was accurate.

With column C, it basically counted each row position in order except
for about 10 or 15 rows in the middle of my long dataset in column
A.

As for the conditional formatting, since column C did not work out
exactly how i think you had anticipated, the formatting was also off.
Although the formatting did what it was supposed to do, just that
column C seemed to be of little use in this case.

I will email you the dataset so that you can see for yourself, the
results i achieved with your formulas.

What exactly did you mean for columns B and C to do? I am not the best
with understanding the code in excel.

THank you again for all of your help in this matter.

Pete,

Thanks so much for your attempt at this problem.

It is very important that all of the positives cancel with all of the
negative and leave the unique values untouched. A -10 should only
cancel with a single +10, etc...

I have tried a variation of the suggestion you offered, and though it
works in a case where this does not come into play, my needs require a
very accurate canceling of unique opposing values.

I wish there was a way i could just post the raw list of values i am
working with so it would be easier to understand.

Thanks again for your help Pete.

On Sun, 30 Sep 2007 11:07:47 -0700, wrote:

I wish there was a way i could just post the raw list of values i am
working with so it would be easier to understand.

Use either of the 2 free filehosts below to upload a sample of your actual
worksheet.
(Desensitize it first, if needed). Then copy & paste the generated link to
your sample file in response he

http://www.flypicture.com/
http://cjoint.com/index.php



Gord Dibben MS Excel MVP

I don't have time to code this for you, but a VBA solution could work
along the lines of:

add a sequence to column B

sort the data in both columns in (say) descending order of column A
(all the positive numbers will come first, down through zero then
negative numbers)

start looking at both ends of the list with two variables - top and
bottom

if cell(top) = cell(bottom) then colour both cells: increment top:
decrement bottom
else if cell(top) magnitude of cell(bottom) then increment top
else decrement bottom

do this until top=bottom

re-sort the data back to how it was using sequence in column B

delete column B.

Hope this helps.

Pete

On Sep 30, 7:07 pm, wrote:
Pete,

Thanks so much for your attempt at this problem.

It is very important that all of the positives cancel with all of the
negative and leave the unique values untouched. A -10 should only
cancel with a single +10, etc...

I have tried a variation of the suggestion you offered, and though it
works in a case where this does not come into play, my needs require a
very accurate canceling of unique opposing values.

I wish there was a way i could just post the raw list of values i am
working with so it would be easier to understand.

Thanks again for your help Pete.

Let's suppose your column that contains the values indicated is a
named range called MyRange. The following multi-cell array formula
will return a unique occurence of each uncancelled item:

=INDEX(IF(MyRange0,ABS(MyRange)*(COUNTIF(MyRange, "="&MyRange)-
COUNTIF(MyRange,"="&-MyRange)),
0),SMALL(IF(MATCH(IF(MyRange0,ABS(MyRange)*(COUNT IF(MyRange,"="&MyRange)-
COUNTIF(MyRange,"="&-MyRange)),
0),IF(MyRange0,ABS(MyRange)*(COUNTIF(MyRange,"="& MyRange)-
COUNTIF(MyRange,"="&-MyRange)),0),
0)=ROW(INDIRECT("1:"&ROWS(IF(MyRange0,ABS(MyRange )*(COUNTIF(MyRange,"="&MyRange)-
COUNTIF(MyRange,"="&-MyRange)),
0)))),MATCH(IF(MyRange0,ABS(MyRange)*(COUNTIF(MyR ange,"="&MyRange)-
COUNTIF(MyRange,"="&-MyRange)),
0),IF(MyRange0,ABS(MyRange)*(COUNTIF(MyRange,"="& MyRange)-
COUNTIF(MyRange,"="&-MyRange)),0),
0),""),ROW(INDIRECT("1:"&ROWS(IF(MyRange0,ABS(MyR ange)*(COUNTIF(MyRange,"="&MyRange)-
COUNTIF(MyRange,"="&-MyRange)),0))))))

If there's an extra negative, it will return the negative, and if
there's an extra positive, it will return the positive. One zero will
be returned for all matching pairs. It will return #NUM error if your
array range is longer than the list. So, in this set:

10
-10
-10
11
-11
12
13
14
-14
15
-15
-15
16
16
-16


The result is this, assuming you array-entered the above formula in an
8-row range:

-10
0
12
13
-15
16
#NUM!
#NUM!


Hope this helps.
-Ilia



On Sep 28, 4:24 pm, wrote:
Tom,
A great start upon a solution, i am attempting a workaround using the
info you provided, many thanks!

Although my problem is more intricate than i let on. Realistically, i
would like to find duplicate values within the same column, but some
of the values are negative and some ar positive. I need to find out,
and highlight, the values which would cancel each other out. Hence i
need to find duplicates, except that they are not EXACT duplicates,
but one is the positive version of the number and one is the negative
version of the number. Here is an small example of such a list:

A1
10 <-matches such as this and the negative 10 below it both need to
be highlighted
-10
11
12
13 <-does not cancel and doesnt need to be highlighted
-12
-11
15
-15
-15 <-also does not cancel since one positve already canceled with one
negative and doesnt need to be highlighted

as you can see, i am attempting to highlight the corresponding
positive and negative values (the ones which would cancel each other
out) and leave the singular values unhighlighted. The problem is
even futher complicated by the fact that some values might have
multiple duplicates. There may be 10 positive number 11's and
nineteen negative number 11's, meaning i need the 10 positive versions
of the number 11 to cancel and highlight along with the 10 negative
versions of the number 11, but to leave the other 9 number 11's
unhighlighted.

I apologize for how confusing this sounds, but i would choose
confusing over hours of manual labor infused with human error anyday,
seeing as how i have thousands of rows within this one column with
many random and changing values to identify and compare.

If you or anyone can think of a solution more closely tailored to this
particular situation, i would be indebted to you forever.

In the meantime, i will work with what you provided to me Tom.

Thank you kindly.

Heres a link to the source list i am trying to work with. There are
many more where this one came from.

Madenning
Dilemma2.xls

Thanks Gord for the ftp suggestions, and Pete again for the great
idea. Ill see if i can whip up some code for that.

Ilia, i will try out your solution in a second, thanks!

Here's a working sample to illustrate:
http://cjoint.com/?kbafj66Yrv
NettOffPosnNeg.xls

Note that the CF suggested earlier
is to highlight cells is col A which *do not* cancel out.

If you want to CF it the other way round,
use the converse formula: =$C1=""

The formulas in col B and C need to be implemented correctly. They need to
be copied all the way down to the last row of data in source col A.

My suggestion as applied to your sample is available he
http://www.flypicture.com/download/MzcyODY=
Madenning_Dilemma2_1.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ups.com...
Max,
thank you for your valiant effort on this problem of mine.

To comment on the solution you provided, i used your formulas and used
conditional formatting but received results that were not accurate.
Maybe if i emailed you the actual data set i am working with and
showed to you how your formatting worked out, it would be easier.

In the meantime, what happened was that in column B was that numbers
ranging from 1 to 16 showed up in various places. What did you mean
for this column to do? It had 1's next to values which i knew
repeated in the negative, so i dont think that count was accurate.

With column C, it basically counted each row position in order except
for about 10 or 15 rows in the middle of my long dataset in column
A.

As for the conditional formatting, since column C did not work out
exactly how i think you had anticipated, the formatting was also off.
Although the formatting did what it was supposed to do, just that
column C seemed to be of little use in this case.

I will email you the dataset so that you can see for yourself, the
results i achieved with your formulas.

What exactly did you mean for columns B and C to do? I am not the best
with understanding the code in excel.

THank you again for all of your help in this matter.

Typo, Line:
is to highlight cells is col A which *do not* cancel out.

should read as:
is to highlight cells in col A which *do not* cancel out.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sorry, pl disregard this earlier sample as I forgot to adapt the range
in col C's array formula to suit the actual extent of your sample data

My suggestion as applied to your sample is available he
http://www.flypicture.com/download/MzcyODY=
Madenning_Dilemma2_1.xls


Here's the corrected sample:
http://www.flypicture.com/download/MzczMDU=
Madenning_Dilemma2_2.xls

The array formula in the top cell C1 should be:
=IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$2197&"_"&$B$1:$B$2197,0)),"",ROW ()))

since your data was down to that row, 2197

No change to the formulas in B1 and E1.
All formulas in B1:C1, and in E1 to be copied down to row2197
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Some explanations:

Col B serves to assign an arbitrary unique "suffix" to the data in col A.
This suffix assignment is necessary to distinguish between all multiple
occurences of the source numbers which you have in col A. Eg there could be
multiple occurences of 10, -10, 11, -11, etc all the way down in col A.

Col C then checks for the match of the original source numbers in col A
concatenated with the corresponding suffixes in col B against the full table
array composed. Where it matches (ie cancels out), the expression returns a
null string. Where it doesn't match, it returns an arbitrary row number as a
flag. This flag can then be used in the CF formula to format col A for all
the non-cancellations, ie all the unique source numbers in col A which do
not cancel out

The flag could also be used as well in another col E, to "float up" all the
non-cancellations from col A.

Easiest to see what's happening by referring
to this small working sample (as posted earlier):
http://cjoint.com/?kbafj66Yrv
NettOffPosnNeg.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Pogster,
I think the problem is on account of the wrapping up of the code to fit in
the page while posting it in this site. I have noticed this has happened in
all the lines "Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)"
This should appear as a single continuous line or should be separated by an
underscore "_" at the place where the code is being cut and taken to next
line. Kindly to the end of the line after Cells(rownum, and press delete to
bring the "colnum)" up to the same line or try typing after one space an "_".
Either should work. This application.goto line is appearing more than once
and in all cases it has happened. You may have to correct all the way I have
mentioned above.

Then I hope the data is in sheet 1, otherwise, pl see the code and where
ever "Sheet 1" is appearing change the name of the sheet appropriately and
try.

Pl keep me posted. If the code worked, press "Yes" button at the bottom (Pl
see next to "was this post helpful to you", I shall understand.

Best Wishes. If you still encounter problems do not hesitate to write to
me, I shall try to correct the code suitably.

" wrote:

Balan,

Thanks so much for taking the time to write a script for this
dilemma. In my attempts to execute the VBA macro script in excel, i
recieved a compile erorr: syntax error which caused the macro not to
run. The error occured at this point:

Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)

Beneath the first DoWhile Loop.

I am not much a programmer and so am not sure as to why this
happened.

Mr Pogster,
Pl see my suggestions to solve the problem. One of my sentences, I think,
is not clear. How it happened I do not know. May be while typing I might
have made some mistake. The thing I wanted to convey was that that the code
"Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)"
should entirely appear in a single line ( i.e., Application.Goto....colnum)
should be there in a single line or if you want to break it, it can be done
only using an underscore at the point where you want to break it . So, try
to bring every thing in one line.

As I mentioned this is not the only line, the same code is appearing in many
places and every where it is broken ( due to wrapping of text while copying
it from my PC to this site). Mend them also.


" wrote:

Balan,

Thanks so much for taking the time to write a script for this
dilemma. In my attempts to execute the VBA macro script in excel, i
recieved a compile erorr: syntax error which caused the macro not to
run. The error occured at this point:

Application.Goto
Reference:=Worksheets("Sheet1").Cells(rownum,
colnum)

Beneath the first DoWhile Loop.

I am not much a programmer and so am not sure as to why this
happened.

Balan!

Thanks so much for the amazin macro you wrote. The Macro works as you
meant for it to, but unfortunatley it has some shortcomings.

First: On a column of data above 250 rows, the macro locks up and
freezes and can only be exited by END TASKING excel itself.
Second: With this dataset, the macro failed to identify all of the
pairs, but it did identify 1 set of pairs. The problem seems to be
that only consecutive double pairs are identified:

-10 -highlighted
10 -highlighted
10
11 - highlighted
11 -NOT highlighted
-11 -highlighted
-11 - NOT highlighted
12
12
-13
-13
9 -highlighted
-9 -highlighted
-program breaks at an empty cell as it should.
14 -ignored
-14 -ignored

Second Set of Data:
11 -highlighted
-11 -highlighted
11 -highlighted
-11 -highlighted
-break

As you can see, for some reason (and i do not understand the code
perfectly) the macro finds and identifies the first set of pairs and
any subsequent sets of pairs correctly but if the pairs are somehow
broken up, like in the first example, the macro fails to indentify
them. Did you mean for this to happen? For this particular
application it is essential that ALL sets are found, even duplicate
cancelling sets which are spaced apart (because in reality the
matching pairs could be in row 10 and row 1000. If there are 2 +11
and 2 -11, they must both cancel and be highlighted, regardless of
their position in relation to each other.

Is there a way to change the code to achieve this objective?

Balan, thank you so much for the time and effort you have already
spent on this side-project. Hopefully it will challenge you as much
as it has been challenging me!!!
Thanks again, and let me know if you figure out a way to update the
Macro!

Thanks again!

Hi llia,

Thank you so much for your suggested solution! Turns out, that when
i enter your array-formula into cell C1, after naming my dataset
"MyRange", it gives me a formula error for some reason? I have no
clue why, seeing as how i have a hard time even understanding what
your formula does. Again, thanks for the attempt, but im not sure why
im getting an error? I cant get it working properly enough to test
it.

Any suggestions?

Thanks again!

Reply to OP's email received:

Welcome, but kindly keep all discussions online in the newsgroup thread, not
via private email to me. Upload the link to your new sample and post your
responses there.

Particularly 60,000,000.00 and its opposite showed up as "not cancelling"

Assuming the formulas are all correctly installed, and all source numbers in
col A are real numbers, one possibility is that the 2 numbers are not
exactly equal. One may be fractionally off the other (the real underlying
value), despite how they appear in the cells.

Try a simple test.
If the 2 numbers are in cells A5 and A10 (say),
in an empty cell, put: =ABS(A5)=ABS(A10)
If the numbers are really equal,
the return should be TRUE

To cater for the above possibility, we can use ROUND() in the array formula
in col C to round off all source values to say, 2 dp in the comparison

So in C1, array-entered, copied down:
=IF(B1="","",IF(ISNUMBER(MATCH(-ROUND(A1,2)&"_"&ROUND(B1,2),ROUND($A$1:$A$100,2)&" _"&ROUND($B$1:$B$100,2),0)),"",ROW()))

Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
--- pogster wrote:

Hey Max,

Thanks so much for attempting a solution to my
Madenning Dilemma.
Your solution is a great one, although it does not
completely work to
my dataset.
I attempted your formulas on my dataset on my own (i
did not look at
your application on my test sample), and the
formulas failed to flag a
bunch of cancelling pairs.

Particularly 60,000,000.00 and its opposite showed
up as "not
cancelling", when they in fact should cancel out. I
will upload or
email to you a copy of MY test results sometime
tonight.

Thanks again for your great attempt! But this one
seems uncrackable so
far.

-Pogster

Sorry, slight overkill there. Only the source numbers in col A need to be
rounded.

Should have read as
So in C1, array-entered, copied down:
=IF(B1="","",IF(ISNUMBER(MATCH(-ROUND(A1,2)&"_"&B1,ROUND($A$1:$A$100,2)&"_"&$B$1:$ B$100,0)),"",ROW()))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Posgster,
About the macro freezing at row 250 I will see separately. I need some time
to think of some data and test them.

As regards the pairs not highlighted, I understood your requirement as one
in which only a negative number will be paired against its positive( i.e., a
11 to be marked off against a -11). I never thought you want a 11 to be
marked off against another 11. That is why two positive numbers are not
highlighted even if they are of same magnitude. If you confirm that you want
every first occurence of similar number whether it is a negative or positive
equivalent to be marked off, I can modify the code.

I thought a minus figures represents completion of a transaction. Pl reply

" wrote:

Balan!

Thanks so much for the amazin macro you wrote. The Macro works as you
meant for it to, but unfortunatley it has some shortcomings.

First: On a column of data above 250 rows, the macro locks up and
freezes and can only be exited by END TASKING excel itself.
Second: With this dataset, the macro failed to identify all of the
pairs, but it did identify 1 set of pairs. The problem seems to be
that only consecutive double pairs are identified:

-10 -highlighted
10 -highlighted
10
11 - highlighted
11 -NOT highlighted
-11 -highlighted
-11 - NOT highlighted
12
12
-13
-13
9 -highlighted
-9 -highlighted
-program breaks at an empty cell as it should.
14 -ignored
-14 -ignored

Second Set of Data:
11 -highlighted
-11 -highlighted
11 -highlighted
-11 -highlighted
-break

As you can see, for some reason (and i do not understand the code
perfectly) the macro finds and identifies the first set of pairs and
any subsequent sets of pairs correctly but if the pairs are somehow
broken up, like in the first example, the macro fails to indentify
them. Did you mean for this to happen? For this particular
application it is essential that ALL sets are found, even duplicate
cancelling sets which are spaced apart (because in reality the
matching pairs could be in row 10 and row 1000. If there are 2 +11
and 2 -11, they must both cancel and be highlighted, regardless of
their position in relation to each other.

Is there a way to change the code to achieve this objective?

Balan, thank you so much for the time and effort you have already
spent on this side-project. Hopefully it will challenge you as much
as it has been challenging me!!!
Thanks again, and let me know if you figure out a way to update the
Macro!

Thanks again!

Max,

Your solution works wonders, and achieves close to a perfect result
every time. What change to the formula in column C would i need to
make to round it off to the tenths place, instead of 2 decimal
places. I am working with foreign currencies and conversions to USD
usually throw it off a little bit.

Now i just need to work out a way to convert the process into a macro
since its essentially exactly the same for every spreadsheet i do.

I cant thank you and everyone else who has posted on this forum
enough, you guys n gals rock. Thanks so much!

-Pogster

.. What change to the formula in column C would i need to
make to round it off to the tenths place, instead of 2 decimal
places.

Just amend the 2 in ROUND(..,2) to ROUND(..,10)

Place instead in C1, array-entered with CSE, then copy down:
=IF(B1="","",IF(ISNUMBER(MATCH(-ROUND(A1,10)&"_"&B1,ROUND($A$1:$A$100,10)&"_"&$B$1 :$B$100,0)),"",ROW()))

(Remember to change the ranges to suit)

Now i just need to work out a way to convert the process into a macro
since its essentially exactly the same for every spreadsheet i do.

Suggest you try a new posting in .programming
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
Max,

Your solution works wonders, and achieves close to a perfect result
every time. What change to the formula in column C would i need to
make to round it off to the tenths place, instead of 2 decimal
places. I am working with foreign currencies and conversions to USD
usually throw it off a little bit.

Now i just need to work out a way to convert the process into a macro
since its essentially exactly the same for every spreadsheet i do.

I cant thank you and everyone else who has posted on this forum
enough, you guys n gals rock. Thanks so much!

-Pogster

Hi Pogster,

I see you are still following this thread. Here's a macro I put
together a few days ago based on the algorithm I gave you last
weekend. It should be quite quick, even with large amounts of data. It
uses column B, so if you have any data in there you should insert a
new column B at the beginning of the macro (it gets deleted at the
end):

Sub Mark_duplicates()
'
' 04/10/2007, Pete Ashurst
'
Dim my_top As Long
Dim my_bottom As Long
Application.ScreenUpdating = False
Range("B1").Select
ActiveCell.Value = "1"
Range(Selection, Selection.End(xlDown)).Select
Selection.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
my_top = 1
my_bottom = Cells(Rows.Count, "A").End(xlUp).Row
Do Until my_top = my_bottom
If Cells(my_top, 1).Value = Abs(Cells(my_bottom, 1).Value)
Then
Range("A" & my_top).Interior.ColorIndex = 4
Range("A" & my_bottom).Interior.ColorIndex = 4
my_top = my_top + 1
my_bottom = my_bottom - 1
ElseIf Cells(my_top, 1).Value Abs(Cells(my_bottom, 1).Value)
Then
my_top = my_top + 1
Else
my_bottom = my_bottom - 1
End If
Loop
Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
Application.ScreenUpdating = True
End Sub

Beware of spurious line-wraps on some of the long lines.

Hope this helps.

Pete

On Oct 6, 4:39 pm, wrote:
Max,

Your solution works wonders, and achieves close to a perfect result
every time. What change to the formula in column C would i need to
make to round it off to the tenths place, instead of 2 decimal
places. I am working with foreign currencies and conversions to USD
usually throw it off a little bit.

Now i just need to work out a way to convert the process into a macro
since its essentially exactly the same for every spreadsheet i do.

I cant thank you and everyone else who has posted on this forum
enough, you guys n gals rock. Thanks so much!

-Pogster

Yes, the lines starting with If and ElseIf have both wrapped - the
Then should be on the same line.

Pete

On Oct 7, 1:08 am, Pete_UK wrote:
Hi Pogster,

I see you are still following this thread. Here's a macro I put
together a few days ago based on the algorithm I gave you last
weekend. It should be quite quick, even with large amounts of data. It
uses column B, so if you have any data in there you should insert a
new column B at the beginning of the macro (it gets deleted at the
end):

Sub Mark_duplicates()
'
' 04/10/2007, Pete Ashurst
'
Dim my_top As Long
Dim my_bottom As Long
Application.ScreenUpdating = False
Range("B1").Select
ActiveCell.Value = "1"
Range(Selection, Selection.End(xlDown)).Select
Selection.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
my_top = 1
my_bottom = Cells(Rows.Count, "A").End(xlUp).Row
Do Until my_top = my_bottom
If Cells(my_top, 1).Value = Abs(Cells(my_bottom, 1).Value)
Then
Range("A" & my_top).Interior.ColorIndex = 4
Range("A" & my_bottom).Interior.ColorIndex = 4
my_top = my_top + 1
my_bottom = my_bottom - 1
ElseIf Cells(my_top, 1).Value Abs(Cells(my_bottom, 1).Value)
Then
my_top = my_top + 1
Else
my_bottom = my_bottom - 1
End If
Loop
Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
Application.ScreenUpdating = True
End Sub

Beware of spurious line-wraps on some of the long lines.

Hope this helps.

Pete

On Oct 6, 4:39 pm, wrote:



Max,

Your solution works wonders, and achieves close to a perfect result
every time. What change to the formula in column C would i need to
make to round it off to the tenths place, instead of 2 decimal
places. I am working with foreign currencies and conversions to USD
usually throw it off a little bit.

Now i just need to work out a way to convert the process into a macro
since its essentially exactly the same for every spreadsheet i do.

I cant thank you and everyone else who has posted on this forum
enough, you guys n gals rock. Thanks so much!

-Pogster- Hide quoted text -

- Show quoted text -

I downloaded your file (2200 rows) and tried the macro which I posted
the other day with it - it took less than 2 seconds.

Pete

On Sep 30, 10:30 pm, wrote:
Heres a link to the source list i am trying to work with. There are
many more where this one came from.

Madenning
Dilemma2.xls

Thanks Gord for the ftp suggestions, and Pete again for the great
idea. Ill see if i can whip up some code for that.

Ilia, i will try out your solution in a second, thanks!

Pete!

Your macro is very effecient and effective...and speedy too. Thanks
so much for the time you put into coding this!

On the sample data that I provided, i ran the macro and found that for
some odd reason, it brought the value: $41,711,328,951.02 to the top
of the list after running.
It achieved the right answer with the highlighting for sure, but i am
not sure why this one value was brought up. Specifically, it was
brought up to A1 from the position of A297. It is important that the
values remain in the same order, as i am copying them out of a larger
source-set that is sorted specifically, so the monetary ammounts need
to retain their original order.

Again, this only happened with this particular dataset, not sure why.

I ran it multiple times and had the same result, but this did not
happen with other datasets....strange... Did you notice this
happening?

Thanks again for your incredible effort, i will certainly use your
macro to aid my projects!

-pogster

Pete,

Also, as in max's example, is there a way for the macro to round the
numbers it looks for to the TENTHS (thats one decimal place) place?
As in, a value of 1001.19 is simply rounded off at 1001.1. Not like
rounding up or down, but just ignoring the hundreths place all-
together. I posted my reasoning to Max for this, so you can see up
top for a description.

If this is too difficult, forget it, but it would help!

Thanks again.

-pogster

Balan,

Thanks for looking into it. You understood my requirements correctly,
but your macro looks specifically for the first pair, or occurrence of
a match. Negatives must indeed cancel with positives, but every
occurrence of a pair of cancelling numbers, must cancel. Not just
some pairs, and not others.

In a simple dataset such as this:

11 - A1
11 - B1
-11 - A2
-11 - B2

A1 should cancel with the A2, and B1 should cancel with B2....but what
happens in the macro is that A1 correctly cancels with A2, but B1 does
not Cancel with B2 as it should. This only happens when the numbers
are arranged in this order...A1B1,A2B2. Does this make sense? This
is a case that your macro does not successfully handle, and i am not
sure why.

I would appreciate if you thought about it, but its okay if not. Take
a look at Pete's Macro, his correctly identifies the matches, maybe
you will find inspiration there?

Thank you again Balan for all of your effort. You rock!

-Pogster

Going by your latest explanation to Pete
for your earlier line
.. round it off to the tenths place

think the line:
Just amend the 2 in ROUND(..,2) to ROUND(..,10)

should have read as:
Just amend the 2 in ROUND(..,2) to ROUND(..,1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Yes, I was able to reproduce this, and it is caused by the two sort
routines allowing Excel to guess if there is a header or not (there
shouldn't be). Change both instances of:

Header:=xlGuess, OrderCustom:=1, _

to this:

Header:=xlNo, OrderCustom:=1, _

then this shouldn't happen again.

Hope this helps.

Pete

On Oct 8, 2:23 pm, wrote:
Pete!

Your macro is very effecient and effective...and speedy too. Thanks
so much for the time you put into coding this!

On the sample data that I provided, i ran the macro and found that for
some odd reason, it brought the value: $41,711,328,951.02 to the top
of the list after running.
It achieved the right answer with the highlighting for sure, but i am
not sure why this one value was brought up. Specifically, it was
brought up to A1 from the position of A297. It is important that the
values remain in the same order, as i am copying them out of a larger
source-set that is sorted specifically, so the monetary ammounts need
to retain their original order.

Again, this only happened with this particular dataset, not sure why.

I ran it multiple times and had the same result, but this did not
happen with other datasets....strange... Did you notice this
happening?

Thanks again for your incredible effort, i will certainly use your
macro to aid my projects!

-pogster

To do this you need to change one line in the macro - the first IF, as
follows:

If Int(Cells(my_top, 1).Value * 10) / 10 = Int(Abs(Cells(my_bottom,
1).Value) * 10) / 10 Then

All one line - be wary of any line-breaks that the newsgroups put in.

This will not change any of your values. It merely ignores anything
beyond the first decimal place in the comparison.

Hope this helps.

Pete


On Oct 8, 2:25 pm, wrote:
Pete,

Also, as in max's example, is there a way for the macro to round the
numbers it looks for to the TENTHS (thats one decimal place) place?
As in, a value of 1001.19 is simply rounded off at 1001.1. Not like
rounding up or down, but just ignoring the hundreths place all-
together. I posted my reasoning to Max for this, so you can see up
top for a description.

If this is too difficult, forget it, but it would help!

Thanks again.

-pogster

Pogster,
Thanks for the feedback. I thought you have given up.
Pete's macro is excellent and efficient. You can't compare his coding with
mine. He is an expert. As I have mentioned in my first reply to your
question, I am a novice learning programming. I have learnt from Pete's code
that "Application.Screenupdating " procedure will reduce the time taken by
the code to execute.

I have solved the problem of second pairs being ignored. But it is the same
approach which I had with the first macro which I posted here - adopting an
iterative process which consumes lot of time - the macro reads each entry and
compares it with every other until it reaches its pair. That takes time.
When you have 1000's of rows of data, the process takes lot of time. I think
you mistook this for freezing . When you use control break or end task you
perhaps saw only execution upt 250 or so rows. I tried with 1375 entries of
6 digits each. It took 2mts 40 secs to complete the task. From Pete's
macro, I realise there could be more efficient ways of solving this. But I
have a tight schedule of my own, which is not allowing me to look into the
code. May be for another 15-20 days, I will not be able to find time for
this. So I am presenting the revised one here for you to try:

( Cursor should be on the first row; the macro will identify pairs of one
positive and one negative of same magnitude, it will ignore pairs of same
sign [postive or negative], if numbers of opposite sign are not available for
mark off. (Pete's macro pairs, if I have seen the results properly, even
pairs of same sign - was it OK ? I am still not clear. (In that case I have
a modified one, which I am not posting for the present). You have mentioned
about rounding off to the first decimal. I have not attempted that also.
The macro will retains the values as they are. It will simply color the
pairs of values of opposite sign. The macro which I call as MarkOff3 is as
follows:

---------------------------------
Sub MARKOFF3()

Dim Num As Range
Dim Val As Double
Dim addr As String
Dim begrow As Integer
Dim endrow As Integer
Dim rownum As Integer
Dim colnum As Integer
begrow = ActiveCell.Row
colnum = ActiveCell.Column
rownum = ActiveCell.Row
Range("b14").End(xlDown).Select
endrow = Range(Cells(rownum, colnum), Cells(rownum, colnum)) _
.End(xlDown).Row + 1
rownum = 0
Application.Goto Reference:=Worksheets("Sheet1").Cells _
(begrow, colnum)
Do While ActiveCell.Row < endrow

Do While ActiveCell.Interior.ColorIndex = 6

Application.ScreenUpdating = False
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells _
(rownum, colnum)
Loop

addr = ActiveCell.Address
Val = ActiveCell.Value
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells _
(rownum, colnum)

Do While ActiveCell.Row < endrow
Application.ScreenUpdating = False
If ActiveCell.Value = -Val Then
If ActiveCell.Interior.ColorIndex = 6 Then
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1"). _
Cells(rownum, colnum)

Else
ActiveCell.Interior.ColorIndex = 6
Range(addr).Interior.ColorIndex = 6
Application.Goto Reference:=Worksheets("Sheet1").Range _
(addr)
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1").Cells _
(rownum, colnum)
Exit Do

End If
Else
If ActiveCell.Row + 1 = endrow Then
Application.Goto Reference:=Worksheets("Sheet1"). _
Range(addr)
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1"). _
Cells(rownum, colnum)
Exit Do
Else
rownum = ActiveCell.Row + 1
Application.Goto Reference:=Worksheets("Sheet1"). _
Cells(rownum, colnum)
End If


End If

Loop
Loop
Application.ScreenUpdating = True
End Sub
----------------------

Lots of editing may be necessary to keep the coding crisp. But as I have
mentioned I couldn't find time for the same.

Best Wishes. Keep me posted.

Balan
" wrote:

Balan,

Thanks for looking into it. You understood my requirements correctly,
but your macro looks specifically for the first pair, or occurrence of
a match. Negatives must indeed cancel with positives, but every
occurrence of a pair of cancelling numbers, must cancel. Not just
some pairs, and not others.

In a simple dataset such as this:

11 - A1
11 - B1
-11 - A2
-11 - B2

A1 should cancel with the A2, and B1 should cancel with B2....but what
happens in the macro is that A1 correctly cancels with A2, but B1 does
not Cancel with B2 as it should. This only happens when the numbers
are arranged in this order...A1B1,A2B2. Does this make sense? This
is a case that your macro does not successfully handle, and i am not
sure why.

I would appreciate if you thought about it, but its okay if not. Take
a look at Pete's Macro, his correctly identifies the matches, maybe
you will find inspiration there?

Thank you again Balan for all of your effort. You rock!

-Pogster