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 |
#31
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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 - |
#32
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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! |
#33
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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 |
#34
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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 |
#35
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
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 |
#36
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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 --- |
#37
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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 |
#38
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
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 |
#39
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
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 |
#40
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
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 |
Thread Tools | |
Display Modes | |
|
|