A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #21  
Old October 1st, 2007, 03:02 AM posted to microsoft.public.excel.worksheet.functions
Balan
external usenet poster
 
Posts: 65
Default Maddening Dilemma - Compare each cell within column a to each

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.



  #22  
Old October 1st, 2007, 03:53 PM posted to microsoft.public.excel.worksheet.functions
Balan
external usenet poster
 
Posts: 65
Default Maddening Dilemma - Compare each cell within column a to each

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.



  #23  
Old October 3rd, 2007, 06:41 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
external usenet poster
 
Posts: 28
Default Maddening Dilemma - Compare each cell within column a to each

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!

  #24  
Old October 3rd, 2007, 07:11 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
external usenet poster
 
Posts: 28
Default Maddening Dilemma - Compare each cell within column a to each cell

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!


  #25  
Old October 4th, 2007, 12:14 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Maddening Dilemma - Compare each cell within column a to each cell

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




  #26  
Old October 4th, 2007, 12:28 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Maddening Dilemma - Compare each cell within column a to each cell

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


  #27  
Old October 4th, 2007, 02:03 AM posted to microsoft.public.excel.worksheet.functions
Balan
external usenet poster
 
Posts: 65
Default Maddening Dilemma - Compare each cell within column a to each

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!


  #28  
Old October 6th, 2007, 04:39 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
external usenet poster
 
Posts: 28
Default Maddening Dilemma - Compare each cell within column a to each cell

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

  #29  
Old October 6th, 2007, 11:24 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Maddening Dilemma - Compare each cell within column a to each cell

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



  #30  
Old October 7th, 2007, 01:08 AM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Maddening Dilemma - Compare each cell within column a to each cell

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.