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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|