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

[Guest]

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.

 

[Guest]

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.

 

[pogster]

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!

 

[pogster]

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!

 

[Max]

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]

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()))


---

 

[Guest]

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

 

[pogster]

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

 

[Max]

.. 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

---

 

[Pete_UK]

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

 

[Pete_UK]

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

Pete

 

[Pete_UK]

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

 

[pogster]

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

 

[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

 

[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

 

[Max]

Going by your latest explanation to Pete
for your earlier line
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)

---

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[Guest]

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

 

[Guest]

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. "(e-mail address removed)"
wrote: