Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches

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

Poster,
I am sorry in my today's post I had mentioned that Pete's macro pairs even
numbers of same sign, which is not correct. Only difference is it
presupposes that a positive number will precede a negative number. I had
coded my macro based on the presumption that a positive or negative value may
precede and it will be followed by the same value of opposite sign
subsequently for mark off. This means my first value could be a negative or
positive. The macro will start looking for a value of opposite sign and then
color it. One more thing, my macro takes lot of time, it will stop if you
break the execution. Pete's macro executes in a jiffy. "
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