Please Help (Unusual Lookup)
in [Programming]
|
Prev: I learn that way
Next: VBA//Oracle Interfacing Question
From: 1plane on 15 Nov 2009 18:39 Hi Guys, Below is a list of invoices from a supplier. What I am trying to do is to extract or delete the line items that are not duplicates. Ideally I want a code that will leave me with Row C1, C2 C6 and C7. Rows C3, C4, C5, C8 C9 and C10 can be deleted. I shall be grateful if anyoe can suggest how to extract these. C1 13/03/2009 V 00182675 £97,750.00 TRUE C2 13/03/2009 V 00261027 £97,750.00 FALSE C3 11/06/2008 V 00145507 £100,703.38 FALSE C4 17/08/2009 V 00285504 £101,914.92 FALSE C5 25/07/2008 V 00222055 £128,492.97 FALSE C6 30/01/2008 V 00192264 £163,001.17 TRUE C7 30/01/2008 V 00236244 £163,001.17 FALSE C8 10/01/2008 V 00194622 £167,822.38 FALSE C9 30/07/2008 V 00222733 £316,372.39 FALSE C10 30/07/2008 V 00152296 £386,725.54 FALSE Regards 1plane
From: joel on 15 Nov 2009 19:11 I'm a little bit confused on what you are considering a duplicate. You description if incosistent with the data you provided. I can write a macro, if you crrect your error. first I would sort the data by date (descending order) and any other columns that need to be sorted to put duplicates in adjacent rows. I would do is to add a formula into the worksheet in an auxilary column that would look for duplicates. This is an example of the formula starting in row 2. The formula compares row 1 with row 2. then copy the formula down to worksheet. =IF(AND(B1=B2,E1=E2),TRUE,FALSE) the duplicates would be the formulas that returned True. I would then use autofilter to remove the TRUE rows. All this can be done using a macro. Let me know which columns need to be compared and if there is a header row that need to be skipped. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=154326 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
From: 1plane on 15 Nov 2009 20:02 On Nov 16, 12:11 am, joel <joel.41p...(a)thecodecage.com> wrote: > I'm a little bit confused on what you are considering a duplicate. You > description if incosistent with the data you provided. > > I can write a macro, if you crrect your error. first I would sort the > data by date (descending order) and any other columns that need to be > sorted to put duplicates in adjacent rows. I would do is to add a > formula into the worksheet in an auxilary column that would look for > duplicates. This is an example of the formula starting in row 2. The > formula compares row 1 with row 2. then copy the formula down to > worksheet. > > =IF(AND(B1=B2,E1=E2),TRUE,FALSE) > > the duplicates would be the formulas that returned True. I would then > use autofilter to remove the TRUE rows. All this can be done using a > macro. Let me know which columns need to be compared and if there is a > header row that need to be skipped. > > -- > joel > ------------------------------------------------------------------------ > joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229 > View this thread:http://www.thecodecage.com/forumz/showthread.php?t=154326 > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] Joel, Thanks for your response. What I am looking for a way to put TRUE on both C1 & C2 because they have the same date and amount. This will be the same for C6 & C7. Your formula returns true on only one row and what I need to to is extract only rows with same day and amount. I hope this helps? Regards 1plane
From: OssieMac on 15 Nov 2009 20:18 Before automating the process with code, please confirm the following. When you refer to duplicates, am I correct in assuming that you mean duplicate amounts of money; not full duplicate records because as full records there are no duplicates in your example. If so, then you can do this with the following without code. If it returns the correct result and you still want the code to automate the process then let me know. Ensure you backup your data first in case this method does not return the expected result. Assuming that the C1, C2 etc is part of your data in column A then the money column will be column E. In the first empty column out to the right (say column G) insert the following formula in row 2 (I am assuming that you have column headers in row 1). =COUNTIF($E:$E,E2) Copy the formula down. Apply AutoFilter to the range of data. Set the filter for column G (the one with the above formula) to 1. Select all of the data BELOW the column headers. Select Edit -> Goto -> Special cells -> Visible Cells Only. Right click over the selected area. Select Delete rows. (At popup answer Yes to delete entire rows) Reset filter on column G to all data. -- Regards, OssieMac
From: 1plane on 15 Nov 2009 20:36
On Nov 16, 1:18 am, OssieMac <Ossie...(a)discussions.microsoft.com> wrote: > Before automating the process with code, please confirm the following. > > When you refer to duplicates, am I correct in assuming that you mean > duplicate amounts of money; not full duplicate records because as full > records there are no duplicates in your example. If so, then you can do this > with the following without code. If it returns the correct result and you > still want the code to automate the process then let me know. > > Ensure you backup your data first in case this method does not return the > expected result. > > Assuming that the C1, C2 etc is part of your data in column A then the money > column will be column E. > > In the first empty column out to the right (say column G) insert the > following formula in row 2 (I am assuming that you have column headers in row > 1). > > =COUNTIF($E:$E,E2) > > Copy the formula down. > > Apply AutoFilter to the range of data. > > Set the filter for column G (the one with the above formula) to 1. > > Select all of the data BELOW the column headers. > > Select Edit -> Goto -> Special cells -> Visible Cells Only. > > Right click over the selected area. > > Select Delete rows. (At popup answer Yes to delete entire rows) > > Reset filter on column G to all data. > > -- > Regards, > > OssieMac OssieMac, Yes it works, it works. I can confirm that I am refering to duplicate money (you are right) Can you pls help with automating the code? Regards 1plane |