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 |
#41
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
Balan,
Your macro does in fact work, but you are correct, it is very time consuming (and resource consuming) due to its recursive nature. Thank you again for all of your time and effort, you have done very well! Especially as some just getting into VBA programming! Well Done, Bravo! You do not need to worry about this particular problem anymore, do not let it distract you. I will work with what Pete and Max have provided and figure it out from there. Thank you again Balan, you have been invaluable! -Pogster |
#42
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Pete,
The changes you suggested worked like a charm! Your macro is fast, efficient, and does exactly what it is supposed to. It is amazing, the number of shortfalls Excel 2003 has compared to the new 2007 version (which allows me to conditionally format by searching for duplicates) But your macro has brought 2003 up to speed, at least in this one case. Thanks so much for your help, this macro should definitley be posted somewhere where it can be easily found, it is very useful, and modifiable to suit other specific needs. Thank you again for the time you spent in helping me with this issue, and coding a great solution. This forum is made great by people like yourself and the others who posted with replies to my issue You are all great! Many Thanks! -pogster |
#43
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Well, thanks for such effusive praise - I'm glad the macro does what
you want it to do. As regards storing it somewhere, well Google maintains archives of these newsgroups, so it should be available for anyone who searches for it. If after thorough testing you are convinced that it does the job, then you might like to apply it directly to your other files, as you said in one post that you extract this data from a file and that the order was very important so that you can paste it back to the same file - so this could save you a bit of time. Add two new lines near the beginning of the macro to insert two new columns, so that it would read: Dim my_top As Long Dim my_bottom As Long Application.ScreenUpdating = False Columns("B:C").Select 'new Selection.Insert Shift:=xlToRight 'new Range("B1").Select and so on ... Then near the end of the macro change this line: Columns("B:B").Select to this: Columns("B:C").Select You could now safely apply the macro to your other files, as the contents of the other columns get shifted out of the way - well, only if the numbers are in column A of the other files. Perhaps you could explain what it is you are working with sometime ... Pete On Oct 8, 9:15 pm, wrote: Pete, The changes you suggested worked like a charm! Your macro is fast, efficient, and does exactly what it is supposed to. It is amazing, the number of shortfalls Excel 2003 has compared to the new 2007 version (which allows me to conditionally format by searching for duplicates) But your macro has brought 2003 up to speed, at least in this one case. Thanks so much for your help, this macro should definitley be posted somewhere where it can be easily found, it is very useful, and modifiable to suit other specific needs. Thank you again for the time you spent in helping me with this issue, and coding a great solution. This forum is made great by people like yourself and the others who posted with replies to my issue You are all great! Many Thanks! -pogster |
#44
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Actually, the numbers are always in column O.
Thanks for the suggestion though. What i am working with are balance sheets for clients of an insurance company. I am weeding out all of the unnessecary entries (which cancel) to track down the entries which actually contribute to the final account balance. There are hundreds of balance sheets, spanning an average of 2000 rows apiece. Its a big job to do manually, but this macro really helps. The only other variable to consider (one which is probably impossible to code for), is that some of the numbers which DO NOT contribute to the balance (and cancel out) are not just single numbres, but made up of 2 or more other individual entries. A simple example: -100 cancel with SUM(50+10+40) Except in reality the numbers are not that simple or easy to locate. To accomplish this, you would need a smart macro that could detect numbers that could add together to create other numbers in this 2000 row sheet. That would take alot of code and thought, i think. No need to bother with it. Its easier to try to do that part by hand. But cancelling identical cancelling numbers is 90% of the task. Like i said, hours of tedius head-ache inducing searching, simplified to two clicks by your great macro. Cant thank you enough. Hope this answers your question, which is a nice change from you answering mine =) Cheers. -Pogster |
#45
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Yeah, thanks for taking the trouble to post back - it's always
interesting to find out what the real situation is, as often we just get a snippet. The other problem you refer to crops up quite often in the groups, where people want to reconcile payments against invoices. I think Harlan Grove had a macro to do it, but it is a very number-intensive exercise (not one that I'm going to attempt). I dabbled a bit with the macro last night in making the colour cycle through a range of values, rather than just one colour (green), i.e. the colour changes each time a pairing is found - would you be interested in a multi-colour version? You can't actually do very much with coloured cells - would you like the macro to put something in column B (eg "Y") to indicate that the cell has been paired, so that it can help you to eliminate them and thus concentrate on your other problem? Pete On Oct 9, 9:33 pm, wrote: Actually, the numbers are always in column O. Thanks for the suggestion though. What i am working with are balance sheets for clients of an insurance company. I am weeding out all of the unnessecary entries (which cancel) to track down the entries which actually contribute to the final account balance. There are hundreds of balance sheets, spanning an average of 2000 rows apiece. Its a big job to do manually, but this macro really helps. The only other variable to consider (one which is probably impossible to code for), is that some of the numbers which DO NOT contribute to the balance (and cancel out) are not just single numbres, but made up of 2 or more other individual entries. A simple example: -100 cancel with SUM(50+10+40) Except in reality the numbers are not that simple or easy to locate. To accomplish this, you would need a smart macro that could detect numbers that could add together to create other numbers in this 2000 row sheet. That would take alot of code and thought, i think. No need to bother with it. Its easier to try to do that part by hand. But cancelling identical cancelling numbers is 90% of the task. Like i said, hours of tedius head-ache inducing searching, simplified to two clicks by your great macro. Cant thank you enough. Hope this answers your question, which is a nice change from you answering mine =) Cheers. -Pogster |
#46
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Hey, sorry for the late post. Been busy with work lately.
As for the color changing, its really not necssary but thanks for the suggestion. As for the marker in column B, the trouble with moving the numbers out of the order i have them in, or extracting unique values is that each number also has about 11 columns of other information such as acct numbers and journal ID's and dates and descriptions etc. that go along with that particular value. So moving it out of the order i get it in has the possibility of mucking everything up for me. Its easier just to point out, visually, the values to ignore (or focus on), and in the process retain the order. If the marker just marked off the Unique values in column B, i could of course insert a column next to col O and use it that way somehow, to sort it, which would actually be quite helpful i think. Maybe if the macro could look directly at column O, create a column to use for its data, which it will delete, but also insert a column to the right of Col O and place the markers there. Would this be difficult to accomplish? If its easier, yes, just placing a marker in column B would work just as well, i can shift columns manually without a problem. Thanks for the good idea though, as this would allow me to sort by Unique or cancelled value (since you cannot sort by formatting in 2003) -Pogster |
#47
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
I was thinking that you would do this in your extracted file - in
addition to ending up with numbers in column A which are highlighted if paired, you would also have a column B which would have some marker in to indicate these pairings (like "x"). You can still copy column A back to column O of your original sheet, and copy column B to the next empty column in your original sheet (which might be column P, but I didn't know the layout of your data in the original file). I thought you could then apply autofilter to this new column, along the lines of: Custom, Not Equal To, x ... so that only the rows which have not been paired would then be visible, so that you can then concentrate on doing what you need to do with those. Of course, you could sort the data by this new column, to bunch up the unpaired amounts and then concentrate on them - it's up to you how you use it. The thing is - would you find it useful? OK, I'll ditch the multi-coloured idea. Pete On Oct 15, 8:43 pm, wrote: Hey, sorry for the late post. Been busy with work lately. As for the color changing, its really not necssary but thanks for the suggestion. As for the marker in column B, the trouble with moving the numbers out of the order i have them in, or extracting unique values is that each number also has about 11 columns of other information such as acct numbers and journal ID's and dates and descriptions etc. that go along with that particular value. So moving it out of the order i get it in has the possibility of mucking everything up for me. Its easier just to point out, visually, the values to ignore (or focus on), and in the process retain the order. If the marker just marked off the Unique values in column B, i could of course insert a column next to col O and use it that way somehow, to sort it, which would actually be quite helpful i think. Maybe if the macro could look directly at column O, create a column to use for its data, which it will delete, but also insert a column to the right of Col O and place the markers there. Would this be difficult to accomplish? If its easier, yes, just placing a marker in column B would work just as well, i can shift columns manually without a problem. Thanks for the good idea though, as this would allow me to sort by Unique or cancelled value (since you cannot sort by formatting in 2003) -Pogster |
#48
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Hey Pete,
Yes, absolutely, that would probably be useful. Thing is, management is going to look at what ive done and decide what format they want it in anyway. So i could group them by paired or unpaired and then they could just go and undo it with some stupid autofilter, but i believe it would look better and be easier to digest in such a sorted format. In short: yes this would be useful !!! As for the multicolor idea which i shot down, i can see how it would be useful if say a seperate color was applied to ammount under 50k, 50k - 150k, 150k - 250k, 250 - 500k, 500k - 1M, etc. etc. This way the colors could represent ranges of values. Again, i do not need this functionality, but it would be interesting if you wanted to implement it for others use. Thanks again for keeping up with this, i hope your code can be put to good use outside of my particular dilemma, because i think its such useful functionality. -Pogster |
#49
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Okay, here's an amended macro in full, along the lines you have
suggested: Sub Mark_duplicates_a() ' ' 04/10/2007, Pete Ashurst ' amended 17/10/2007 ' Dim my_top As Long Dim my_bottom As Long Dim colour As Integer Application.ScreenUpdating = False Columns("B").Select Selection.Insert Shift:=xlToRight Range("C1").Select ActiveCell.Value = "1" Range(Selection, Selection.End(xlDown)).Select Selection.DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False Columns("A:C").Select Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _ Header:=xlNo, 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 Int(Cells(my_top, 1).Value * 10) / 10 = Int(Abs(Cells(my_bottom, 1).Value) * 10) / 10 Then Select Case Cells(my_top, 1).Value Case Is 50000 colour = 4 'Bright Green Case Is 150000 colour = 6 'Yellow Case Is 250000 colour = 8 'Turquoise Case Is 500000 colour = 39 'Lavendar Case Else colour = 15 'Grey End Select Range("A" & my_top).Interior.ColorIndex = colour Cells(my_top, 2).Value = "Y" Range("A" & my_bottom).Interior.ColorIndex = colour Cells(my_bottom, 2).Value = "Y" 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:C").Select Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Columns("C").Select Selection.Delete Shift:=xlToLeft Range("C1").Select Application.ScreenUpdating = True End Sub This inserts a new column B, so any other data on the sheet will be moved to the right. Column B will contain "Y" wherever there is a pairing - you could change this to "paired" or some-such by making the obvious two changes mid-way in the macro. The macro also applies colour banding for the ranges you suggested. It should be fairly obvious how to introduce other ranges in the CASE part of the macro (just keep the numbers in sequence), and you can easily change colours if you don't like mine - here's some other numbers you might like to play about with: Red - 3, Aqua - 42, Orange - 46, Pink - 7, Tan - 40 Maybe marginally slower, but still less than 3 seconds on my test data of nearly 2200 values. Hope this helps. Pete On Oct 16, 3:30 pm, wrote: Hey Pete, Yes, absolutely, that would probably be useful. Thing is, management is going to look at what ive done and decide what format they want it in anyway. So i could group them by paired or unpaired and then they could just go and undo it with some stupid autofilter, but i believe it would look better and be easier to digest in such a sorted format. In short: yes this would be useful !!! As for the multicolor idea which i shot down, i can see how it would be useful if say a seperate color was applied to ammount under 50k, 50k - 150k, 150k - 250k, 250 - 500k, 500k - 1M, etc. etc. This way the colors could represent ranges of values. Again, i do not need this functionality, but it would be interesting if you wanted to implement it for others use. Thanks again for keeping up with this, i hope your code can be put to good use outside of my particular dilemma, because i think its such useful functionality. -Pogster |
#50
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Pete,
Great mod! The color coding works great, though in my version of the macro i removed your Select case as i did not need the color coding. But the column B identifier is also very useful. Thank you for this valuable addition to your already superb macro! The speed decrease is completely trivial, as it is barely noticeable. I will toy around with this macro and possibly add more comments to the file itself as i figure out its full functionality. You are so the man. Thanks pete. -Pogster |
Thread Tools | |
Display Modes | |
|
|